微软BI体系结构

报表平台（BI）体系设计-关注于报表/统计分析/商业智能,邓英221713609702242yindengMicrosoftCorporation,内容,第一部分：体系的理解与用处第二部分：体系结构的设计1.总体结构2.数据建模3.功能模块,传统报表技术的难点,基于平台的架构-功能性报表性能-支持静态与动态报表业务系统的影响面向业务的灵活性前端展现的易用性开发和维护成本,实现报表系统（商业智能）的平台组件,商业智能项目的通用实现模式,源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、功能模块,微软商业智能体系要点：强调集成,DataMartsandcubes,DataWarehouse,SourceSystems,OLTP,1,3,4,2,DataWarehouse,DataMarts/OLAP/Cubes,Front-EndPortalorTools,BusinessIntelligence,相关产品模块,AnalysisServicesOLAP&DataMining,DataTransformationServices（SSIS）,SQLServerRelationalEngine,ReportingServices,ManagementTools,DevtoolsVisualStudio.Net,ExcelOWCMapPointDataAnalyzerBalanceScoreCard,SharePointPortalServer,WindowsServer,WindowsClient,微软商业智能体系要点：强调OLAP,财务经理的视角,产品经理的视角,某些特殊视角,销售经理的视角,销售数据,产品,时间,市场,OLAP的基本概念-维度和度量,二维到多维,度量,维度,层次,海量数据的处理：T3,世界最大的多维数据集-MOLAPCube中1.2TB的源数据DW77亿行RDW进入到MOLAP中（440GB）50个并发用户中值查询时间=0.02-0.08sec反映了真实的公司架构、业务流程和数据,微软商业智能体系要点：重视闭环,直接联系,电话中心,其它,外部交互应用,站点,销售,内部交互应用,市场,服务,内部最终用户访问,数据仓库(DW)and/orODS,内部安全访问,后台系统,数据分析报表工具,ERP,external,operational,e-commerce,other,数据挖掘数据报表数据分析,数据集市,微软BI的典型架构,体系结构的设计,1、总体结构2、数据建模3、功能模块,TheStarSchema,FactTable,DimensionTable,Sales_Fact,TimeKeyEmployeeKeyProductKeyCustomerKeyShipperKey,SalesAmountUnitSales.,多维结构的价值,GroceryTransaction,StoreNumber,TransactionDate,Customer,Product,Quantity,Amount,Time,TransactionDate,SalesPeriod,PeriodIdentifier,SalesPeriod,FromDate,ToDate,取决于企业结构与时间的使用方式季度上半年/下半年是否传统节假日或西方节假日财政年的月份月份是上旬、中旬还是下旬星期几？今年的第几个星期？,多维模型:四种模式,星型模式(StarSchema)雪花模式(SnowflakeSchema)星座模式(ConstellationSchema)雪暴模式(SnowstormSchema),多维模型:雪花模式,GroceryTransaction,StoreNumber,TransactionDate,Customer,Product,Quantity,Amount,Customer,Customer,FirstName,LastName,Address1,Address2,Address3,City,State,Country,PostalCode,CustomerCategory,Time,TransactionDate,Store,StoreNumber,StoreName,City,State,Country,Telephone,Region,Product,Product,Description,Category,ProductCategory,ProductCategory,Description,Region,Region,Description,SalesPeriod,PeriodIdentifier,SalesPeriod,FromDate,ToDate,CustomerCategory,Category,CustomerCategory,为了避免数据冗余,用多张表来描述一个复杂维在星型模式的基础上,构造维表的多层结构,多维模型:星座模式,GroceryTransaction,StoreNumber,TransactionDate,Customer,Product,PurchaseQuantity,Amount,Customer,Customer,FirstName,LastName,Address1,Address2,Address3,City,State,Country,PostalCode,CustomerCategory,Time,TransactionDate,Store,StoreNumber,StoreName,City,State,Country,Telephone,Region,Product,Product,Description,Category,ProductLine,SalesPeriod,PeriodIdentifier,SalesPeriod,FromDate,ToDate,CustomerCategory,Category,CustomerCategory,ProductPurchases,Product,PurchaseDate,SupplyingVendor,PurchaseOrder,UnitQuantity,PurchaseCost,Vendor,Vendor,VendorName,Address1,Address2,Address3,City,State,Country,PostalCode,ProductInventory,Product,WarehouseLocation,QuantityOnHand,QuantityBackOrdered,Warehouse,Warehouse,Address1,Address2,Address3,City,State,Country,PostalCode,具有多个事实表,多维模型:雪暴模式,GroceryTransaction,StoreNumber,TransactionDate,Customer,Product,PurchaseQuantity,Amount,Customer,Customer,FirstName,LastName,Address1,Address2,Address3,City,State,Country,PostalCode,CustomerCategory,Time,TransactionDate,Store,StoreNumber,StoreName,City,State,Country,Telephone,Region,Product,Product,Description,Category,ProductLine,ProductCategory,ProductCategory,Description,Region,Region,Description,SalesPeriod,PeriodIdentifier,SalesPeriod,FromDate,ToDate,CustomerCategory,Category,CustomerCategory,PromotionPeriod,PromotionId,Promotion,FromDate,ToDate,ProductLine,ProductLineID,Description,ProductPurchases,Product,PurchaseDate,SupplyingVendor,PurchaseOrder,UnitQuantity,PurchaseCost,Vendor,Vendor,VendorName,Address1,Address2,Address3,City,State,Country,PostalCode,ProductInventory,Product,WarehouseLocation,QuantityOnHand,QuantityBackOrdered,Warehouse,Warehouse,Address1,Address2,Address3,City,State,Country,PostalCode,具有多个事实表与多层维表,确定事实表的组成,DimensionTables,customer_dim,201ALFIAlfreds,product_dim,25123Chai,Sales_factTable,customer_key,product_key,time_key,quantity_sales,amount_sales,ForeignKeys,201,25,134,400,10,789,Thegrainofthesales_facttableisdefinedbythelowestlevelofdetailstoredineachdimensionassociatedwiththefacttableThegrainofthesales_facttableissalesdatabycustomerID,productID,andorderdate,描述维度,DescribingBusinessEntitiesContainingAttributesThatProvideContexttoNumericDataPresentingDataOrganizedintoHierarchies,建模的原则,粒度设计数据稳定性设计事实表存储设计维表主键的选择层次结构的设计雪化模型中的维表退化维的设计变化维的设计,体系结构的设计,1、总体结构2、数据建模3、功能模块,EM2000,QA2000,AM2000,EM2000,QA2000,AM2000,SQL2000,SQL2005,SQLManagementStudio,BIDevelopmentStudio,New,SQLServer2005管理工具,SQLServer2005DTS-SQLServerIntegrationServices(SSIS),SSIS组件,完善,丰富的流水线组件(Transforms)Source,DestinationAggregation,DerivedcolumnMerge,Sort,Pivot,SamplingConditionalsplit,multicast,SlowlychangingdimensionDataMing,OLAP,Fuzzylookup,SSISexample,SQLServer2005-OLAP,Multi-DimensionOLAP,“嘿我卖掉了价值1亿美元的货物”,维度：产品、地区、时间分层汇总路径产品地区时间行业国家年类别地区季度产品城市月周部门天,分析OLAP,数据分析软件钻取切片比较多维分析:MOLAP,ROLAP,HOLAP,性能与空间的平衡,Aggregationwizardfindsthe“80-20”ruleinthedataThe20percentofallpossiblepre-aggregationsthatprovide80percentoftheperformancegainAnalyseslevelcountsforeachdimensionsandparent-childratiosforeachlevel,1)January,February,March2000Sales,Client,Server,2)Quarter12000Sales,3)Quarter11999Sales,1)January,February,March2000Sales,3)Quarter11999Sales,ClientCalculates,OnlyQuarter11999NeededFromServer,IntelligentCaching,SQLServer2005ReportingServices,Reportingservices组件,报表服务是一个开放的可扩展的平台，它具有支持授权、管理与在整个企业内传递丰富的交互式报告的能力。,ReportDefinition,ManagedReport,DeliveryC