微软BI体系结构

报表平台〔BI〕体系设计

----关注于报表/统计分析/商业智能内容第一局部：体系的理解与用处第二局部：体系结构的设计1.总体结构2.数据建模3.功能模块传统报表技术的难点基于平台的架构---功能性报表性能---支持静态与动态报表业务系统的影响面向业务的灵活性前端展现的易用性开发和维护本钱实现报表系统〔商业智能〕的平台组件DataWarehouseDataAccess前端报表用户DataSources

DataInputStagingAreaDataMarts商业智能工程的通用实现模式源DB+前端工具〔包括报表统计模块〕源DB+OLAP+前端工具源DB+ODS+前端工具源DB+ODS+OLAP+前端工具源DB+DW+前端工具源DB+DW+OLAP+前端工具源DB+ODS+DW+OLAP+前端工具源DB+ODS+DW+DataMart+OLAP+前端工具考虑的因素性能---OLAP与报表Cache数据的集成性---集成的数据存储业务的灵活性---面向业务的设计满足更多需求---数据建模使用方便性---可以由客户定制的报表/基于WEB的使用模式第二局部：微软BI体系结构体系结构的设计1、总体结构2、数据建模3、功能模块微软商业智能体系要点：强调集成DataMartsandcubesDataWarehouseSourceSystemsOLTP1342DataWarehouseDataMarts/OLAP/CubesFront-EndPortalorToolsBusinessIntelligence相关产品模块AnalysisServicesOLAP&DataMiningDataTransformationServices〔SSIS〕SQLServerRelationalEngineReportingServicesManagementToolsDevtoolsVisualStudio.NetExcelOWCMapPointDataAnalyzerBalanceScoreCardSharePointPortalServerWindowsServerWindowsClient微软商业智能体系要点：强调OLAP财务经理的视角产品经理的视角某些特殊视角销售经理的视角销售数据产品时间市场OLAP的根本概念--维度和度量二维到多维度量维度层次海量数据的处理：T3世界最大的多维数据集-MOLAPCube中1.2TB的源数据DW77亿行RDW进入到MOLAP中〔440GB〕50个并发用户中值查询时间=0.02-0.08sec反映了真实的公司架构、业务流程和数据微软商业智能体系要点：重视闭环直接联系中心其它外部交互应用站点销售内部交互应用市场效劳内部最终用户访问数据仓库(DW)and/or

ODS内部平安访问后台系统数据分析报表工具ERP,external,operational,

e-commerce,other数据挖掘数据报表数据分析DataMarts数据集市微软BI的典型架构体系结构的设计1、总体结构2、数据建模3、功能模块TheStarSchemaFactTableDimensionTableEmployee_DimEmployeeKeyEmployeeID...Time_DimTimeKeyTheDate...Product_DimProductKeyProductID...Customer_DimCustomerKeyCustomerID...Shipper_DimShipperKeyShipperID...Sales_FactTimeKeyEmployeeKeyProductKeyCustomerKeyShipperKeySalesAmountUnitSales...多维结构的价值GroceryTransactionStoreNumberTransactionDateCustomerProductQuantityAmountTimeTransactionDateSalesPeriodPeriodIdentifierSalesPeriodFromDateToDate取决于企业结构与时间的使用方式季度上半年/下半年是否传统节假日或西方节假日财政年的月份月份是上旬、中旬还是下旬星期几？今年的第几个星期？多维模型:四种模式星型模式(StarSchema)雪花模式(SnowflakeSchema)星座模式(ConstellationSchema)雪暴模式(SnowstormSchema)多维模型:雪花模式GroceryTransactionStoreNumberTransactionDateCustomerProductQuantityAmountCustomerCustomerFirstNameLastNameAddress1Address2Address3CityStateCountryPostalCodeCustomerCategoryTimeTransactionDateStoreStoreNumberStoreNameCityStateCountryTelephoneRegionProductProductDescriptionCategoryProductCategoryProductCategoryDescriptionRegionRegionDescriptionSalesPeriodPeriodIdentifierSalesPeriodFromDateToDateCustomerCategoryCategoryCustomerCategory为了防止数据冗余,用多张表来描述一个复杂维在星型模式的根底上,构造维表的多层结构多维模型:星座模式GroceryTransactionStoreNumberTransactionDateCustomerProductPurchaseQuantityAmountCustomerCustomerFirstNameLastNameAddress1Address2Address3CityStateCountryPostalCodeCustomerCategoryTimeTransactionDateStoreStoreNumberStoreNameCityStateCountryTelephoneRegionProductProductDescriptionCategoryProductLineSalesPeriodPeriodIdentifierSalesPeriodFromDateToDateCustomerCategoryCategoryCustomerCategoryProductPurchasesProductPurchaseDateSupplyingVendorPurchaseOrderUnitQuantityPurchaseCostVendorVendorVendorNameAddress1Address2Address3CityStateCountryPostalCodeProductInventoryProductWarehouseLocationQuantityOnHandQuantityBackOrderedWarehouseWarehouseAddress1Address2Address3CityStateCountryPostalCode具有多个事实表多维模型:雪暴模式GroceryTransactionStoreNumberTransactionDateCustomerProductPurchaseQuantityAmountCustomerCustomerFirstNameLastNameAddress1Address2Address3CityStateCountryPostalCodeCustomerCategoryTimeTransactionDateStoreStoreNumberStoreNameCityStateCountryTelephoneRegionProductProductDescriptionCategoryProductLineProductCategoryProductCategoryDescriptionRegionRegionDescriptionSalesPeriodPeriodIdentifierSalesPeriodFromDateToDateCustomerCategoryCategoryCustomerCategoryPromotionPeriodPromotionIdPromotionFromDateToDateProductLineProductLineIDDescriptionProductPurchasesProductPurchaseDateSupplyingVendorPurchaseOrderUnitQuantityPurchaseCostVendorVendorVendorNameAddress1Address2Address3CityStateCountryPostalCodeProductInventoryProductWarehouseLocationQuantityOnHandQuantityBackOrderedWarehouseWarehouseAddress1Address2Address3CityStateCountryPostalCode具有多个事实表与多层维表确定事实表的组成Dimension

Tablescustomer_dim201ALFI

Alfreds

product_dim

25123Chai

Sales_factTablecustomer_keyproduct_keytime_keyquantity_salesamount_salesForeignKeys2012513440010,789Thegrain

ofthesales_facttableisdefinedbythelowestlevelofdetailstoredineachdimensionassociatedwiththefacttableThegrainofthesales_facttableissalesdatabycustomerID,productID,andorderdate1341/1/2000

time_dimMeasures描述维度DescribingBusinessEntitiesContainingAttributesThatProvideContexttoNumericDataPresentingDataOrganizedintoHierarchies建模的原那么粒度设计数据稳定性设计事实表存储设计维表主键的选择层次结构的设计雪化模型中的维表退化维的设计变化维的设计……体系结构的设计1、总体结构2、数据建模3、功能模块EM2000QA2000AM2000EM2000QA2000AM2000SQL2000SQL2005SQLManagementStudioBIDevelopmentStudio

针对目标:DBADeveloperNewSQLServer2005管理工具SQLServer2005DTS--

SQLServerIntegrationServices(SSIS)SSIS组件完善,丰富的流水线组件(Transforms)Source,DestinationAggregation,DerivedcolumnMerge,Sort,Pivot,SamplingConditionalsplit,multicast,SlowlychangingdimensionDataMing,OLAP,Fuzzylookup…SSISexampleSQLServer2005-OLAPMulti-DimensionOLAP“嘿…我卖掉了价值1亿美元的货物〞月份1234765产品牙膏果汁可乐牛奶奶油肥皂地区西南北维度：产品、地区、时间分层汇总路径

产品

地区

时间

行业国家年

类别地区季度

产品城市月周

部门天分析–OLAP数据分析软件钻取切片比较多维分析:MOLAP,ROLAP,HOLAP性能与空间的平衡Aggregationwizardfindsthe“80-20〞ruleinthedataThe20percentofallpossiblepre-aggregationsthatprovide80percentoftheperformancegainAnalyseslevelcountsforeachdimensionsandparent-childratiosforeachlevel1)January,February,March2000SalesQuery1)January,February,andMarch2000SalesClientServer2)Quarter12000Sales3)Quarter11999Sales1)January,February,March2000Sales3)Quarter11999SalesQuery2)Quarter12000SalesQuery3)Quarter12000SalesandQuarter11999SalesClientCalcul