微軟BI體系結(jié)構(gòu)

微軟BI體系結(jié)構(gòu)第一頁，共41頁。內(nèi)容第一部分：體系的理解與用處第二部分：體系結(jié)構(gòu)的設(shè)計(jì)1.總體結(jié)構(gòu)2.數(shù)據(jù)建模3.功能模塊第二頁，共41頁。傳統(tǒng)報(bào)表技術(shù)的難點(diǎn)基于平臺(tái)的架構(gòu)---功能性報(bào)表性能---支持靜態(tài)與動(dòng)態(tài)報(bào)表業(yè)務(wù)系統(tǒng)的影響面向業(yè)務(wù)的靈活性前端展現(xiàn)的易用性開發(fā)和維護(hù)成本第三頁，共41頁。實(shí)現(xiàn)報(bào)表系統(tǒng)（商業(yè)智能）的平臺(tái)組件DataWarehouseDataAccess前端報(bào)表用戶DataSources

DataInputStagingAreaDataMarts第四頁，共41頁。商業(yè)智能項(xiàng)目的通用實(shí)現(xiàn)模式源DB+前端工具（包括報(bào)表統(tǒng)計(jì)模塊）源DB+OLAP+前端工具源DB+ODS+前端工具源DB+ODS+OLAP+前端工具源DB+DW+前端工具源DB+DW+OLAP+前端工具源DB+ODS+DW+OLAP+前端工具源DB+ODS+DW+DataMart+OLAP+前端工具第五頁，共41頁?？紤]的因素性能---OLAP與報(bào)表Cache數(shù)據(jù)的集成性---集成的數(shù)據(jù)存儲(chǔ)業(yè)務(wù)的靈活性---面向業(yè)務(wù)的設(shè)計(jì)滿足更多需求---數(shù)據(jù)建模使用方便性---可以由客戶定制的報(bào)表/基于WEB的使用模式第六頁，共41頁。第二部分：微軟BI體系結(jié)構(gòu)第七頁，共41頁。體系結(jié)構(gòu)的設(shè)計(jì)1、總體結(jié)構(gòu)2、數(shù)據(jù)建模3、功能模塊第八頁，共41頁。微軟商業(yè)智能體系要點(diǎn)：強(qiáng)調(diào)集成DataMartsandcubesDataWarehouseSourceSystemsOLTP1342DataWarehouseDataMarts/OLAP/CubesFront-EndPortalorToolsBusinessIntelligence第九頁，共41頁。相關(guān)產(chǎn)品模塊AnalysisServicesOLAP&DataMiningDataTransformationServices（SSIS）SQLServerRelationalEngineReportingServicesManagementToolsDevtoolsVisualStudio.NetExcelOWCMapPointDataAnalyzerBalanceScoreCardSharePointPortalServerWindowsServerWindowsClient第十頁，共41頁。微軟商業(yè)智能體系要點(diǎn)：強(qiáng)調(diào)OLAP財(cái)務(wù)經(jīng)理的視角產(chǎn)品經(jīng)理的視角某些特殊視角銷售經(jīng)理的視角銷售數(shù)據(jù)產(chǎn)品時(shí)間市場(chǎng)第十一頁，共41頁。OLAP的基本概念--維度和度量二維到多維度量維度層次第十二頁，共41頁。海量數(shù)據(jù)的處理：T3世界最大的多維數(shù)據(jù)集-MOLAPCube中1.2TB的源數(shù)據(jù)DW77億行RDW進(jìn)入到MOLAP中（440GB）50個(gè)并發(fā)用戶中值查詢時(shí)間=0.02-0.08sec反映了真實(shí)的公司架構(gòu)、業(yè)務(wù)流程和數(shù)據(jù)第十三頁，共41頁。微軟商業(yè)智能體系要點(diǎn)：重視閉環(huán)直接聯(lián)系電話中心其它外部交互應(yīng)用站點(diǎn)銷售內(nèi)部交互應(yīng)用市場(chǎng)服務(wù)內(nèi)部最終用戶訪問數(shù)據(jù)倉庫(DW)and/or

ODS內(nèi)部安全訪問后臺(tái)系統(tǒng)數(shù)據(jù)分析報(bào)表工具ERP,external,operational,

e-commerce,other數(shù)據(jù)挖掘數(shù)據(jù)報(bào)表數(shù)據(jù)分析DataMarts數(shù)據(jù)集市第十四頁，共41頁。微軟BI的典型架構(gòu)第十五頁，共41頁。體系結(jié)構(gòu)的設(shè)計(jì)1、總體結(jié)構(gòu)2、數(shù)據(jù)建模3、功能模塊第十六頁，共41頁。TheStarSchemaFactTableDimensionTableEmployee_DimEmployeeKeyEmployeeID...Time_DimTimeKeyTheDate...Product_DimProductKeyProductID...Customer_DimCustomerKeyCustomerID...Shipper_DimShipperKeyShipperID...Sales_FactTimeKeyEmployeeKeyProductKeyCustomerKeyShipperKeySalesAmountUnitSales...第十七頁，共41頁。多維結(jié)構(gòu)的價(jià)值GroceryTransactionStoreNumberTransactionDateCustomerProductQuantityAmountTimeTransactionDateSalesPeriodPeriodIdentifierSalesPeriodFromDateToDate取決于企業(yè)結(jié)構(gòu)與時(shí)間的使用方式季度上半年/下半年是否傳統(tǒng)節(jié)假日或西方節(jié)假日財(cái)政年的月份月份是上旬、中旬還是下旬星期幾？今年的第幾個(gè)星期？第十八頁，共41頁。多維模型:四種模式星型模式(StarSchema)雪花模式(SnowflakeSchema)星座模式(ConstellationSchema)雪暴模式(SnowstormSchema)第十九頁，共41頁。多維模型:雪花模式GroceryTransactionStoreNumberTransactionDateCustomerProductQuantityAmountCustomerCustomerFirstNameLastNameAddress1Address2Address3CityStateCountryPostalCodeCustomerCategoryTimeTransactionDateStoreStoreNumberStoreNameCityStateCountryTelephoneRegionProductProductDescriptionCategoryProductCategoryProductCategoryDescriptionRegionRegionDescriptionSalesPeriodPeriodIdentifierSalesPeriodFromDateToDateCustomerCategoryCategoryCustomerCategory為了避免數(shù)據(jù)冗余,用多張表來描述一個(gè)復(fù)雜維在星型模式的基礎(chǔ)上,構(gòu)造維表的多層結(jié)構(gòu)第二十頁，共41頁。多維模型:星座模式GroceryTransactionStoreNumberTransactionDateCustomerProductPurchaseQuantityAmountCustomerCustomerFirstNameLastNameAddress1Address2Address3CityStateCountryPostalCodeCustomerCategoryTimeTransactionDateStoreStoreNumberStoreNameCityStateCountryTelephoneRegionProductProductDescriptionCategoryProductLineSalesPeriodPeriodIdentifierSalesPeriodFromDateToDateCustomerCategoryCategoryCustomerCategoryProductPurchasesProductPurchaseDateSupplyingVendorPurchaseOrderUnitQuantityPurchaseCostVendorVendorVendorNameAddress1Address2Address3CityStateCountryPostalCodeProductInventoryProductWarehouseLocationQuantityOnHandQuantityBackOrderedWarehouseWarehouseAddress1Address2Address3CityStateCountryPostalCode具有多個(gè)事實(shí)表第二十一頁，共41頁。多維模型:雪暴模式GroceryTransactionStoreNumberTransactionDateCustomerProductPurchaseQuantityAmountCustomerCustomerFirstNameLastNameAddress1Address2Address3CityStateCountryPostalCodeCustomerCategoryTimeTransactionDateStoreStoreNumberStoreNameCityStateCountryTelephoneRegionProductProductDescriptionCategoryProductLineProductCategoryProductCategoryDescriptionRegionRegionDescriptionSalesPeriodPeriodIdentifierSalesPeriodFromDateToDateCustomerCategoryCategoryCustomerCategoryPromotionPeriodPromotionIdPromotionFromDateToDateProductLineProductLineIDDescriptionProductPurchasesProductPurchaseDateSupplyingVendorPurchaseOrderUnitQuantityPurchaseCostVendorVendorVendorNameAddress1Address2Address3CityStateCountryPostalCodeProductInventoryProductWarehouseLocationQuantityOnHandQuantityBackOrderedWarehouseWarehouseAddress1Address2Address3CityStateCountryPostalCode具有多個(gè)事實(shí)表與多層維表第二十二頁，共41頁。確定事實(shí)表的組成Dimension

Tablescustomer_dim201ALFI

Alfreds

product_dim

25123Chai

Sales_factTablecustomer_keyproduct_keytime_keyquantity_salesamount_salesForeignKeys2012513440010,789Thegrain

ofthesales_facttableisdefinedbythelowestlevelofdetailstoredineachdimensionassociatedwiththefacttableThegrainofthesales_facttableissalesdatabycustomerID,productID,andorderdate1341/1/2000

time_dimMeasures第二十三頁，共41頁。描述維度DescribingBusinessEntitiesContainingAttributesThatProvideContexttoNumericDataPresentingDataOrganizedintoHierarchies第二十四頁，共41頁。建模的原則粒度設(shè)計(jì)數(shù)據(jù)穩(wěn)定性設(shè)計(jì)事實(shí)表存儲(chǔ)設(shè)計(jì)維表主鍵的選擇層次結(jié)構(gòu)的設(shè)計(jì)雪化模型中的維表退化維的設(shè)計(jì)變化維的設(shè)計(jì)……第二十五頁，共41頁。體系結(jié)構(gòu)的設(shè)計(jì)1、總體結(jié)構(gòu)2、數(shù)據(jù)建模3、功能模塊第二十六頁，共41頁。EM2000QA2000AM2000EM2000QA2000AM2000SQL2000SQL2005SQLManagementStudioBIDevelopmentStudio

針對(duì)目標(biāo):DBADeveloperNewSQLServer2005管理工具第二十七頁，共41頁。SQLServer2005DTS--

SQLServerIntegrationServices(SSIS)第二十八頁，共41頁。SSIS組件完善,豐富的流水線組件(Transforms)Source,DestinationAggregation,DerivedcolumnMerge,Sort,Pivot,SamplingConditionalsplit,multicast,SlowlychangingdimensionDataMing,OLAP,Fuzzylookup…第二十九頁，共41頁。SSISexample第三十頁，共41頁。SQLServer2005-OLAP第三十一頁，共41頁。Multi-DimensionOLAP“嘿…我賣掉了價(jià)值1億美元的貨物”

月份1234765產(chǎn)品牙膏果汁可樂牛奶奶油肥皂地區(qū)西南北維度：產(chǎn)品、地區(qū)、時(shí)間分層匯總路徑

產(chǎn)品

地區(qū)

時(shí)間

行業(yè)國(guó)家年

類別地區(qū)季度

產(chǎn)品城市月周

部門天第三十二頁，共41頁。分析–OLAP數(shù)據(jù)分析軟件鉆取切片比較多維分析:MOLAP,ROLAP,HOLAP第三十三頁，共41頁。性能與空間的平衡Aggregationwizardfindsthe“80-20”ruleinthedataThe20percentofallpossiblepre-aggregationsthatprovide80percentoftheperformancegainAnalyseslevelcountsforeachdimensionsandparent-childratiosforeachlevel第三十四頁，共41頁。1)January,February,March2000SalesQuery1)January,February,andMarch2000SalesClientServer2)Quarter12000Sales3)Quarter11999Sales1)January,February,March2000Sales3)Quarter11999SalesQuery2)Quarter12000SalesQuery3)Quarter12000SalesandQuarter11999S