微软BI体系结构.

1、 Data Warehouse Data Access 前端报表用户前端报表用户 Data Sources Data Input Staging Area Data Marts 财务经理的视角财务经理的视角 产品经理的视角产品经理的视角某些特殊视角某些特殊视角 销售经理的视角销售经理的视角 销售数据销售数据 产品产品 时间时间 市市 场场 内部最终 用户访问 内部内部 安全安全 访问访问 后台系统数据分析报表工具 ERP, external, operational, e-commerce, other 数据挖掘 数据报表 数据分析 Data Marts Fact Table Dimensio

2、n Table EmployeeKey EmployeeID . TimeKey TheDate . ProductKey ProductID . CustomerKey CustomerID . ShipperKey ShipperID . Sales_Fact TimeKey EmployeeKey ProductKey CustomerKey ShipperKey Sales Amount Unit Sales . 多维结构的价值多维结构的价值 Grocery Transaction Store Number Transaction Date Customer Product Quant

3、ity Amount Time Transaction Date Sales Period Period Identifier Sales Period From Date To Date n取决于企业结构与时间的使取决于企业结构与时间的使 用方式 p 季度 p 上半年/下半年 p 是否传统节假日或西方节假日 p 财政年的月份 p 月份是上旬、中旬还是下旬 p 星期几？今年的第几个星期？ l 星型模式(Star Schema) l 雪花模式(Snowflake Schema) l 星座模式(Constellation Schema) l 雪暴模式(Snowstorm Schema) 多维模型:

4、 雪花模式 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 Produ

5、ct 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 n为了避免数据冗余为了避免数据冗余, 用多张表来描述一个复杂维用多张表来描述一个复杂维 n在星型模式的基础上在星型模式的基础上, 构造维表的多层结构构造维表的多层结构 多维模型:

6、 星座模式 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 Reg

7、ion 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 Add

8、ress 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 n具有多个事实表具有多个事实表 多维模型: 雪暴模式 Grocery Transaction Store Number Transaction Date Customer P

9、roduct 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 Cate

10、gory 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

11、 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 Co

12、untry Postal Code n具有多个事实表与多层维表具有多个事实表与多层维表 Dimension Tables 201 ALFI Alfreds 25 123 Chai Sales_fact Table customer_key product_key time_key quantity_sales amount_sales Foreign Keys 2012513440010,789 The grain of the sales_fact table is defined by the lowest level of detail stored in each dimension

13、associated with the fact table The grain of the sales_fact table is sales data by customer ID, product ID, and order date 134 1/1/2000 Measures EM 2000 QA 2000 AM 2000 EM 2000 QA 2000 AM 2000 SQL 2000 SQL 2005 SQL Management Studio BI Development Studio 针对目标: DBA Developer New u“嘿嘿 我卖掉了价值我卖掉了价值1亿美元的

14、货物亿美元的货物” 月份月份 1 2 3 4 76 5 产品产品 牙膏牙膏 果汁果汁 可乐可乐 牛奶牛奶 奶油奶油 肥皂肥皂 地区地区 西西 南南 北北 维度：产品、地区、时间维度：产品、地区、时间 分层汇总路径分层汇总路径 产品产品 地区地区 时间时间 行业行业 国家国家 年年 类别类别 地区地区 季度季度 产品产品 城市城市 月月 周周 部门部门 天天 1) January, February, March 2000 Sales Query 1) January, February, and March 2000 Sales 2) Quarter 1 2000 Sales 3) Quart

15、er 1 1999 Sales 1) January, February, March 2000 Sales 3) Quarter 1 1999 Sales Query 2) Quarter 1 2000 Sales Query 3) Quarter 1 2000 Sales and Quarter 1 1999 Sales Client Calculates Only Quarter 1 1999 Needed From Server 报表服务是一个开放的可扩展的平台，它具有支报表服务是一个开放的可扩展的平台，它具有支 持授权、管理与在整个企业内传递丰富的交互式持授权、管理与在整个企业内传递丰富的交互式 报告的能力。报告的能力。 Report Definition Managed Report D