1、第五章 范 例 設(shè)計一個數(shù)據(jù)倉庫首先要面對的是哪些是事實數(shù)據(jù)，而哪些是維度數(shù)據(jù)。在一個大型的OLTP系統(tǒng)中字段眾多，要在其中決定事實與維度數(shù)據(jù)并不容易。識別事實與維度設(shè)計事實表設(shè)計維度表識別事實與維度1）在整個OLTP系統(tǒng)中搜索最基本的交易，它們極可能是事實數(shù)據(jù)2）決定搜索每一個事實數(shù)據(jù)的外鍵，它們極可能是維度數(shù)據(jù)3）檢驗每一個可能事實數(shù)據(jù)的字段，確定它不是嵌入在事實數(shù)據(jù)中的維度數(shù)據(jù)4）檢驗每一個可能維度數(shù)據(jù)的字段，確定它不是嵌入在維度數(shù)據(jù)中的事實數(shù)據(jù)設(shè)計事實表一個事實表是由OLTP系統(tǒng)轉(zhuǎn)入而生成出來的，數(shù)據(jù)倉庫的數(shù)據(jù)并不包含OLTP系統(tǒng)中所有的數(shù)據(jù)。在設(shè)計一個事實表的時候，考慮下面的事項：1

2、）為每一項功能決定數(shù)據(jù)倉庫的時距（十年，五年還是其它？）2）為每一項功能決定其采用原則3）決定在事實表中應(yīng)包含哪些字段4）盡量縮小事實表中字段的大小5）將時間因素加入事實表設(shè)計維度表在設(shè)計之初要確定不會更新數(shù)據(jù)的主鍵，否則一旦修改，事實表要一起更新。維度表常是違反正規(guī)化的。時間一般都是一個事實表的維度例子 Northwind數(shù)據(jù)庫一個貿(mào)易公司商業(yè)所使用的數(shù)據(jù)庫（SQL Server 2000的范例）數(shù)據(jù)庫架構(gòu)（1）Categories表：存儲產(chǎn)品所有類型的相關(guān)信息字段名功能描述CategoryID產(chǎn)品類型的識別碼CategoryName產(chǎn)品名稱Description產(chǎn)品類型描述Picture產(chǎn)

3、品圖片數(shù)據(jù)庫架構(gòu)（2）CustomerDemo表：存儲了顧客所屬類別信息（3）CustomerDemographics表：存儲了顧客類別的描述信息字段名功能描述CustomerID顧客識別碼CustomerTypeID顧客類別識別碼字段名功能描述CustomerTypeID顧客類別識別碼CustomerDesc顧客類別的描述數(shù)據(jù)庫架構(gòu)（4）Customers表：存儲了顧客所有相關(guān)信息字段名功能描述CustomerID顧客識別碼CustomerName顧客姓名Address地址City城市Region地區(qū)PostalCode郵政編碼Country國家Phone電話Fax傳真CompanyName

4、顧客單位（公司）數(shù)據(jù)庫架構(gòu)（5）Employees表：存儲了員工所有相關(guān)信息Extension公司內(nèi)部分機號Photo照片Notes員工信息描述字段名功能描述EmployeeID員工識別碼EmployeeName員工姓名BirthDate出生年月HireDate雇傭日期Address地址City城市Region地區(qū)PostalCode郵政編碼HomePhone家庭電話Title員工職務(wù)數(shù)據(jù)庫架構(gòu)（6）EmployeesTerritories表：存儲了員工所負責(zé)的區(qū)域（7）Territories表：存儲了員工所負責(zé)的區(qū)域的基本數(shù)據(jù)字段名功能描述EmployeeID員工識別碼TerritoryID

5、員工負責(zé)區(qū)域識別碼字段名功能描述TerritoryID區(qū)域識別碼TerritoryDescription區(qū)域描述RegionID所屬地區(qū)識別碼數(shù)據(jù)庫架構(gòu)（8）Region：存儲了員工所負責(zé)的區(qū)域所屬區(qū)的基本數(shù)據(jù)（9）OrderDetail：存儲訂單下的單項商品信息字段名功能描述RegionID地區(qū)識別碼RegionDescription地區(qū)名稱字段名功能描述OrderID訂單識別號ProductID產(chǎn)品識別號UnitPrice產(chǎn)品單價Quantity訂購數(shù)量Discount折扣數(shù)據(jù)庫架構(gòu)（10）Orders：存儲訂單的全部信息Feight貨運價格ShipName接受貨品人姓名ShipAddre

6、ss送貨地址字段名功能描述OrderID訂單識別號CustomerID顧客識別號EmployeeID承辦員工識別號OrderDate訂購日期RequiredDate訂單的需要日期ShippedDate送貨日期ShipVia送貨公司識別碼ShipCity送貨城市ShipRegion送貨地區(qū)ShipPostalCode送貨區(qū)郵政編碼數(shù)據(jù)庫架構(gòu)（11）Shippers：存儲了貨運公司的相關(guān)信息字段名功能描述ShipperID貨運公司識別號CompanyName貨運公司名稱Phone貨運公司電話號碼數(shù)據(jù)庫架構(gòu)（12）Products：產(chǎn)品的相關(guān)信息UnitOnOrder一次訂貨量ReorderLeve

7、l重新訂貨最低庫存量Discontinued是否停售字段名功能描述ProductID產(chǎn)品識別號ProductName產(chǎn)品名稱SupplierID供貨商識別號CategoryID產(chǎn)品分類識別號QuantityPerUnit每單位數(shù)量UnitPrice單價UnitInStock庫存量數(shù)據(jù)庫架構(gòu)（13）Suppliers：供貨商的相關(guān)信息phone電話Fax傳真HomePage公司網(wǎng)址字段名功能描述SupplierID供貨商識別號CompanyName公司名稱ContactName聯(lián)絡(luò)人姓名Address地址City城市Region地區(qū)PostalCode郵政編碼表間關(guān)系Employeer員工表Em

8、ployeerTerritoriesTerritories區(qū)域表Region地區(qū)表Customer顧客表CustomerCustomorDemo顧客類別CustomorDemographics顧客描述Orders訂單表OrdersDetail訂單詳情表Shippers貨運公司Products產(chǎn)品表Categories產(chǎn)品類別表Suppliers供貨商表需求分析經(jīng)過調(diào)查后，假定得到了下面的需求：希望能夠針對每一個員工做銷售業(yè)績分析希望能夠針對每一產(chǎn)品做銷售分析希望能夠針對每一分類產(chǎn)品做銷售分析希望能夠針對每一供貨商做銷售分析希望能夠針對每一顧客做銷售分析希望能夠針對每一地區(qū)的顧客做銷售分析希望能

9、夠針對每一城市的顧客做銷售分析希望能夠針對年、季、月做銷售分析識別事實和維度經(jīng)歸納發(fā)現(xiàn)，索引基準(zhǔn)點為5類：顧客員工產(chǎn)品供貨商時間分析過程Employeer員工表EmployeerTerritoriesTerritories區(qū)域表Region地區(qū)表Customer顧客表CustomerCustomorDemo顧客類別CustomorDemographics顧客描述Orders訂單表OrdersDetail訂單詳情表Shippers貨運公司Products產(chǎn)品表Categories產(chǎn)品類別表Suppliers供貨商表8個記錄0個記錄0個記錄91個記錄9個記錄49個記錄2155個記錄830個記錄4個

10、記錄53個記錄29個記錄3個記錄77個記錄貨運在分析中不出現(xiàn)，可去掉分析過程Employeer員工表EmployeerTerritoriesTerritories區(qū)域表Region地區(qū)表Customer顧客表CustomerCustomorDemo顧客類別CustomorDemographics顧客描述Orders訂單表OrdersDetail訂單詳情表Products產(chǎn)品表Categories產(chǎn)品類別表Suppliers供貨商表8個記錄0個記錄0個記錄91個記錄9個記錄49個記錄2155個記錄830個記錄4個記錄53個記錄29個記錄77個記錄員工負責(zé)的區(qū)域及其區(qū)域所屬地區(qū)與分析無關(guān)，去掉三個

11、表Employeer員工表Customer顧客表CustomerCustomorDemo顧客類別CustomorDemographics顧客描述Orders訂單表OrdersDetail訂單詳情表Products產(chǎn)品表Categories產(chǎn)品類別表Suppliers供貨商表8個記錄0個記錄0個記錄91個記錄9個記錄2155個記錄830個記錄29個記錄77個記錄顧客類別及描述在分析中不會感興趣，而且為0個記錄，所以也不考慮分析過程Employeer員工表Customer顧客表Orders訂單表OrdersDetail訂單詳情表Products產(chǎn)品表Categories產(chǎn)品類別表Suppliers

12、供貨商表字段名功能描述CategoryID產(chǎn)品類型的識別碼CategoryName產(chǎn)品名稱Description產(chǎn)品類型描述Picture產(chǎn)品圖片分析過程Employeer員工表Customer顧客表Orders訂單表OrdersDetail訂單詳情表Products產(chǎn)品表Categories產(chǎn)品類別表Suppliers供貨商表UnitOnOrder一次訂貨量ReorderLevel重新訂貨最低庫存量Discontinued是否停售字段名功能描述ProductID產(chǎn)品識別號ProductName產(chǎn)品名稱SupplierID供貨商識別號CategoryID產(chǎn)品分類識別號QuantityPerUn

13、it每單位數(shù)量UnitPrice單價UnitInStock庫存量分析過程Employeer員工表Customer顧客表Orders訂單表OrdersDetail訂單詳情表Products產(chǎn)品表Categories產(chǎn)品類別表Suppliers供貨商表phone電話Fax傳真HomePage公司網(wǎng)址字段名功能描述SupplierID供貨商識別號CompanyName公司名稱ContactName聯(lián)絡(luò)人姓名Address地址City城市Region地區(qū)PostalCode郵政編碼分析過程Employeer員工表Customer顧客表Orders訂單表OrdersDetail訂單詳情表Products

14、產(chǎn)品表Categories產(chǎn)品類別表Suppliers供貨商表HomePhone家庭電話Extension公司內(nèi)部分機號Photo照片Notes員工信息描述字段名功能描述EmployeeID員工識別碼EmployeeName員工姓名BirthDate出生年月HireDate雇傭日期Address地址City城市Region地區(qū)PostalCode郵政編碼Title員工職務(wù)分析過程Employeer員工表Customer顧客表Orders訂單表OrdersDetail訂單詳情表Products產(chǎn)品表Categories產(chǎn)品類別表Suppliers供貨商表字段名功能描述CustomerID顧客識別

15、碼CustomerName顧客姓名Address地址City城市Region地區(qū)PostalCode郵政編碼Country國家Phone電話Fax傳真CompanyName顧客單位（公司）分析過程Employeer員工表Customer顧客表Orders訂單表OrdersDetail訂單詳情表Products產(chǎn)品表Categories產(chǎn)品類別表Suppliers供貨商表Feight貨運價格ShipName接受貨品人姓名ShipAddress送貨地址字段名功能描述OrderID訂單識別號CustomerID顧客識別號EmployeeID承辦員工識別號OrderDate訂購日期RequiredDa

16、te訂單的需要日期ShippedDate送貨日期ShipVia送貨公司識別碼ShipCity送貨城市ShipRegion送貨地區(qū)ShipPostalCode送貨區(qū)郵政編碼分析過程Employeer員工表Customer顧客表Orders訂單表OrdersDetail訂單詳情表Products產(chǎn)品表Categories產(chǎn)品類別表Suppliers供貨商表字段名功能描述OrderID訂單識別號ProductID產(chǎn)品識別號UnitPrice產(chǎn)品單價Quantity訂購數(shù)量Discount折扣分析過程識別事實與維度數(shù)據(jù)顧客維度員工維度供應(yīng)商維度時間維度產(chǎn)品維度產(chǎn)品維度Categories:產(chǎn)品類別描述

17、Products:產(chǎn)品類別描述字段名功能描述CategoryID產(chǎn)品類型的識別碼CategoryName產(chǎn)品名稱CategoryID,CategoryName:維度數(shù)據(jù)，屬于產(chǎn)品維度字段名ProductIDProductName產(chǎn)品名稱SupplierID供貨商識別號CategoryID產(chǎn)品分類識別號QuantityPerUnitUnitPrice功能描述產(chǎn)品識別號每單位數(shù)量單價每單位數(shù)量、單價是同公司運營有關(guān)的數(shù)據(jù)，不隨時間改變，也不會以每單位數(shù)量和單價為基準(zhǔn)來分析數(shù)據(jù)，所以是事實數(shù)據(jù)顧客維度Customers:顧客描述字段名功能描述CustomerID顧客識別碼CustomerName顧客

18、姓名City城市Region地區(qū)Country國家CustomerID、CustomerName:屬于顧客維度數(shù)據(jù)City 、Region、Country:屬于顧客維度數(shù)據(jù)，且具有層次關(guān)系員工維度Employees:員工描述字段名功能描述EmployeeID員工識別碼Name員工姓名Title員工職務(wù)都是維度數(shù)據(jù)，屬于員工維度事實數(shù)據(jù)Orders:訂單描述字段名功能描述OrderID訂單識別號CustomerID顧客識別號EmployeeID承辦員工識別號OrderDate訂購日期訂單號是因公司經(jīng)營而產(chǎn)生，不隨時間變化，不會以訂單號為基準(zhǔn)分析，所以是事實數(shù)據(jù)CustomerID:顧客維度數(shù)據(jù)E

19、mployeeID:員工維度數(shù)據(jù)OrderDate:時間維度數(shù)據(jù)事實數(shù)據(jù)OrdersDetail:產(chǎn)品項描述字段名功能描述OrderID訂單識別號ProductID產(chǎn)品識別號UnitPrice產(chǎn)品單價Quantity訂購數(shù)量Discount折扣隨公司運營產(chǎn)生的有訂單號、產(chǎn)品單價、訂購數(shù)量和折扣，它們不隨時間變化。也不會以訂單號、產(chǎn)品單價、訂購數(shù)量和折扣為基準(zhǔn)分析數(shù)據(jù)，所以是事實數(shù)據(jù)。ProductID是產(chǎn)品維度數(shù)據(jù)設(shè)計事實表事實表名稱：Sales數(shù)據(jù)源：Orders,OrderDtails,Employees,Products,Suppliers, Customers索引：EmployeeID

20、,來自Employees表ProcutID,來自Products表CustomerID,來自Customers表OrderDate,來自O(shè)rders表事實表度量值字段：UnitPrice,來自O(shè)rderDetails表Total=Quantity*UnitPrice*(1.0-Discount)Quantity,來自O(shè)rderDetails表Discount,來自O(shè)rderDetails表數(shù)據(jù)抽取SQL語句SELECT e.EmployeeID,p.ProductID,s.SuppliersID, c.CustomerID,o.OrderDate,od.UnitPrice, od.Quanti

21、ty*od.UnitPrice*(1.0-od.Discount) Total, od.Quantity,od.DiscountFROM Orders o,OrderDetails od, Employees e, Products p,Suppliers s,Customers cWHERE o.OrderID=od.OrderID ANDo.EmployeeID=e.EmployeeID ANDo.CustomerID=c.CustomerID ANDod.ProductID=s.ProductID ANDp.SuppliersID=s.SuppliersID設(shè)計員工維度表員工維度表名稱：

22、Employee數(shù)據(jù)源：Employees表數(shù)據(jù)字段：EmoloyeeID,來自Employees表EmployeeName,來自Employees表Title,來自Employees表SQL語句 SELECT EmployeeID,EmployeeName,Title FROM Employees數(shù)據(jù)架構(gòu)：星型架構(gòu)主鍵字段：EmployeeID設(shè)計產(chǎn)品維度表產(chǎn)品維度表名稱：Product數(shù)據(jù)源：Products表數(shù)據(jù)字段：ProductID,來自Products表CategoryID,來自Products表ProductName,來自Products表QuantityPerUnit:來自Pr

23、oducts表SQL語句SELECT ProductID,ProductName,QuantityPerUnitFROM Products數(shù)據(jù)架構(gòu)：雪花式架構(gòu)主鍵字段：ProductID設(shè)計產(chǎn)品維度表產(chǎn)品分類維度表名稱：Category數(shù)據(jù)源：Categories表數(shù)據(jù)字段：CategoryID,來自Categories表CategoryName,來自Categoies表SQL語句 SELECT CategoryID,CategoryName FROM Categories數(shù)據(jù)架構(gòu)：雪花式架構(gòu)主鍵字段：CategoryID設(shè)計供應(yīng)商維度表供貨商維度表名稱：Supplier數(shù)據(jù)源：Supplie

24、rs表數(shù)據(jù)字段：SupplierID,來自Suppliers表 panyNameCompanyName,來自Suppliers表SQL語句 SELECT SupplierID, CompanyName, SupplierName FROM Suppliers數(shù)據(jù)架構(gòu)：星型架構(gòu)主鍵字段：SupplierID設(shè)計顧客維度顧客維度表名稱：Customer數(shù)據(jù)源：Customers表數(shù)據(jù)字段：CustomerID,來自Customers表CustomerName,=來自Customers表City,來自Customers表Country,來自Customers表SQL語句 SELECT CustomerID, CustomerName,City,Region, Country FROM Suppliers數(shù)據(jù)架構(gòu)：星型架構(gòu)層次字段：Country|Region|City主鍵字段：CustomerID設(shè)計時間維度表時間維度表名稱：Time數(shù)據(jù)源：Orders表數(shù)據(jù)字段：OrderDate,來自O(shè)rd