目錄
<a>一、作業一：星型模型及緩慢變化維... 1</a>
<a>1.1 問題一：基本星型模型... 1</a>
<a>1.2 問題二：增加緩慢變化維的星型模型... 3</a>
<a>二、作業二：導出表和多值次元問題... 4</a>
<a>2.1 問題一：導出表... 4</a>
<a>2.2 問題二：多值次元問題... 5</a>
<a>三、作業三：次元表的抽取... 7</a>
<a>3.1 一些準備工作... 8</a>
<a>3.2 抽取title_dim表... 9</a>
<a>3.3 抽取dept_dim表... 11</a>
<a>3.4 抽取emp_dim表... 12</a>
<a>3.5 抽取day_dim表... 13</a>
<a>四、作業四：事實表的抽取... 14</a>
<a>五、作業五：資料分析... 17</a>
<a>5.1建構多元資料集... 17</a>
<a>5.2資料可視化... 19</a>
<a>5.2.1部門與工資之間的關系... 20</a>
<a>5.2.2 職稱與工資關系... 22</a>
<a>5.2.3 性别與工資關系... 24</a>
<a>5.2.4 職稱、性别與工資關系... 25</a>
對于問題一中建立星型模型所滿足的要求：（1）公司每個員工每月的薪資分别是多少？（2）哪些部門每個月開出的薪資比較高？（3）哪種職稱的每月得到的薪資比較高？我們對需求語義進行分析可知：事實表中的度量有：工資，時間；次元有：員工資訊、部門資訊、職稱資訊。
通過以上分析，我們通過資料庫設計工具進行制圖，在星形模型中，無論是事實表還是次元表都需要一個SK，這個SK通常為int類型；并将原本表中的主鍵變為NK；此外，事實表與次元表通過外鍵關聯。我們設計的星形模型如圖1.1所示。
圖1.1 問題一星形模型
其中salaries_fact是事實表，title_dim、emp_dim、dept_dim、day_dim是次元表，表title_dim的字段定義如表1.1所示，表emp_dim的字段定義如表1.2所示，表dept_dim的字段定義如表1.3所示，表day_dim的字段定義如表1.4所示，表salaries_fact的字段定義如表1.4所示。
表1.1
title_dim表字段定義
名
類型
長度
是否為主鍵
title_key
int
11
是
title
varchar
50
否
表1.2
emp_dim表字段定義
emp_key
birth_day
date
first_name
20
last_name
emp_no
gender
10
hire_date
表1.3
dept_dim表字段定義
dept_key
dept_no
4
dept_name
40
表1.4
day_dim表字段定義
day_key
from_day
to_day
表1.5
salaries_fact表字段定義
salary_key
salary
fitle_key
對于問題二中所增加的需求：（1）我們需要根據以前部門的名稱統計名稱改變以前的薪水情況；（2）同時，需要根據現在部門的名稱統計整個部門曆史的薪水情況。我們可以在問題一所建立的星形模型上進行修改得到，将department_dimension表中的dept_name做成一個混合類型的緩慢變化維類型即可。
通過以上分析，我們将dept_name屬性變為兩個屬性dept_name_current和dept_name-history。其中，dept_name_current屬性存儲目前部門名稱，dept_name-history存儲曆史部門名稱。如圖1.2所示。
圖1.2
問題二星形模型
相對于圖1.1，圖1.2中僅dept_dim表進行了修改，修改後的dept_dim表字段定義如表1.6所示。
表1.6 增加緩慢變化維的dept_dim表字段定義
dept_name_current
dept_name_history
對于問題一中提出的要求：如果想要了解每位員工的年薪情況，但是又覺得在OLAP中查詢的速度比較慢，應該怎樣解決這一問題？我們可以使用導出表的形式來提升查詢速度，其中聚集表的粒度為年薪。在第一次作業的基礎上我們設計事實表：salaries_fact表；次元表：title_dim、emp_dim、dept_dim、year_dim。通過以上分析我們使用資料庫設計工具進行設計，如圖2.1所示。
圖2.1
問題一導出表
相對于圖1.1，圖2.1中新增了表year_dim，修改了表salaries_fact。其中表year_dim的字段定義如表2.1所示，表salaries_fact的字段定義如表2.2所示。
表2.1
year_dim表字段定義
year_key
year
表2.2
對于問題二中提出的要求：如果公司管理層需要了解每位員工在公司的各部門中的變動情況，即員工什麼時候來到公司，在哪些崗位工作了多長時間，現在在什麼崗位工作。應該建立什麼樣的模型來滿足要求？關鍵點在與解決一個多值次元問題。這裡，我們使用橋接表+主表的方式解決。
這裡我們設計事實表emp_fact，在查詢時該表的資訊通過橋接表msgGroup_dim提供。msgGroup_dim表與msgGroupMenberShip_dim表相連接配接。msgGroupMenberShip_dim表擁有msg_title_dim、msg_department_dim、msg_date_dim表的外鍵。如圖2.2所示。
圖2.2 多值次元問題解決
其中employees_fact是事實表，其表結構如表2.3所示，salary_dim，date_dim， msgGroup_dim，msgGroupMemberShip_dim，msg_title_dim，msg_department_dim，msg_date_dim是次元表。salary_dim表字段結構如圖2.4所示，date_dim表字段結構如圖2.5所示，msgGroup_dim表字段結構如圖2.6所示，msgGroupMemberShip_dim表字段結構如圖2.7所示，msg_title_dim表字段結構如圖2.8所示，msg_department_dim表字段結構如圖2.9所示，msg_date_dim表字段結構如圖2.10所示。
表2.3
employees_fact字段結構表
birth_date
14
enum
msgGroup_key
date_key
表2.4
salary_dim字段結構表
表2.5
date_dim字段結構表
month
day
week
表2.6
msgGroup_dim字段結構表
表2.7
msgGroupMemberShip_dim字段結構表
msgGroupMemberShip _key
msg_title_key
msg_dept_key
msg_date_key
表2.8
msg_title_dim字段結構表
表2.9
msg_department_dim字段結構表
表2.10
msg_date_dim字段結構表
end_day
針對作業任務中提出的需求: 将樣例資料庫中的資料經過ETL過程裝載到資料倉庫中，主要是完成次元表格的裝載，我們可以使用SQL Server的Integration Service項目進行資料的抽取與轉換。根據前兩次作業中對于資料倉庫的設計，我們這裡需要抽取的次元表有 day_dim、dept_dim、employees_dim、titles_dim四個。這裡，我們在資料源部分使用SQL語句進行抽取，在資料目标部分使用表或視圖的映射。下面我們依次對他們進行抽取。
首先，我們将以上模型使用SQL語句在SqlServer資料庫中建表，使用的SQL語句如下：
CREATE
TABLE [day_dim] (
[day_key]
int NOT NULL IDENTITY(1,1),[from_day] date NULL,[to_day] date NULL,
PRIMARY
KEY ([day_key]) )
GO
TABLE [emp_dim] (
[emp_key]
int NOT NULL IDENTITY(1,1),
[birth_date]
date NULL,[first_name] varchar(20) NULL,[last_name] varchar(20) NULL,
[emp_no]
int NULL,[gender] varchar(10) NULL,[hire_date] date NULL,
KEY ([emp_key]) )
TABLE [title_dim] (
[title_key]
int NOT NULL IDENTITY(1,1),[title] varchar(50) NULL,
KEY ([title_key]) )
TABLE [dept_dim] (
[dept_key]
[dept_no]
char(4) NULL,[dept_name] varchar(40) NULL,PRIMARY KEY ([dept_key]) )
TABLE [salaries_fact] (
[salary_key]
[salary]
decimal(12) NULL,[emp_key] int NULL,[dept_key] int NULL,
int NULL,[day_key] int NULL,PRIMARY KEY ([salary_key]) )
ALTER
TABLE [salaries_fact] ADD CONSTRAINT [day_s] FOREIGN KEY ([day_key]) REFERENCES
[day_dim] ([day_key])
TABLE [salaries_fact] ADD CONSTRAINT [emp_s] FOREIGN KEY ([emp_key]) REFERENCES
[emp_dim] ([emp_key])
TABLE [salaries_fact] ADD CONSTRAINT [title_s] FOREIGN KEY ([title_key])
REFERENCES [title_dim] ([title_key])
TABLE [salaries_fact] ADD CONSTRAINT [demp] FOREIGN KEY ([dept_key]) REFERENCES
[dept_dim] ([dept_key])
這裡我們不詳細解釋建立Integration Service項目的過程，僅給出幾點需要注意的問題：
（1）樣例資料庫為Mysql資料庫，SQL Server不支援将其作為資料源，我們可以使用資料庫可視化工具Navicat将樣例資料庫中資料傳輸到SQL Server的employees資料庫中，并以SQL Server的employees資料庫作為源資料庫。
（2）我們需要将前兩次作業設計的模式生成SQL Server資料庫作為資料倉庫，這裡我們的資料倉庫名字為“21751106鄭明月”。
titles_dim表中包含titles_key、title兩個字段，我們可以直接從示例資料庫employees的titles表中抽取。這裡要注意的是再抽取過程中需要将将title字段去重，我們設定titles_key字段為主鍵自增。
<b>Step1</b><b>：</b>建立一個資料流任務，如圖3.1所示
圖3.1 建立資料流任務
<b>Step2</b><b>：</b>設計該資料流中的OLEDB源，資料轉換，OLEDB目标。将兩張表的字段進行對應。如圖3.2所示。
圖3.2 設計資料流
抽取資料源使用的SQL語句為：
SELECT
distinct title
FROM
employees.titles
這裡需要注意的是，如果直接将OLEDB源轉為OLEDB目标，可會會有一個因為轉碼而造成的錯誤。如圖3.3所示。
圖3.3 一個錯誤
針對這個錯誤，我們設計一個中間轉碼的流程，如圖3.4所示。
圖3.4 錯誤的解決
<b>Step3</b><b>：</b>執行包，經過執行包後，我們看到綠色顯示即為成功。如圖3.5所示。
<b></b>
圖3.5
執行成功
抽取成功後資料倉庫中titles_dim表中有7行資料，如下圖3.6所示。
圖3.6
title_dim 表
dept_dim表中包含dept_key、dept_no、dept_name三個字段，我們可以直接從示例資料庫employees的department表中抽取。我們設定dept_key字段為主鍵自增。
<b>Step1:</b> 建立一個資料流任務，圖3.7所示。
圖3.7 建立一個資料流任務
<b>Step2</b><b>：</b>設計該資料流中的OLEDB源，資料轉換，OLEDB目标。将兩張表的字段進行對應。如圖3.8所示。
圖3.8 設計資料流
SELECT
dept_no, dept_name
employees.departments
<b>Step3</b><b>：</b>執行包，經過執行包後，我們看到綠色顯示即為成功。如圖3.9所示。
圖3.9 執行成功
抽取成功後資料倉庫中dept_dim表中有9行資料，如下圖3.10
圖3.10
dept_dim表
employees_dim表中包含employees_key、birth_date、first_name、last_name、emp_no、gender、hire_date七個字段，我們可以直接從示例資料庫employees的employees表中抽取。我們設定employees_key字段為主鍵自增。
<b>Step1: </b>建立一個資料流任務，如圖3.11。
圖3.11
建立一個資料流任務
<b>Step2</b><b>：</b>設計該資料流中的OLEDB源，資料轉換，OLEDB目标。将兩張表的字段進行對應。如圖3.12。
圖3.12
設計資料流
SELECT DISTINCT
emp_no, birth_date, first_name, last_name, gender, hire_date
employees.employees
<b>Step3</b><b>：</b>執行包，經過執行包後，我們看到綠色顯示即為成功。如圖3.13。
圖3.13
抽取成功後資料倉庫中dept_dim表中有300024行資料，如圖3.14：
圖3.14
day_dim表中包含day_key、from_day、end_day三個字段，我們可以直接從示例資料庫employees的employees表中抽取。我們設定employees_key字段為主鍵自增。
<b>Step1: </b>建立一個資料流任務，如圖3.15。
圖3.15 建立一個資料流任務
<b>Step2</b><b>：</b>設計該資料流中的OLEDB源，OLEDB目标。将兩張表的字段進行對應。如圖3.16。
圖3.16 設計資料流
SELECT DISTINCT to_date, from_date
employees.salaries
<b>Step3</b><b>：</b>執行包，經過執行包後，我們看到綠色顯示即為成功。如圖3.17。
圖3.17 執行成功
抽取成功後資料倉庫中day_dim表中有331603行資料，如下圖3.18：
圖3.18 day_dim表
事實表的抽取與次元表的抽取有相同的步驟，但是事實表抽取相對比較麻煩，這裡我将事實表的抽取分為三個步驟。步驟一中，建立了中間表：将dept_emp與dept_manager表合并抽取到表produce_one、将salaries表與title表左連接配接抽取到表produce_two;步驟二中，将表produce_two與表produce_one左連接配接，抽取到包含所有資訊的合并表produce_three；将表produce_three資料倉庫中其他表左連接配接，抽取得到最終的事實表salaries_fact。下面詳細講解這一過程。
<b>Step1</b><b>：</b>建立中間表
使用如下SQL語句抽取表produce_one：
SELECT
dept_emp.dept_no,dept_emp.emp_no,
dept_emp.from_date,dept_emp.to_date,
departments.dept_name
FROM
departments,dept_emp
WHERE
dept_emp.dept_no = departments.dept_no
使用如下SQL語句抽取表produce_two：
salaries.emp_no,salaries.salary,
salaries.from_date AS salary_from_date,
salaries.to_date AS salary_to_date,
titles.from_date AS title_from_date,
titles.to_date AS title_to_date
salaries
LEFT JOIN titles
ON salaries.emp_no = titles.emp_no
salaries.from_date >= titles.from_date
AND
salaries.to_date <= titles.to_date
<b>Step2</b><b>：</b>抽取合并表
使用如下SQL語句抽取表produce_three：
produce_two.emp_no,produce_two.salary,
produce_two.salary_from_date,produce_two.salary_to_date,
produce_two.title,produce_one.dept_no
FROM
employees. produce_two
LEFT OUTER JOIN employees.produce_one ON produce_two.emp_no =
produce_one.emp_no
WHERE
produce_two.salary_from_date
>= produce_one.from_date
AND produce_two.salary_to_date <= produce_one.to_date
抽取成功後，該表中資料如圖4.1所示。
圖4.1 表produce_three 資料
<b>Step3</b><b>：</b>抽取事實表
得到produce_three表後，我們可以使用emp_no字段得到emp_dim表中的代理鍵emp_key;使用salary_from_date及salary_to_date字段得到day_dim表中的代理鍵day_key;使用title字段得到titles_dim表中的代理鍵title_key字段；使用dept_no字段得到dept_dim表中的dept_key字段。
設計事實表抽取資料流程如圖4.2所示。
圖4.2 事實表抽取資料流程
其中，抽取資料源部分使用的SQL語句如下：
produce_one.salary,
emp_dim.emp_key,dept_dim.dept_key,
title_dim.title_key,day_dim.day_key
FROM
produce_one
LEFT JOIN title_dim
ON produce_one.title = title_dim.title
LEFT JOIN day_dim ON
produce_one.salary_from_date = day_dim.from_day
AND
produce_one.salary_to_date = day_dim.to_day
LEFT JOIN dept_dim
ON produce_one.dept_no = dept_dim.dept_no
LEFT JOIN emp_dim ON
produce_one.emp_no = emp_dim.emp_no
以上資料流圖執行成功後得到salaries_fact表，抽取成功結果圖如圖4.3所示。
圖4.3 抽取成功
salaries_fact表中資料如圖4.4所示。
表4.4 salaries_fact表中資料
本次作業要求使用SqlServer對資料倉庫進行可視化，需要實作的需求有：（1）分析每個員工每個月的薪水情況；（2）分析每個部門的薪水情況；（3）分析不同職稱的薪水情況。
想要實作對上文資料倉庫中資料的分析，我們需要借助SqlServer提供的兩個項目AnalysisService項目以及報表項目。首先我們在AnalysisService項目中建構多元資料集，然後使用報表項目對其進行可視化。下面我們分兩步完成需求。
建構多元資料集這一部分我們使用一個AnalysisService項目完成。這裡省略建立項目以及建立資料源、資料源視圖的步驟，着重闡述多元資料集的構造。如圖5.1所示為建立資料源後的成果圖。
圖5.1 建立資料源
<b>Step1</b><b>：</b>建立多元資料集
我們以salaries_fact表為度量建立一個多元資料集，建立過程中主要流程如圖5.2所示。
圖5.2 建立一個多元資料集
<b>Step2</b>：完善次元
建立一個多元資料集之後，我們對度量的相關次元進行完善，為每個次元增加
字段，完善之後次元字段如圖5.3所示。
圖5.3 完善次元
<b>Step3</b><b>：</b>部署
我們通過在項目上右鍵，然後點選部署的方式部署多元資料集，部署結果如圖5.4所示。
圖5.4 部署完成
此時我們可以在SqlServer的浏覽器中進行浏覽，如圖5.5所示。
圖5.5 浏覽器中浏覽
我們使用SqlServer提供的報表項目完成資料可視化。這裡我将不會較長的描述具體建立步驟，僅提出幾點注意事項：
（1）建立共享資料源屬性時，選擇Analysis Service一項，連接配接到我們上文部署的多元資料集項目上，如圖5.6所示；
圖5.6
建立共享資料源
（2）建立報表的查詢設計器時務必要勾選參數這一項，否則該報表不能使用多個數值作為條件，如圖5.7所示。
圖5.7
勾選參數
<b>Step1</b><b>：</b> 建立資料集
這裡，我們選擇Dept_Name、Salary、Salaries_Fact_計數、average_salary四個字段進行計算，如圖5.8所示。其中average_salary為一個計算字段，其計算方式如公式（5.1）所示。
average_salary= Salary/ Salaries_Fact_計數
公式（5.1）
圖5.8 建立資料集
<b>Step2</b><b>：</b> 設計表格、繪制圖表
我們設計表格、設計圖表如下圖5.9所示。
圖5.9 設計表格圖表
<b>Step3</b><b>：</b> 圖表的分析
根據Step2中的設計，可以得到表5.1及圖5.10、圖5.11。從圖表中我們可以看出，部門：Development、Production、Sales的總工資較高；部門：Finance、Marking、Sales的人/月平均工資較高。
表5.1 部門與工資
部門
總工資
平均工資
Customer Service
9802982141
57461.45768
Development
42194302803
59225.80966
Finance
10120686629
70676.17305
Human Resources
8070960565
54971.80606
Marketing
11102046223
71764.54078
Production
35307268848
59541.78931
Quality Management
8862262053
57119.86989
Research
9580107115
59868.18595
Sales
35029560045
80564.39493
圖5.10 部門與總工資關系
圖5.11部門與平均工資關系
建立資料集如圖5.12所示。
圖5.12 建立資料集
設計表格、繪制圖表如圖5.13所示
圖5.13 設計表格、繪制圖表
根據Step2中的設計，可以得到表5.2及圖5.13、圖5.14。從圖表中我們可以看出，職稱：Assistant、Manger的總工資較低；職稱：Manager、SeniorStaff的人/月平均工資較高。
表5.2 職稱工資表
職稱
Assistant Engineer
4823711052
54121.1634054394
Engineer
36026662812
54877.4515676481
Manager
19696302
66994.2244897959
Senior Engineer
38254259493
64761.0026307815
Senior Staff
41803627986
75040.8883244357
Staff
40738678933
64118.2416064391
Technique Leader
8403539844
59326.9219756015
圖5.13 職稱與總工資
圖5.14 職稱與平均工資
建立資料集如圖5.15所示。
圖5.15 建立資料集
設計表格、繪制圖表如圖5.16所示。
圖5.16 設計表格、繪制圖表
根據Step2中的設計，可以得到表5.3及圖5.17、圖5.18。從圖表中我們可以看出，職稱：Assistant、Manger的總工資較低；職稱：Manager、SeniorStaff的人/月平均工資較高。
表5.3 性别與工資
性别
領取工資人數
F
67999416575
63640.8459578825
1068487
M
102070759847
63705.7933000504
1602221
圖5.17 性别與總工資
圖5.18 性别與平均工資
建立資料集如圖5.19所示。
圖5.19 建立資料集
設計表格、繪制圖表如圖5.20所示。
圖5.20 設計表格、繪制圖表
根據Step2中的設計，可以得到表5.4、表5.5及圖5.21、圖5.22。
表5.4 職稱性别與總工資
Title
1882429902
2941281150
14409240726
21617422086
8866422
10829880
15369973027
22884286466
16705584008
25098043978
16232753513
24505925420
3390568977
5012970867
表5.5職稱性别與平均工資
53940.9107112155
54237.1593214088
54817.7934238008
54917.2892875652
61572.375
72199.2
64700.1478676697
64801.9393501764
75021.5963390921
75053.7348213672
64123.3167541645
64114.880264979
59270.5004282842
59365.1441445709
圖5.21
性别職稱與總工資
圖5.22
性别職稱與平均工資