微软BI体系结构

Click to edit Title Slide,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,报表平台（,BI,）体系设计,-,关注于报表,/,统计分析,/,商业智能,邓英达,020-38771111-2217,Microsoft Corporation,内容,第一部分：体系的理解与用处,第二部分：体系结构的设计,1.,总体结构,2.,数据建模,3.,功能模块,传统报表技术的难点,基于平台的架构,-,功能性报表,性能,-,支持静态与动态报表,业务系统的影响,面向业务的灵活性,前端展现的易用性,开发和维护成本,实现报表系统（商业智能）的平台组件,Data Warehouse,Data Access,前端报表用户,Data,Sources,Data Input,Staging,Area,Data Marts,商业智能项目的通用实现模式,源,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,、功能模块,微软商业智能体系要点：强调集成,Data Marts and cubes,Data,Warehouse,Source,Systems,OLTP,1,3,4,2,Data Warehouse,Data Marts/OLAP/Cubes,Front-End Portal or Tools,Business Intelligence,相关产品模块,Analysis Services,OLAP&Data Mining,Data Transformation Services,（,SSIS,）,SQL Server,Relational Engine,Reporting Services,Management Tools,Dev tools,Visual Studio.Net,Excel,OWC,Map Point,Data Analyzer,Balance Score Card,SharePoint Portal,Server,Windows Server,Windows Client,微软商业智能体系要点：强调,OLAP,财务经理的视角,产品经理的视角,某些特殊视角,销售经理的视角,销售数据,产品,时间,市,场,OLAP,的基本概念,-,维度和度量,二维到多维,度量,维度,层次,海量数据的处理：,T,3,世界最大的多维数据集-,MOLAP Cube,中1.2,TB,的源数据,DW,77,亿行,RDW,进入到,MOLAP,中（440,GB）,50,个并发用户,中值查询时间,=0.02-0.08 sec,反映了真实的公司架构、业务流程和数据,微软商业智能体系要点：重视闭环,直接联系,电话中心,其它,外部交互应用,站点,销售,内部交互应用,市场,服务,内部最终,用户访问,数据仓库,(DW),and/or ODS,内部,安全,访问,后台系统,数据分析报表工具,ERP,external,operational,e-commerce,other,数据挖掘,数据报表,数据分析,Data,Marts,数据集市,微软,BI,的典型架构,体系结构的设计,1,、总体结构,2,、数据建模,3,、功能模块,The Star Schema,Fact Table,Dimension Table,Employee_Dim,EmployeeKey,EmployeeID,.,Time_Dim,TimeKey,TheDate,.,Product_Dim,ProductKey,ProductID,.,Customer_Dim,CustomerKey,CustomerID,.,Shipper_Dim,ShipperKey,ShipperID,.,Sales_Fact,TimeKey,EmployeeKey,ProductKey,CustomerKey,ShipperKey,Sales Amount,Unit Sales.,多维结构的价值,Grocery Transaction,Store Number,Transaction Date,Customer,Product,Quantity,Amount,Time,Transaction Date,Sales Period,Period Identifier,Sales Period,From Date,To Date,取决于企业结构与时间的使,用方式,季度,上半年,/,下半年,是否传统节假日或西方节假日,财政年的月份,月份是上旬、中旬还是下旬,星期几？今年的第几个星期？,多维模型,:,四种模式,星型模式,(Star Schema),雪花模式,(Snowflake Schema),星座模式,(Constellation Schema),雪暴模式,(Snowstorm Schema),多维模型,:,雪花模式,Grocery Transaction,Store Number,Transaction Date,Customer,Product,Quantity,Amount,Customer,Customer,First Name,Last Name,Address 1,Address 2,Address 3,City,State,Country,Postal Code,Customer Category,Time,Transaction Date,Store,Store Number,Store Name,City,State,Country,Telephone,Region,Product,Product,Description,Category,Product Category,Product Category,Description,Region,Region,Description,Sales Period,Period Identifier,Sales Period,From Date,To Date,Customer Category,Category,Customer Category,为了避免数据冗余,用多张表来描述一个复杂维,在星型模式的基础上,构造维表的多层结构,多维模型,:,星座模式,Grocery Transaction,Store Number,Transaction Date,Customer,Product,Purchase Quantity,Amount,Customer,Customer,First Name,Last Name,Address 1,Address 2,Address 3,City,State,Country,Postal Code,Customer Category,Time,Transaction Date,Store,Store Number,Store Name,City,State,Country,Telephone,Region,Product,Product,Description,Category,Product Line,Sales Period,Period Identifier,Sales Period,From Date,To Date,Customer Category,Category,Customer Category,Product Purchases,Product,Purchase Date,Supplying Vendor,Purchase Order,Unit Quantity,Purchase Cost,Vendor,Vendor,Vendor Name,Address 1,Address 2,Address 3,City,State,Country,Postal Code,Product Inventory,Product,Warehouse Location,Quantity On Hand,Quantity Back Ordered,Warehouse,Warehouse,Address 1,Address 2,Address 3,City,State,Country,Postal Code,具有多个事实表,多维模型,:,雪暴模式,Grocery Transaction,Store Number,Transaction Date,Customer,Product,Purchase Quantity,Amount,Customer,Customer,First Name,Last Name,Address 1,Address 2,Address 3,City,State,Country,Postal Code,Customer Category,Time,Transaction Date,Store,Store Number,Store Name,City,State,Country,Telephone,Region,Product,Product,Description,Category,Product Line,Product Category,Product Category,Description,Region,Region,Description,Sales Period,Period Identifier,Sales Period,From Date,To Date,Customer Category,Category,Customer Category,Promotion Period,Promotion Id,Promotion,From Date,To Date,Product Line,Product Line ID,Description,Product Purchases,Product,Purchase Date,Supplying Vendor,Purchase Order,Unit Quantity,Purchase Cost,Vendor,Vendor,Vendor Name,Address 1,Address 2,Address 3,City,State,Country,Postal Code,Product Inventory,Product,Warehouse Location,Quantity On Hand,Quantity Back Ordered,Warehouse,Warehouse,Address 1,Address 2,Address 3,City,State,Country,Postal Code,具有多个事实表与多层维表,确定事实表的组成,DimensionTables,customer_dim,201,ALFI,Alfreds,product_dim,25,123,Chai,Sales_fact Table,customer_key,product_key,time_key,quantity_sales,amount_sales,Foreign Keys,201,25,134,400,10,789,The,grain,of the sales_fact table is defined by the lowest level of detail stored in each dimension a