《微軟BI體系結構.》由會員分享，可在線閱讀，更多相關《微軟BI體系結構.(41頁珍藏版)》請在人人文庫網上搜索。

1、Data Warehouse Data Access 前端報表用戶前端報表用戶 Data Sources Data Input Staging Area Data Marts 財務經理的視角財務經理的視角 產品經理的視角產品經理的視角某些特殊視角某些特殊視角 銷售經理的視角銷售經理的視角 銷售數據銷售數據 產品產品 時間時間 市市 場場 內部最終 用戶訪問 內部內部 安全安全 訪問訪問 后臺系統數據分析報表工具 ERP, external, operational, e-commerce, other 數據挖掘 數據報表 數據分析 Data Marts Fact Table Dimension。

2、 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 Quanti。

3、ty 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 Produc。

5、t 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 Regi。

7、on 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 Addr。

8、ess 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 Pr。

9、oduct 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 Categ。

10、ory 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 Cou。

12、ntry 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 a。

13、ssociated 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) Quarte。

15、r 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 Delivery Channels Online Access AuthoringManagementDelivery OLAP Reports (Adhoc) Reports (Static) Data Mining Business Knowledge Easy Difficult Usability Relative Business Value SQL Server 2005 - Complete Platform for Business Intelligence。