- 建立資料來源─檢視表
─SQL Server─
- vDimProduct
- AdventureWorks2012資料庫下,新增檢視,選取資料表,勾選欄位:
- Product:ProductID、Name、Color、StandardCost、ListPrice
- ProductCategory:ProductCategoryID、Name
- ProductSubCategory:ProductSubCategoryID、Name
- 勾選完欄位後,發現總共有三個Name欄位,會有Expr1、Expr2,所以將這三個Name欄位的別名分別依所屬資料表改成:ProductName、CatName、SubCatName。
- 執行後儲存成「vDimProduct」。
- vDimDate_1
- AdventureWorks2012資料庫下,新增檢視,選取資料表,勾選欄位:
- SalesOrderHeader:OrderDate(別名:PDate)
- 把語法全選,複製兩次,將複製後的其他兩個OrderDate改成:DueDate、ShipDate,如下面紅字,然後做union。
SELECT OrderDate AS PDate
FROM Sales.SalesOrderHeader union SELECT DueDate AS PDate FROM Sales.SalesOrderHeader union SELECT ShipDate AS PDate FROM Sales.SalesOrderHeader |
- 將上面的語法全選,做整個資料的DISTINCT
(輸入紅字語法,_1、_2執行後會自動帶入)
SELECT DISTINCT TOP (100) PERCENT PDate
FROM (SELECT OrderDate AS PDate FROM Sales.SalesOrderHeader UNION SELECT DueDate AS PDate FROM Sales.SalesOrderHeader AS SalesOrderHeader_2 UNION SELECT ShipDate AS PDate FROM Sales.SalesOrderHeader AS SalesOrderHeader_1) AS a ORDER BY PDate |
- 儲存成「vDimDate_1」,_1是暫~時~的表,後面還會繼續做。
- vDimDate_2
- AdventureWorks2012資料庫下,新增檢視,選取資料表,勾選欄位:
- vDimDate_1:PDate
- 用DATEPART取出年、季、月、週,語法如下:
SELECT PDate, DATEPART(year, PDate) AS Year, DATEPART(Q, PDate) AS Q, DATEPART(month, PDate) AS Month, DATEPART(week, PDate) AS Week, DATEPART(weekday, PDate) AS WeekDay, DATEPART(dayofyear, PDate) AS DY
FROM dbo.vDimDate_1 |
執行後,儲存成「vDimDate_2」,這還是暫~時~的,等等還會有一個修正的。
- vDimDate
- AdventureWorks2012資料庫下,新增檢視,選取資料表,勾選欄位:
- vDimDate_2:Year, Q, Month, Week, WeekDay, PDate
- 執行後,儲存為「vDimDate」。
- vFactOrder
- AdventureWorks2012資料庫下,新增檢視,選取資料表,勾選欄位:
- SalesOrderHeader:SalesOrderID、OrderDate、DueDate、ShipDate、TerritoryID
- SalesOrderDetail:SalesOrderDetailID、OrderQty、ProductID、UnitPrice、LineTotal
- 執行後儲存成「vFactOrder」。
- vDimTerritory
- AdventureWorks2012資料庫下,新增檢視,選取資料表,勾選欄位:
- SalesTerritory:TerritoryID、Name、CountryRegionCode、Group
- 執行後儲存成「vDimTerritory」。
- 建立專案、Cube
─SQL Server Data Tools For Visual Studio─
- 建立Analysis Services專案、維度
- 左上點選新增→專案→Analysis Services多維度和資料採礦專案
- 新增資料來源:
右邊「方案總管」→資料來源→右鍵新增資料來源→下一步→New…→伺服器名稱:.、資料庫名稱:AdventureWorks2012→OK→下一步→勾「繼承」→下一步→完成
- 新增資料來源檢視:
右邊「方案總管」→資料來源檢視→右鍵新增資料來源檢視→下一步→選剛剛建立的資料來源(AdventureWorks2012)→下一步→下一步→選取「vDimProduct、vDimDate、vDimTerritory、vFactOrder」資料表移到右邊「包含的物件」→下一步→完成
- 設定邏輯主索引鍵
針對[vDimDate.PDate],右鍵→設定邏輯主索引鍵,並建立資料表關聯:
- OrderDate→vDimDate.PDate
- DueDate→vDimDate.PDate
- ShipDate→vDimDate.PDate
- ProductID→vDimProduct.ProductID
- TerritoryID→vDimTerritory.TerritoryID
- 角色扮演維度
當一個事實資料表多次引用一個維度資料表時,稱為角色扮演維度。
- 新增維度:
右邊「方案總管」→維度→右鍵新增維度→下一步→勾「使用現有資料表」→下一步→
主資料表 | 索引建資料行 | 名稱資料行 | 命名為 |
vDimDate | PDate | PDate | DimDate |
vDimProduct | ProductID | ProductID | DimProduct |
vDimTerritory | TerritoryID | TerritoryID | DimTerritory |
→下一步→可用的屬性:全勾→下一步→完成。
- 建立階層:
- DimDate
- 「YQMD」:Year>Q>Month>PDate
- 「YWD」:Year>Week>PDate
- 「YMD」:Year>Month>PDate
- DimProduct
- 「Product階層」:CatName>SubCatName>ProductName
- DimTerritory
先把維度的欄位改名
- Country Region Code→Country
- Name→Territory
再建立階層
- 「Territory階層」:Group>Country>Territory
- 新增Cube
- 右邊「方案總管」→CUBE→右鍵CUBE→下一步→勾”使用現有資料表”→下一步→量值群組資料表:勾選「成績單」→下一步→下一步→下一步→下一步→完成。
左上「處理」。
- 處理完成後,切換到「瀏覽器」頁籤,輸出到EXCEL建立樞紐分析表
─EXCEL─
- 欄:Territory階層
- 列:Order Date > Order Date.YMD
- 值:v Fact Order > Order Qty
- 年月資料要顯示成某”年”、某”月”
─SQL Server Data Tools For Visual Studio─
- 到資料來源檢視,vDimDate資料表標題處按右鍵「新增具名計算」
- Year1
- 資料行名稱:Year1
- 運算式:convert(nvarchar, Year) + N’年’
- Month1
- 資料行名稱:Month1
- 運算式:convert(nvarchar, Month) + N’月’
- DD
- 資料行名稱:DD
- 運算式:convert(varchar, PDate, 111)
- 到DimDate維度再建立一個階層
- YMD1:Year1>Month1>PDate
- 年月的排序依然撿角,所以必須修改維度的屬性,點選左邊Month1,修改右下屬性:
- OrderBy:Name→Key
- KeyColumn:Month1→Month
- 重新將Cube處理一次。
- 處理完成後,切換到「瀏覽器」頁籤,輸出到EXCEL建立樞紐分析表
─EXCEL─
- 欄:Product階層
- 列:Order Date > Order Date.YMD1
- 值:v Fact Order > Order Qty