微軟BI體系結(jié)構(gòu).

1、 Data Warehouse Data Access 前端報(bào)表用戶前端報(bào)表用戶 Data Sources Data Input Staging Area Data Marts 財(cái)務(wù)經(jīng)理的視角財(cái)務(wù)經(jīng)理的視角 產(chǎn)品經(jīng)理的視角產(chǎn)品經(jīng)理的視角某些特殊視角某些特殊視角 銷售經(jīng)理的視角銷售經(jīng)理的視角 銷售數(shù)據(jù)銷售數(shù)據(jù) 產(chǎn)品產(chǎn)品 時(shí)間時(shí)間 市市 場(chǎng)場(chǎng) 內(nèi)部最終 用戶訪問(wèn) 內(nèi)部?jī)?nèi)部 安全安全 訪問(wèn)訪問(wèn) 后臺(tái)系統(tǒng)數(shù)據(jù)分析報(bào)表工具 ERP, external, operational, e-commerce, other 數(shù)據(jù)挖掘 數(shù)據(jù)報(bào)表 數(shù)據(jù)分析 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 . 多維結(jié)構(gòu)的價(jià)值多維結(jié)構(gòu)的價(jià)值 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取決于企業(yè)結(jié)構(gòu)與時(shí)間的使取決于企業(yè)結(jié)構(gòu)與時(shí)間的使 用方式 p 季度 p 上半年/下半年 p 是否傳統(tǒng)節(jié)假日或西方節(jié)假日 p 財(cái)政年的月份 p 月份是上旬、中旬還是下旬 p 星期幾？今年的第幾個(gè)星期？ 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為了避免數(shù)據(jù)冗余為了避免數(shù)據(jù)冗余, 用多張表來(lái)描述一個(gè)復(fù)雜維用多張表來(lái)描述一個(gè)復(fù)雜維 n在星型模式的基礎(chǔ)上在星型模式的基礎(chǔ)上, 構(gòu)造維表的多層結(jié)構(gòu)構(gòu)造維表的多層結(jié)構(gòu) 多維模型:

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具有多個(gè)事實(shí)表具有多個(gè)事實(shí)表 多維模型: 雪暴模式 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具有多個(gè)事實(shí)表與多層維表具有多個(gè)事實(shí)表與多層維表 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 針對(duì)目標(biāo): DBA Developer New u“嘿嘿 我賣掉了價(jià)值我賣掉了價(jià)值1億美元的

14、貨物億美元的貨物” 月份月份 1 2 3 4 76 5 產(chǎn)品產(chǎn)品 牙膏牙膏 果汁果汁 可樂(lè)可樂(lè) 牛奶牛奶 奶油奶油 肥皂肥皂 地區(qū)地區(qū) 西西 南南 北北 維度：產(chǎn)品、地區(qū)、時(shí)間維度：產(chǎn)品、地區(qū)、時(shí)間 分層匯總路徑分層匯總路徑 產(chǎn)品產(chǎn)品 地區(qū)地區(qū) 時(shí)間時(shí)間 行業(yè)行業(yè) 國(guó)家國(guó)家 年年 類別類別 地區(qū)地區(qū) 季度季度 產(chǎn)品產(chǎn)品 城市城市 月月 周周 部門部門 天天 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 報(bào)表服務(wù)是一個(gè)開放的可擴(kuò)展的平臺(tái)，它具有支報(bào)表服務(wù)是一個(gè)開放的可擴(kuò)展的平臺(tái)，它具有支 持授權(quán)、管理與在整個(gè)企業(yè)內(nèi)傳遞豐富的交互式持授權(quán)、管理與在整個(gè)企業(yè)內(nèi)傳遞豐富的交互式 報(bào)告的能力。報(bào)告的能力。 Report Definition Managed Report D