Transcript 設計資料倉儲範例

CHAP 5 設計資料倉儲綱要
利用SQL Server 2000 的
Northwind 資料庫
示範資料庫如何轉換到資料倉儲
產生星狀或雪花綱要
Reference
資料倉儲與Analysis Service
SQL Server 2000 OLAP 解決方
案（沈兆陽，文魁）
1
資料庫應用
匯入Northwind(北風資料庫）


2
在SQL Server 2005 匯入SQL Server 2000 的
Northwind(北風資料庫）
請執行程式：SQL2005_Insert_Northwind.sql
5_設計資料倉儲綱要
Northwind 資料庫簡介

Northwind ﹕



3
一個貿易公司營運所使用的資料庫
是一個OLTP的資料庫
 以交易為處理單位
 以正規化的方式來設計
 擁有較多資料表，但每個資料表擁有的欄位數較少
這是OLTP資料庫的特性之ㄧ
安裝好SQL Server 2000後，此資料庫即存在
5_設計資料倉儲綱要
Northwind 綱要介紹
－含13個資料表（1）
1.
4
Categories：產品類型檔（ 4
個欄位，共 8 筆記錄）
 CategoryID：產品類型
的識別號碼主鍵
 CategoryName：產品
類型的名稱
 Description：產品類型
的描述
 Picture：產品的圖片
2.
CustomerCustomerDemo：顧
客所屬類別檔（2個欄位，0筆記
錄）
 CustomerID：顧客識別號碼
複合主鍵
 CustomerTypeID：顧客類別
識別碼複合主鍵
5_設計資料倉儲綱要
Northwind 綱要介紹
－含13個資料表（2）
3.
CustomerDemographics：顧
客類別主檔（ 2個欄位，0筆記
錄）

CustomerTypeID：顧客類
別識別碼主鍵

CustomerDesc：顧客類別
描述
4.
Customers：顧客檔（ 11個欄
位，共91筆記錄）











5
CustomerID：顧客識別號碼主
鍵
CustomerName：顧客公司名稱
ContactName：聯絡人姓名
ContactTitle：聯絡人職稱
Address：顧客住址
City：城市名稱
Region：地區名稱
PostalCode：郵遞區號
Country：國家名稱
Phone：電話號碼
Fax：傳真號碼
5_設計資料倉儲綱要
Northwind 綱要介紹
－含13個資料表（3）
5.
6
Employees ：員工檔（ 18個欄位，共9筆記錄）
6.

EmployeeID：員工識別號碼(主鍵)

LastName：員工姓氏

FirstName：員工名字

Title：員工職稱

TitleOfCourtesy：員工的尊稱（例如：先
生、女士）

BirthDate：生日

HireDate：僱用日期

Address：住址

City：城市名稱

Region：地區名稱

PostalCode：郵遞區號

Country：國家名稱

HomePhone：住家電話

Extension：公司內分機號碼

Photo：照片

Notes：員工相關描述

ReportsTo：員工經理的員工號碼

PhotoPath：照片的路徑名稱
EmployeeTerritories ：員工
所屬區域檔（ 2個欄位，共49
筆記錄）
 EmployeeID：員工識別號
碼複合主鍵
 TerritoryID：員工負責區域
識別碼複合主鍵
P.S. 一個員工可以負責多個區域
5_設計資料倉儲綱要
Northwind 綱要介紹
－含13個資料表（4）
7.
Order Details：訂單詳細檔
（ 5個欄位，共2155筆記錄）

OrderID：訂單號碼複
合主鍵

ProductID：產品識別碼
複合主鍵

UnitPrice：產品單價

Quantity：訂購數量

Discount：折扣
8.
Orders：訂單檔（ 14個欄位，共
830筆記錄）













7

OrderID：訂單識別號碼複合主鍵
CustomerID：顧客識別碼複合主
鍵
EmplyoeeID：承辦員工的員工號碼
OrderDate：訂購日期
RequiredDate：訂單的需要日期
ShippedDate：送貨日期
ShipVia：負責貨運公司的識別碼
Freight：貨運的價格（運費）
ShipName：送貨姓名（接受者）
ShipAddress：送貨住址
ShipCity：送貨的城市名稱
ShipRegion：送貨的地區名稱
ShipPostalCode：送貨的郵遞區號
ShipCountry：送貨的國家名稱
5_設計資料倉儲綱要
Northwind 綱要介紹
－含13個資料表（5）
9.
8
Products：產品檔（ 10個欄位，共 10.
77筆記錄）

ProductID：產品識別碼主鍵

ProductName：產品名稱

SupplierID：供應商識別碼

CategoryID：產品分類識別碼

QuantityPerUnit：每單位的尺寸、
大小、數量

UnitPrice：產品單價

UuitsInStock：庫存數量

UuitsOnOrder：一次訂貨數量

ReorderLevel：重新訂貨最低庫
存量

Discontinued：是否停售
Region ：區域資料檔（ 2個欄
位，共4筆記錄）
 RegionID：區域的識別號
碼主鍵
 RegionDescription：區域
的名稱
5_設計資料倉儲綱要
Northwind 綱要介紹
－含13個資料表（6）
11.
Shippers ：貨運公司資料檔
（ 3個欄位，共3筆記錄）

ShipperID：貨運公司的識
別號碼主鍵

CompanyName：貨運公
司的名稱

Phone：貨運公司的電話
號碼
12.
Suppliers：供應商資料檔（ 12個
欄位，共29筆記錄）












9
SupplierID：供應商的識別號碼
主鍵
CompanyName：公司名稱
ContactName：聯絡人姓名
ContactTitle：聯絡人職稱
Address：住址
City：城市名稱
Region：地區名稱
PostalCode：郵遞區號
Country：國家名稱
Phone：電話號碼
Fax：傳真號碼
Homepage：公司網址
5_設計資料倉儲綱要
Northwind 綱要介紹
－含13個資料表（7）
13.
Territories ：員工負責區域資料檔（ 3個欄位，共53筆記錄）



TerritoryID：區域的識別號碼主鍵
TerritoryDescription：區域的描述
RegionID：所屬地區的識別碼
P.S. 一筆Region資料可以對應多筆Territory資料
10
5_設計資料倉儲綱要
Northwind–關聯圖
11
5_設計資料倉儲綱要
需求分析

需求彙總結果
1.
2.
3.
4.
5.
6.
7.
8.
9.
12
針對每一員工做銷售業績分析
針對每一產品做銷售業績分析
針對每一分類的產品做銷售分析
針對每一供應商做產品銷售分析
針對每一顧客做銷售分析
針對每一國家的顧客做銷售分析
針對每一地區的顧客做銷售分析
針對每一城市的顧客做銷售分析
針對年度、季、月做銷售分析
5_設計資料倉儲綱要
歸納需求分析

歸納出有五個索引基準點：
1.
2.
3.
4.
5.
13
顧客：
 除了可做單一顧客分析外
 可以國別、地區、城市來分析
 具有階層的關係
員工：單一員工做分析
產品：
 除了可做單一產品分析外
 可以產品分類來分析
供應商：單一供應商
時間：為一年、月、日的階層關係
5_設計資料倉儲綱要
產生維度資料

事實上，維度就是一種索引方式，所以歸納出有五種
維度資料：
1.
2.
3.
4.
5.
14
顧客維度
員工維度
產品維度
供應商維度
時間維度
5_設計資料倉儲綱要
Northwind資料庫
－資料篩選 & 分析維度（1）

篩選原來的13個檔案及欄位後， 4. Customers：顧客檔（ 6個
欄位，共91筆記錄）
留下需要的8個檔案，分別為：
1.
Categories：產品類型檔（ 2
 CustomerID：顧客識別號碼
個欄位，共 8 筆記錄）
主鍵
 CustomerName：顧客公司
 CategoryID：產品類型
名稱
的識別號碼主鍵
 ContactName：聯絡人姓名
 CategoryName：產品
 City：城市名稱
類型的名稱
 Region：地區名稱
屬於產品維度
 Country：國家名稱
屬於顧客維度
其中Country、Region、City
具有階層式的關係
15
5_設計資料倉儲綱要
Northwind資料庫
－資料篩選 & 分析維度（2）
Employees ：員工檔（ 8個欄位， 7. Order Details：訂單詳細檔（ 5個欄
共9筆記錄）
位，共2155筆記錄）

EmployeeID：員工識別號碼

OrderID：訂單號碼複合主鍵
主鍵

ProductID：產品識別碼複合

LastName：員工姓氏
主鍵

FirstName：員工名字

UnitPrice：產品單價

Title：員工職稱

Quantity：訂購數量

City：城市名稱

Discount：折扣

Region：地區名稱
其中OrderID、UnitPrice、

Country：國家名稱
Quantity、Discount不會隨時間

ReportsTo：員工經理的員工
而改變；且不會以訂單號碼、訂
號碼
購數量、折扣為基準來分析資料
屬於員工維度
，所以是事實資料
排除Country、Region、City具有階
其中ProductID屬於產品維度
層式的關係，因為不會對員工來自
5_設計資料倉儲綱要
何處感興趣
5.
16
Northwind資料庫
－資料篩選 & 分析維度（3）
8.
Orders：訂單檔（ 5個欄位，
共830筆記錄）





17
9.
Products：產品檔（ 6個欄位，共
77筆記錄）
OrderID：訂單識別號碼複
 ProductID：產品識別碼主鍵
合主鍵
 ProductName：產品名稱
CustomerID：顧客識別碼
 SupplierID：供應商識別碼
複合主鍵
 CategoryID：產品分類識別碼
EmplyoeeID：承辦員工的員
 QuantityPerUnit：每單位數量
工號碼
 UnitPrice：產品單價
OrderDate：訂購日期
ShipVia：負責貨運公司的識 其中UnitPrice、QuantityPerUnit
不會隨時間而改變，且不會以產品
別碼
單價、每單位數量為基準來分析資
其中OrderID不會隨時間而改變
料，所以是事實資料
，且不會以訂單號碼為基準來
分析資料，所以它是事實資料 其中ProductID、ProductName、
CategoryID屬於產品維度
其中CustomerID屬於顧客維度
其中EmployeeID屬於員工維度 其中SupplierID屬於供應商維度
其中Orderdate屬於時間維度
5_設計資料倉儲綱要
Northwind資料庫
－資料篩選 & 分析維度（4）
11.
Shippers ：貨運公司資料 12. Suppliers：供應商資料檔
檔（ 2個欄位，共3筆記錄）
（ 4個欄位，共29筆記錄）


ShipperD：貨運公司的識
別號碼主鍵
CompanyName：貨運公
司的名稱



SupplierID：供應商的識別號
碼主鍵
CompanyName：公司名稱
City：城市名稱
Country：國家名稱
因為Orders資料表不分析

ShipVia欄位，所以排除整 排除City與Country，因為需求
個Shippers資料表
分析沒有要求供應商要以國別
及城市來分析資料
其中SupplierID、
CompanyName屬於供應商維
度
18
5_設計資料倉儲綱要
設計事實資料表－Salses

事實資料：


維度資料：




單價存在於 Product 資料表
總銷售金額必須透過現存的欄位計算而得，稱為衍生欄位
事實資料表尚需包含索引所使用的欄位

19
折扣：分析哪一折扣範圍的產品銷售狀況最佳
單價：分析哪一單價範圍的產品銷售狀況最佳
關心：一個產品在一個訂單中的總銷售金額及該產品的單價


OrderID 與 Quantity 是因公司銷售行為而產生出來的，所以一定
是事實資料
員工號碼、產品號碼、供應商號碼、顧客號碼、以及訂購日期
5_設計資料倉儲綱要
設計事實資料表－Salses －欄位

事實資料表：Sales


20
欄位：
 EmployeeID：員工號碼主索引鍵欄位 來自Employees資料表
 ProductID：產品號碼主索引鍵欄位
來自Products資料表
 SupplierID：供應商號碼主索引鍵欄位來自Suppliers資料表
 CustomerID：顧客號碼主索引鍵欄位 來自Customers資料表
 OrderDate：訂購日期主索引鍵欄位 來自Orders資料表
 UnitPrice：產品單價事實量值欄位
來自Order Details資料表
 Total：單項總價Total＝Quantity ＊ UnitPrice ＊（1.0－Discount）
事實量值欄位
 Quantity：訂購數量事實量值欄位
來自Order Details資料表
 Discount：折扣
事實量值欄位 來自Order Details資料表
來源資料表：
 Orders、Order Details、Employees、Products、Suppliers、
Customers
5_設計資料倉儲綱要
設計事實資料表－Salses － SQL敘述

21
SQL敘述：
select e.EmployeeID, p.ProductID, s.SupplierID,
c.CustomerID, o.OrderDate, od.UnitPrice,
od.Quantity * od.UnitPrice * (1.0- od.Discount) total,
od.Quantity, od.Discount
From Orders o, [Order Details] od, Employees e,
Products p, Suppliers s, Customers c
where o.orderID=od.OrderID
and o.EmployeeID=e.EmployeeID
and o.CustomerID=c.CustomerID
and od.ProductID=p.ProductID
and p.SupplierID=s.SupplierID
5_設計資料倉儲綱要
設計員工維度資料表－Employee

員工維度資料表：Employee




22
欄位：
 EmployeeID：員工號碼主索引鍵欄位
 Name：員工姓名  Name＝FirstName ＋ ’ ‘ ＋LastName
 Title：員工職稱
 ReportsTo：員工經理的員工號碼
來源資料表：
 Employees
資料綱要：星狀綱要
SQL敘述：
select EmployeeID, FirstName+’ ‘+LastName Name, Title, ReportsTo
from Employees
5_設計資料倉儲綱要
設計產品維度資料表
產品維度資料表－Product

產品維度資料表： Product




23
欄位：
 ProductID：產品號碼主索引鍵欄位
 CategoryID：產品分類號碼
 ProductName：產品名稱
 QuantityPerUnit：每單位數量
來源資料表：
 Products
資料綱要：雪花綱要
SQL敘述：
select ProductID, CategoryID, ProductName, QuantityPerUnit
from Products
5_設計資料倉儲綱要
設計產品維度資料表
產品分類維度資料表－Category

產品分類維度資料表： Category




24
欄位：
 CategoryID：產品分類號碼主索引鍵欄位
 CategoryName：產品分類名稱
來源資料表：
 Categories
資料綱要：雪花綱要
SQL敘述：
select CategoryID, CategoryName
from Categories
5_設計資料倉儲綱要
設計供應商維度資料表－Supplier

供應商維度資料表： Supplier

欄位：
 SupplierID：供應商號碼主索引鍵欄位
 SupplierName：供應商名稱 SupplierName ＝
CompanyName



25
來源資料表：
 Suppliers
資料綱要：星狀綱要
SQL敘述：
select SupplierID, CompanyName SupplierName
from Suppliers
5_設計資料倉儲綱要
設計顧客維度資料表－Customer

顧客維度資料表： Customer





26
欄位：
 CustomerID：顧客號碼主索引鍵欄位
 CustomerName：公司名稱 CustomerName ＝ CompanyName
 ContactName：聯絡人
 City ：城市名稱
 Region：地區名稱
 Country：國家名稱
來源資料表：
 Customers
資料綱要：星狀綱要
SQL敘述：
select CustomerID, CompanyName CustomerName, ContactName, City,
Region, Country
from Customers
階層欄位：
 Country
→ Region → City
5_設計資料倉儲綱要
設計時間維度資料表－Time

時間維度資料表： Time



27
欄位：
 OrderDate：訂購日期主索引鍵欄位
來源資料表：
 Orders
SQL敘述：
select DISTINCT OrderDate
from Orders
5_設計資料倉儲綱要
星狀雪花綱要


28
一個資料倉儲最適合的綱要類型：
 將反正規化的星狀綱要與正規化的雪花式綱要合併一起使用，
而成為星狀雪花綱要
分析上述五個維度資料表
 四個維度採星狀綱要：
 Employee 員工維度資料表
 Time 時間維度資料表
 Supplier 供應商維度資料表
 Customer 顧客維度資料表
 產品維度採雪花式綱要，包含了兩個資料表
 Product 與 category
5_設計資料倉儲綱要
Northwind_DW資料倉儲的資料表間關係
－主索引鍵及外部索引鍵 (外來鍵)對應關係 P 5-38
主索引鍵（主鍵）
29
外來鍵
資料表
欄位
資料表
欄位
Time
OrderDate
Sales
OrderDate
Customer
CustomerID
Sales
CustomerID
Employee
EmployeeID
Sales
EmployeeID
Supplier
SupplierID
Sales
SupplierID
Product
ProductID
Sales
ProductID
Category
CategoryID
Product
CategoryID
5_設計資料倉儲綱要
Northwind_DW資料倉儲－
事實資料表/維度資料表的關連性(關聯圖) P 5-37
事實資料表
同一個維度
雪花結構
30
5_設計資料倉儲綱要
多重維度綱要 － 常見的查詢
31

分析資料時不以單一維度為基準，而以多個維度為依據

例如：Northwind_DW資料倉儲，常見的查詢如下：
 查詢SUPPLIER1供應商於1997年提供了多少金額的產品
 查詢SUPPLIER1供應商於1997年提供了多少金額的
PRODUCT1產品
 查詢EMPLOYEE1員工於1997年銷售了多少金額的
PRODUCT1產品
 查詢EMPLOYEE1員工於1997年銷售了多少金額的
SUPPLIER1供應商的產品
 查詢EMPLOYEE1員工於1997年銷售了多少金額的產品給
CUSTOMER1顧客
5_設計資料倉儲綱要
多重維度綱要 － CUBE

CUBE（多重維度綱要）：

32
每個查詢都用到多重的維度，應將多個維度集合在一起成為
一個單位，且一併加以考量而成為一個CUBE（多重維度綱
要）
5_設計資料倉儲綱要