商務(wù)智能總體解決方案

1、Sybase/Business IntelligenceSYBASE 數(shù)據(jù)倉庫/商務(wù)智能處理方案魏健商務(wù)智能咨詢顧問SYBASE 軟件(中國)有限企業(yè)第1頁議程數(shù)據(jù)倉庫處理方案概述數(shù)據(jù)倉庫設(shè)計工具數(shù)據(jù)倉庫引擎 Sybase Adaptive Server IQ Multiplex第2頁“數(shù)據(jù)倉庫是在企業(yè)管理和決議中面向主題,集成, 與時間相關(guān)和不可修改數(shù)據(jù)集合”Bill Inmon數(shù)據(jù)倉庫定義第3頁OLTP系統(tǒng)5-10 年過去詳細數(shù)據(jù)當(dāng)前詳細數(shù)據(jù)輕度匯總數(shù)據(jù)高度匯總數(shù)據(jù)數(shù)據(jù)集市用戶分析網(wǎng)絡(luò)資源分析數(shù)據(jù)倉庫數(shù)據(jù)倉庫/決議分析系統(tǒng)數(shù)據(jù)倉庫是完全不一樣數(shù)據(jù)庫系統(tǒng)RDBMSSybaseSAP/ERPV

2、SAMEXCEL第4頁操作(業(yè)務(wù))系統(tǒng)特征事務(wù)處理性能是第一位 支持日常業(yè)務(wù) 事務(wù)驅(qū)動面向應(yīng)用 數(shù)據(jù)是當(dāng)前并在不停改變 存放詳細數(shù)據(jù) (每一個事件或事務(wù)) 針對快速預(yù)定義事務(wù)優(yōu)化設(shè)計 可預(yù)見使用模式 支持辦事人員或行政人員第5頁數(shù)據(jù)倉庫應(yīng)用系統(tǒng)特點支持久遠業(yè)務(wù)戰(zhàn)略決議分析驅(qū)動面向主題數(shù)據(jù)是歷史數(shù)據(jù)反應(yīng)某個時間點或一段時間數(shù)據(jù)是靜態(tài)，除數(shù)據(jù)刷新外數(shù)據(jù)是匯總 優(yōu)化是針對查詢而不是更新支持管理人員和執(zhí)行主管人員第6頁數(shù)據(jù)倉庫處理方案處理從數(shù)據(jù)庫中獲取信息問題。INFORMATION 信 息 信 息INFORMATION什么是數(shù)據(jù)倉庫處理方案？第7頁應(yīng)用價值時間1. 日常報表2. 即席查詢3. 分析4

3、. 數(shù)據(jù)挖掘?qū)ｎ}應(yīng)用1 2 3 4 數(shù)據(jù)倉庫應(yīng)用類型數(shù)據(jù)倉庫應(yīng)用第8頁數(shù)據(jù)倉庫系統(tǒng)體系架構(gòu)RelationalPackageLegacyExternalsourceDataCleanToolSource DataDataStagingWareHouseAdmin. ToolsEnterprise DataWarehouse Data Extraction,Transformationand loadDatamartDatamartEnterprise/Central DataWarehouseRDBMSROLAPRDBMS Dimension ModelingConformed dimensi

4、on&factIncluding atomic&aggregateArchitectedDatamartsCentralMetadata Data Modeling ToolEnd-UserToolEnd-UserToolMDBEnd-UserToolEnd-UserToolLocal MetadataLocal Metadata第9頁數(shù)據(jù)倉庫/商務(wù)智能應(yīng)用成功關(guān)鍵 做什么，怎么做？ 數(shù)據(jù)倉庫性能第10頁Sybase & Partner 專業(yè)服務(wù) 數(shù)據(jù)倉庫顧問咨詢Industry Warehouse StudioSybase IWS 方法學(xué)ER Design ToolImpact Analys

5、isMetadata ManagementSybase Industry Warehouse Studio打包數(shù)據(jù)倉庫基礎(chǔ)平臺概述業(yè)務(wù)模型物理模式元數(shù)據(jù)ETL 工具例子報表算法ETL Tool Metadata ExchangeSmart ETL Maps (Future)SQL TemplatesCognosBusiness ObjectsMicroStrategyBusiness Models focused on Key Industry EventsEnterprise-wide, Star Schema-based design第11頁IWS產(chǎn)品介紹TABLETABLETABLETA

6、BLETABLEIndustry-specific Data ModelsData Warehouse“Open RDBMS*”O(jiān)RACLE, IBM, MICROSOFT, NCR, SYBASE, etc. BI PartnersSample Applications Analytical CRMSales AnalysisCustomer ProfilingCampaign AnalysisCustomer Care AnalysisLoyalty AnalysisBusiness PerformanceAnalysisIndustry SpecificSample DataGenera

7、l - RepresentativeSystems Integrators GuideProject PlansImplementation Protocole.g. InformaticaETL ToolWarehouse ArchitectMulti-Dimensional Design Tool SQL SampleReportsWarehouse Control CenterMeta Data Management 第12頁客戶組成分析營銷活動分析客戶興趣分析忠誠度分析銷售分析行業(yè)相關(guān)經(jīng)營業(yè)績分析收益率分析EVT_TYP_ID = EVT_TYP_IDPRD_ID = PRD_IDEN

8、TY_ID = ENTY_IDENTY_ID = EMP_IDGEO_ID = GEO_IDLANGUAGE_ID = LANGUAGE_IDPRODUCT_ID = PRODUCT_IDDEMO_ID = DEMO_IDENTY_ID = V_E_ENTY_IDENTY_ID = ENTY_IDENTY_ID = F_C_ENTY_IDCOR_EVT_TYP_ID = COR_EVT_TYP_IDCOR_RPT_STRC_ID = COR_RPT_STRC_IDENTY_ID = CNTC_RSOL_EMP_IDGEO_ID = GEO_IDFNCL_SCOR_ID = FNCL_SCOR_

9、IDMEASURE_UNIT_ID = MEASURE_UNIT_IDCOR_EVT_TXN_ID = COR_EVT_TXN_IDLANGUAGE_ID = LANGUAGE_IDCOR_EVT_TXN_SEQ_NB = COR_EVT_TXN_SEQ_NBPN_BHVR_SCOR_ID = PN_BHVR_SCOR_IDPRODUCT_ID = PRODUCT_IDDEMO_ID = DEMO_IDENTY_ID = ENTY_IDFNCL_SCOR_ID = FNCL_SCOR_IDMEASURE_UNIT_ID = MEASURE_UNIT_IDDEMO_ID = DEMO_IDPRO

10、DUCT_ID = PRODUCT_IDPN_BHVR_SCOR_ID = PN_BHVR_SCOR_IDLANGUAGE_ID = LANGUAGE_IDFNCL_SCORES_ID = FNCL_SCOR_IDMEASURE_UNIT_ID = D_M_MEASURE_UNIT_IDMEASURE_UNIT_ID = MEASURE_UNIT_IDGEO_ID = GEO_IDCOR_RPT_STRC_ID = COR_RPT_STRC_IDEVT_TYP_ID = COR_EVT_TYP_IDENTY_ID = F_C_ENTY_IDGEO_ID = GEO_IDLANGUAGE_ID

11、= LANGUAGE_IDEVT_TYP_ID = EVT_TYP_IDDV_HR_EVT_TYPEEVT_TXN_IDINTEGEREVT_TYP_IDINTEGEREVT_TYP_SHRT_NMCHAREVT_TYP_FULL_NMcharEVT_TYP_CAT_SHRT_NCHAREVT_TYP_CAT_FULL_NcharF_HR_EVTV_E_ENTY_IDINTEGERV_E2_ENTY_IDINTEGEREVT_DT_PRD_IDINTEGERADMININTEGEREVT_EMP_IDINTEGEREVT_EMP_DEMOINTEGEREVT_ADMIN_DEMOINTEGER

12、CORE_EXT_IDINTEGERCORE_RPTG_STRUCINTEGERGEO_IDINTEGERMU_IDINTEGERFIN_SCORE_IDINTEGERLANGUAGE_IDINTEGERPB_SCORE_IDINTEGERF_C_ENTY_IDINTEGERPRODUCT_IDINTEGERDEMO_IDINTEGEREMP_IDINTEGERCDEX_SEQ_NOINTEGERQTYintegerF_CORE_EVTCOR_EVT_TXN_IDINTEGERCOR_EVT_TYP_IDINTEGERD_M_MEASURE_UNIT_IDINTEGERCOR_RPT_STRC

13、_IDINTEGERGEO_IDINTEGERMEASURE_UNIT_IDINTEGERFNCL_SCOR_IDINTEGERLANGUAGE_IDINTEGERPN_BHVR_SCOR_IDINTEGERPRODUCT_IDINTEGERDEMO_IDINTEGERENTY_IDINTEGERV_E_ENTY_IDINTEGERCOR_EVT_TXN_SEQ_NBNUMBERPRD_IDINTEGERAMOUNTNUMBERD_CORE_EVT_TYPEVT_TYP_IDINTEGEREVT_TYP_SHRT_NAMVARCHAR(15)EVT_TYP_LONG_NAMVARCHAR(35

14、)EVT_TYP_SUBTYP_NAMVARCHAR(15)D_CORE_RPT_STRCCOR_RPT_STRC_IDINTEGERHOLDING_COMPANYVARCHAR(35)ORG_TYPEVARCHAR(20)ORG_NAMEVARCHAR(35)REGIONVARCHAR(20)SALES_TEAM_TYPEVARCHAR(15)SALES_TEAMVARCHAR(15)SALES_PERSON_NAMEcharSALES_PERSON_GRADECHARSALES_PERSON_TYPECHARCHNL_CATEGORY1char(18)CHNL_TYPECHARCHNL_S

15、UBCATCHARCHNL_NAMEcharCHNL_CEASED_TRD_DTDATECHNL_ENTY_IDINTEGERCHNL_CITYVARCHAR(20)CHNL_POSTCODEVARCHAR(20)BEGIN_DATE_PRD_IDINTEGEREND_DATE_PRD_IDINTEGERD_GEOGRAPHYGEO_IDINTEGERALL_ENTRIESCHARPOSTAL_CODECHAR VARYING(15)CITYcharPOSTAL_CD_PFXchar(3)HZRD_WTHR_AREACHARHZD_WTHR_TYPECHARDMA_CODECHARSMSA_C

16、ODECHARST_PROV_AREACHARTV_REGIONCHARNTL_RADIO_AREACHARLCL_RADIO_AREACHARREGIONCHARCOUNTRYchar(3)CONTINENTY_ABBRchar(3)GEO_SUB_CNTNT_ABBRchar(3)SMRY_EFF_DTINTEGERSMRY_END_DTINTEGERPRISN_ADRS_INDCHARD_MSR_UNITMEASURE_UNIT_IDINTEGERSHRT_DESCchar(6)LONG_DESCchar(20)D_DEMOGRAPHICSDEMO_IDINTEGERALL_ENTRIE

17、SCHARINCOME_BANDVARCHAR(50)AGE_BANDVARCHAR(50)GNDRCHARMRTL_STATCHARHIGH_VALUE_INDICATCHARACMDTN_CTGRYCHARNBR_IN_HH_BANDVARCHAR(50)CHLD_AT_HOME_BANDVARCHAR(50)SIZE_CLSCHARLEGAL_ORG_TYPECHARNBR_EMP_BANDVARCHAR(50)SECTOR_CLSCHARMAIL_PRMSN_INDCHARTELMKT_PRMSN_INDCHARD_FNCL_SCORFNCL_SCORES_IDINTEGERINTER

18、NAL_FNCL_SCORVARCHAR(50)EXPERIAN_SCOR_BANDVARCHAR(50)SCOR_N_BANDVARCHAR(50)PRFT_IND_BANDVARCHAR(50)DEBT_INCOME_RATIONUMBERD_LANGUAGELANGUAGE_IDINTEGERISO_LANG_CODECHARISO_LANG_NAMEcharLANG_GROUPVARCHAR(20)D_PN_BHVR_SCORPN_BHVR_SCOR_IDINTEGERSCORE1_BANDVARCHAR(20)SCORE_N_BANDVARCHAR(20)D_PRODUCTPRODU

19、CT_IDINTEGERENTY_IDINTEGERPRODUCT_LINECHARPRODUCT_GROUPCHARPRODUCT_CODECHARPRODUCT_NAMECHARPD_VARIANT_CODECHARPRODUCT_VARIANTVARCHAR(35)GRP_INDV_INDCHARPD_START_PRD_IDINTEGERPD_END_PRD_IDINTEGERF_SALES_EVENTEVT_TXN_IDINTEGEREVT_TYP_IDINTEGERRPT_STRC_IDINTEGERMEASURE_UNIT_IDINTEGERFNCL_SCOR_IDINTEGER

20、PN_BHVR_SCOR_IDINTEGERENTY_IDINTEGEREMP_IDINTEGEREVT_TXN_SEQ_NBRINTEGERF_CUS_CNTC_EVTV_E_ENTY_IDINTEGERCUS_CNTC_IDINTEGERD_C_CTCT_RSOL_IDINTEGERLGCY_SYS_CUS_CNTCINTEGERCUS_CNTC_REFcharCUS_CNTC_EVT_IDINTEGERF_C_ENTY_IDINTEGERCUS_STSF_RT_IDINTEGERCNTC_INIT_DT_IDINTEGERHOUR_IDINTEGERMINUTE_IDINTEGERINI

21、T_CNTC_EMPcharCOR_EVT_TXN_IDINTEGERCOR_EVT_TYP_IDINTEGERCOR_RPT_STRC_IDINTEGERGEO_IDINTEGERMEASURE_UNIT_IDINTEGERFNCL_SCOR_IDINTEGERLANGUAGE_IDINTEGERPN_BHVR_SCOR_IDINTEGERPRODUCT_IDINTEGERDEMO_IDINTEGERCNTC_RSOL_EMP_IDINTEGERCUS_IDINTEGERSRSNS_CUS_CO_IDINTEGERDV_EMPENTY_IDINTEGERRPT_STRC_IDINTEGERG

22、EO_IDINTEGERADR_IDINTEGEREMP_DEMO_IDINTEGEREMP_NAME_PFXCHAREMP_SNAMEVARCHAR(15)EMP_FNAMEVARCHAR(15)EMP_MNAMEVARCHAR(15)EMP_NAME_SFXCHAREMP_NTL_INS_NBRCHAREMP_HOME_TEL_NBRCHAREMP_PRIM_FAX_NBRCHAREMP_EMAIL_IDINTEGEREMP_DOBDATEEMP_GNDRCHAREMP_MRTL_STATCHAREMP_LIFE_STATCHAREMP_PREF_LANGVARCHAR(20)F_CPGN

23、_CNTC_EVTCCE_IDINTEGERPROMO_EPSD_IDINTEGERENTY_IDINTEGERCNTC_PRD_IDintegerCCH_COUNTINTEGERCORE_EVT_TYPE_IDINTEGERCOR_RPTG_STRUCT_IDINTEGERGEO_IDINTEGERMU_IDINTEGERFINANCIAL_SCORE_IDINTEGERLANGUAGE_IDINTEGERPB_SCORE_IDINTEGERPRODUCT_IDINTEGERDEMO_IDINTEGEREMP_IDINTEGERCOR_EVT_TX_SEQ_NOSMALLINTTRGT_GR

24、Pchar(3)CORE_EVENTY_TYPE_IDINTEGERCNTCT_CNTRL_GRP_INCHARCCE_RESULTCHARP_PSYCH_IDINTEGERAFFILIATION_IDintPA_IDINTEGERCC_COMM_EVT_AMTdecimal(10,2)D_TIME_PERIODPRD_IDINTEGERDT_NAchar(4)DATEDATEDAY_NAMEchar(8)DAY_ABRchar(3)DAY_IN_WEEKSMALLINTDAY_IN_MONTHSMALLINTDAY_IN_YEARSMALLINTWEEK_IN_MONTHSMALLINTWE

25、EK_IN_YEARSMALLINTCLNT_SVC_WK_IN_YRchar(18)MONTH_NAMEchar(10)MONTH_ABRchar(3)MONTH_IN_YEARSMALLINTCALENDAR_QTRchar(6)MONTH_IN_QTRSMALLINTWEEK_IN_QTRSMALLINTDAY_IN_QTRSMALLINTFINANCIAL_QTRchar(6)COMPETITOR_FSCL_YRchar(6)MONTH_IN_FNCL_QTRSMALLINTWEEK_IN_FNCL_QTRSMALLINTDAY_IN_FNCL_QTRSMALLINTSEMI_YEAR

26、LYSMALLINTYEAR_NAMEchar(18)YEAR_ABRchar(4)SEASON_NAMEchar(18)SEASON_ABRchar(6)NBR_DAYS_SINCE_90integerHOLIDAY_INDCHARXMAS_HLDY_INDCHAREASTER_HLDY_INDCHARD_CPGN_COM_EVT_TYPEVT_TYP_IDINTEGERCPGN_COMM_DESCCHAR分析型CRM經(jīng)營業(yè)績管理Sybase Industry Warehouse Studio 分析型應(yīng)用框架第13頁Time資源搜集需求了解業(yè)務(wù)線設(shè)計模式ETL 模板結(jié)構(gòu)分析需求實施測試用戶反

27、饋精練測試第二代倉庫經(jīng)典數(shù)據(jù)倉庫項目從這里開始Sybase IWS 提供時間上價值 快速開啟數(shù)據(jù)倉庫項目搜集需求了解業(yè)務(wù)線設(shè)計模式ETL 模板結(jié)構(gòu)分析查詢實施測試第一代倉庫Sybase IWS從這里開始IWS節(jié)約 3 到 6 個月更多價值 =更加快地訪問信息第14頁Sybase Industry Warehouse StudioValue Proposition 回顧 預(yù)先建立業(yè)務(wù)和物理模型優(yōu)化了項目進度安排和加緊了對數(shù)據(jù)訪問基于經(jīng)過驗證實施經(jīng)驗和行業(yè)經(jīng)驗設(shè)計和方法論是可擴展/可定制安全企業(yè)范圍數(shù)據(jù)庫獨立面向行業(yè)集成模型和基礎(chǔ)平臺 靈巧節(jié)約資源 二分之一投入 節(jié)約時間 更加快實施節(jié)約資金 降低

28、成本 節(jié)約第15頁數(shù)據(jù)倉庫系統(tǒng)體系架構(gòu)RelationalPackageLegacyExternalsourceDataCleanToolSource DataDataStagingWareHouseAdmin. ToolsEnterprise DataWarehouse Data Extraction,Transformationand loadDatamartDatamartEnterprise/Central DataWarehouseRDBMSROLAPRDBMS Dimension ModelingConformed dimension&factIncluding atomic&ag

29、gregateArchitectedDatamartsCentralMetadata Data Modeling ToolEnd-UserToolEnd-UserToolMDBEnd-UserToolEnd-UserToolLocal MetadataLocal Metadata第16頁Adaptive Server IQ Multiplex是專門為滿足數(shù)據(jù)倉庫和商務(wù)智能設(shè)計高性能關(guān)系數(shù)據(jù)庫系統(tǒng)。IQ Multiplex主要特點是： 高可擴展性 支持?jǐn)?shù)以千計并發(fā)用戶存取TB級數(shù)據(jù)。 突破性速度 閃電般查詢速度，比傳統(tǒng)RDBMS快10 100倍以上。 無限靈活性 支持任意類型即席查詢。 最低擁有

30、總成本 高效數(shù)據(jù)壓縮存放，到達30% 60%；簡單維護和管理。第17頁集成主要產(chǎn)品DesignWarehouse ArchitectManageSybase ASIQMIntegrateInformatica Enterprise ConnectReplication ServerPowerMartVisualizeBo、BrioCognosSPSSAdministerWarehouse Control CenterWarehouseControlCentre第18頁Sybase數(shù)據(jù)倉庫相關(guān)產(chǎn)品集組成RelationalPackageLegacyExternalsourceDataCleanT

31、oolSource DataDataStagingWareHouseAdmin. ToolsEnterprise DataWarehouse Data Extraction,Transformationand loadDatamartDatamartEnterprise/Central DataWarehouseRDBMSROLAPRDBMSRDBMS, Star SchemaArchitectedDatamartsCentralMetadata Data Modeling ToolEnd-UserToolEnd-UserToolMDBEnd-UserToolEnd-UserToolLocal

32、 MetadataLocal MetadataPowerCenterPowerMartSybase IQMSybase IQMBrio/BOPowerMartWarehouseArchitectWCCCognos第19頁設(shè)計: 成功關(guān)鍵數(shù)據(jù)庫設(shè)計對數(shù)據(jù)倉庫系統(tǒng)整體性能、裝載和建立索引時間以及數(shù)據(jù)量增加等影響超出任何其它方面。第20頁數(shù)據(jù)倉庫設(shè)計在支持分析和決議查詢環(huán)境中，使業(yè)務(wù)用戶能夠訪問，了解和利用數(shù)據(jù)以業(yè)務(wù)用戶了解和利用信息方式組織數(shù)據(jù)可預(yù)見查詢方式基于時間匯總數(shù)據(jù)向下/上鉆取（Drill-down / drill-up）第21頁多維模型設(shè)計傳統(tǒng)數(shù)據(jù)建模方法(如ER模型)可能非常復(fù)雜且不易

33、了解按照最終用戶想法定義信息 (以查詢?yōu)橹行慕?Star(星型), Snowflake(雪花型)，Constellation(星座型)，Snowstorm(雪暴型)Facts(事實)： 可度量數(shù)據(jù)，如 數(shù)量、價格 Dimensions(維)：用于分類Fact詳細數(shù)據(jù)第22頁Grocery TransactionStore NumberTransaction DateCustomerProductQuantityAmountCustomerCustomerFrom DateTo DateFirst NameLast NameAddress 1Address 2Address 3CityStat

34、eCountryPostal CodeTimeTransaction DateStoreStore NumberStore NameCityStateCountryTelephoneProductProductDescriptionCategoryFact TableDimensionTablesDimensionTables多維模型: 星型模式第23頁Grocery TransactionStore NumberTransaction DateCustomerProductQuantityAmountCustomerCustomerFirst NameLast NameAddress 1Ad

35、dress 2Address 3CityStateCountryPostal CodeCustomer CategoryTimeTransaction DateStoreStore NumberStore NameCityStateCountryTelephoneRegionProductProductDescriptionCategoryProduct CategoryProduct CategoryDescriptionRegionRegionDescriptionSales PeriodPeriod IdentifierSales PeriodFrom DateTo DateCustom

36、er CategoryCategoryCustomer Category為了防止數(shù)據(jù)冗余, 用多張表來描述一個復(fù)雜維在星型模式基礎(chǔ)上, 結(jié)構(gòu)維表多層結(jié)構(gòu)多維模型: 雪花模式第24頁Grocery TransactionStore NumberTransaction DateCustomerProductPurchase QuantityAmountCustomerCustomerFirst NameLast NameAddress 1Address 2Address 3CityStateCountryPostal CodeCustomer CategoryTimeTransaction Dat

37、eStoreStore NumberStore NameCityStateCountryTelephoneRegionProductProductDescriptionCategoryProduct LineSales PeriodPeriod IdentifierSales PeriodFrom DateTo DateCustomer CategoryCategoryCustomer CategoryProduct PurchasesProductPurchase DateSupplying VendorPurchase OrderUnit QuantityPurchase CostVend

38、orVendorVendor NameAddress 1Address 2Address 3CityStateCountryPostal CodeProduct InventoryProductWarehouse LocationQuantity On HandQuantity Back OrderedWarehouseWarehouseAddress 1Address 2Address 3CityStateCountryPostal Code含有多個事實表多維模型: 星座模式第25頁Grocery TransactionStore NumberTransaction DateCustomer

39、ProductPurchase QuantityAmountCustomerCustomerFirst NameLast NameAddress 1Address 2Address 3CityStateCountryPostal CodeCustomer CategoryTimeTransaction DateStoreStore NumberStore NameCityStateCountryTelephoneRegionProductProductDescriptionCategoryProduct LineProduct CategoryProduct CategoryDescripti

40、onRegionRegionDescriptionSales PeriodPeriod IdentifierSales PeriodFrom DateTo DateCustomer CategoryCategoryCustomer CategoryPromotion PeriodPromotion IdPromotionFrom DateTo DateProduct LineProduct Line IDDescriptionProduct PurchasesProductPurchase DateSupplying VendorPurchase OrderUnit QuantityPurch

41、ase CostVendorVendorVendor NameAddress 1Address 2Address 3CityStateCountryPostal CodeProduct InventoryProductWarehouse LocationQuantity On HandQuantity Back OrderedWarehouseWarehouseAddress 1Address 2Address 3CityStateCountryPostal Code含有多個事實表與多層維表多維模型: 雪暴模式第26頁數(shù)據(jù)模型中事實和維度事實和維概念對應(yīng)于:數(shù)據(jù)倉庫數(shù)據(jù)庫中數(shù)據(jù)模型對象星型模式

42、（Star schema）DSS / OLAP 系統(tǒng)中數(shù)據(jù)模型對象多維模型（Multidimensional model）第27頁Sales factSales measuresTime dimensionAttributes of the time dimension星型模式-Star Schema第28頁Sales CubeSales measures(Metrics)Time dimensionAttributes of thetime dimension多維模型-Multidimensional Model第29頁數(shù)據(jù)倉庫設(shè)計工具WarehouseArchitect為數(shù)據(jù)倉庫設(shè)計提供三

43、大功效：多維建模度量、維、屬性事實表，維表維層次表，事實層次表設(shè)計向?qū)Ь酆希ˋggregation Wizard）分片（Partitioning Wizard）逆向工程數(shù)據(jù)源優(yōu)化代碼生成目標(biāo)數(shù)據(jù)倉庫引擎（IQM，RDBMS)OLAP分析環(huán)境第30頁Time identifier = Time identifierProduct identifier = Product identifierCustomer identifier = Customer identifierStore identifier = Store identifierCustomerCustomer identifierd

44、oubleCustomer namechar(30)Sales FactProduct identifierdoubleTime identifierdoubleCustomer identifierdoubleStore identifierdoubleSales totalrealProfitsrealStoreStore identifierdoubleStore namechar(50)TimeTime identifierdoubleDatetimestampMonthchar(50)QuarterdoubleYeardoubleProductProduct identifierdo

45、ubleProduct descriptionchar(80)WarehouseArchitect第31頁WarehouseArchitectData Warehouse or Data MartDatabaseOperational SourceOLAPEngineInterfaceExternal ObjectsDecision Support / OLAP Model (WA Multidimensional Hierarchy)DimensionalAnalysisTransformationRelational and/orDimensionalAnalysisData Wareho

46、use Model (WAM)WarehouseArchitect支持范圍第32頁數(shù)據(jù)倉庫設(shè)計-小結(jié)WarehouseArchitect對數(shù)據(jù)倉庫設(shè)計過程每一步都提供支持：數(shù)據(jù)源中元數(shù)據(jù)導(dǎo)入。設(shè)計和優(yōu)化數(shù)據(jù)倉庫數(shù)據(jù)模型（星型模式/多維模型）。與抽取、轉(zhuǎn)換工具對接，實施數(shù)據(jù)移動?；跀?shù)據(jù)倉庫模型，為前端DSS/OLAP工具生成所需數(shù)據(jù)立方體。為設(shè)計過程每一步生成文檔和匯報。第33頁數(shù)據(jù)存放、管理挑戰(zhàn)數(shù)據(jù)規(guī)模查詢性能裝載速度易于管理存取訪問成功關(guān)鍵快速，高效數(shù)據(jù)存放技術(shù)出眾查詢性能 - 特殊索引技術(shù)，并行查詢可伸縮性 - GB 到 TB 級易于管理 - 方便，靈活，GUI存取訪問 - 數(shù)據(jù)隨時可用

47、第34頁數(shù)據(jù)管理處理方案通用關(guān)系數(shù)據(jù)庫系統(tǒng)專門數(shù)據(jù)倉庫服務(wù)器Sybase IQM專門為數(shù)據(jù)倉庫/數(shù)據(jù)集市設(shè)計關(guān)系型數(shù)據(jù)庫專門針對OLAP/DSS而優(yōu)化索引和查詢處理技術(shù)第35頁Adaptive Server IQM數(shù)據(jù)存放： Adaptive Server IQM垂直存放技術(shù)(Vertical Partitioning)無處不索引(Index EVERYWHERE)專利Bit Wise索引技術(shù)跨越Bitmap限制各種索引類型：FP,LF,HNG,HG,CMP,WD低級數(shù)限制從100擴充到1000數(shù)據(jù)壓縮(通常到達原始數(shù)據(jù) 70 - 75%)預(yù)連接索引提供額外顯著提升性能伎倆(Join Inde

48、x)支持任意設(shè)計模式星型、雪花、雪暴、星座模式普通關(guān)系模式支持任意加載方式文件、內(nèi)部數(shù)據(jù)、外部數(shù)據(jù)庫直接加載開放接口第36頁Index傳統(tǒng)RDBMSRelational TableTypical RDBMS數(shù)據(jù)按行存放數(shù)據(jù)與索引分開存放極少索引類型 - B-樹普通關(guān)系數(shù)據(jù)庫為 OLTP系統(tǒng)進行優(yōu)化B-tree Index best for retrieving one row at a time第37頁計算“NY”州A類商店平均銷售額當(dāng)表統(tǒng)計數(shù)從幾萬條變?yōu)榍f和上億條時，傳統(tǒng)RDBMS技術(shù)面正確問題:表掃描性能極端低下冗余設(shè)計代價高昂、查詢讀取無效字段過多低級數(shù)類型數(shù)據(jù)上索引失效普通索引加載和

49、空間代價，造成不能任意建造即席查詢SQL次序?qū)π阅苡酗@著影響數(shù)值型比較和運算，無恰當(dāng)伎倆加速處理傳統(tǒng)RDBMS不適合數(shù)據(jù)倉庫第38頁IQM特殊存放方式-垂直存放（按列存放）Sybase IQM: 數(shù)據(jù)是按列存放，而不是按行存放好處: 只存取查詢所需數(shù)據(jù)數(shù)據(jù)類型是一致，因而能夠很輕易被壓縮數(shù)據(jù)庫易于修改和管理第39頁Sybase IQM: 只讀完成查詢所 包括到列計算在紐約“A”類商店平均銷售額好處: 無須使用其它技術(shù)，Sybase IQM就能夠降低I/O 超出 90%IQM特殊存放方式-垂直存放（按列存放）第40頁“How many MALES are NOT INSURED in CALIFORNIA?GenderMMFMM-800 Bytes/Row10MROWSStateNYCACTMACA-RDBMSInsuredYYNYNMYCAMNCAFYNYMNCA1243GenderInsuredState+11011101010110MBits10M Bits x 3 col / 816K Page = 235 I/Os800 Bytes x 10M 16K Page= 500,000 I/Os基本上只能使用表掃描查詢過程讀取了太多無效數(shù)據(jù)IQMExample: I/O 顯著降低第41頁IQM索引特點索引即是數(shù)據(jù)沒有索引和數(shù)據(jù)分別任何一列能夠建