角色扮演維度

  • 建立資料來源─檢視表

─SQL Server─

  • vDimProduct
  1. AdventureWorks2012資料庫下,新增檢視,選取資料表,勾選欄位:
  • Product:ProductID、Name、Color、StandardCost、ListPrice
  • ProductCategory:ProductCategoryID、Name
  • ProductSubCategory:ProductSubCategoryID、Name
  1. 勾選完欄位後,發現總共有三個Name欄位,會有Expr1、Expr2,所以將這三個Name欄位的別名分別依所屬資料表改成:ProductName、CatName、SubCatName。
  2. 執行後儲存成「vDimProduct」。
  • vDimDate_1
  1. AdventureWorks2012資料庫下,新增檢視,選取資料表,勾選欄位:
  • SalesOrderHeader:OrderDate(別名:PDate)
  1. 把語法全選,複製兩次,將複製後的其他兩個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

  1. 將上面的語法全選,做整個資料的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

  1. 儲存成「vDimDate_1」,_1是暫~時~的表,後面還會繼續做。
  • vDimDate_2
  1. AdventureWorks2012資料庫下,新增檢視,選取資料表,勾選欄位:
  • vDimDate_1:PDate
  1. 用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
  1. AdventureWorks2012資料庫下,新增檢視,選取資料表,勾選欄位:
  • vDimDate_2:Year, Q, Month, Week, WeekDay, PDate
  1. 執行後,儲存為「vDimDate」。
  • vFactOrder
  1. AdventureWorks2012資料庫下,新增檢視,選取資料表,勾選欄位:
  • SalesOrderHeader:SalesOrderID、OrderDate、DueDate、ShipDate、TerritoryID
  • SalesOrderDetail:SalesOrderDetailID、OrderQty、ProductID、UnitPrice、LineTotal
  1. 執行後儲存成「vFactOrder」。
  • vDimTerritory
  1. AdventureWorks2012資料庫下,新增檢視,選取資料表,勾選欄位:
  • SalesTerritory:TerritoryID、Name、CountryRegionCode、Group
  1. 執行後儲存成「vDimTerritory」。
  • 建立專案、Cube

─SQL Server Data Tools For Visual Studio─

  • 建立Analysis Services專案、維度
  1. 左上點選新增→專案→Analysis Services多維度和資料採礦專案
  2. 新增資料來源:

右邊「方案總管」→資料來源→右鍵新增資料來源→下一步→New…→伺服器名稱:.、資料庫名稱:AdventureWorks2012→OK→下一步→勾「繼承」→下一步→完成

  1. 新增資料來源檢視:

右邊「方案總管」→資料來源檢視→右鍵新增資料來源檢視→下一步→選剛剛建立的資料來源(AdventureWorks2012)→下一步→下一步→選取「vDimProduct、vDimDate、vDimTerritory、vFactOrder」資料表移到右邊「包含的物件」→下一步→完成

  1. 設定邏輯主索引鍵

針對[vDimDate.PDate],右鍵→設定邏輯主索引鍵,並建立資料表關聯:

  • OrderDate→vDimDate.PDate
  • DueDate→vDimDate.PDate
  • ShipDate→vDimDate.PDate
  • ProductID→vDimProduct.ProductID
  • TerritoryID→vDimTerritory.TerritoryID
  • 角色扮演維度

當一個事實資料表多次引用一個維度資料表時,稱為角色扮演維度。

更多:文章1文章2

  1. 新增維度:

右邊「方案總管」→維度→右鍵新增維度→下一步→勾「使用現有資料表」→下一步→

主資料表 索引建資料行 名稱資料行 命名為
vDimDate PDate PDate DimDate
vDimProduct ProductID ProductID DimProduct
vDimTerritory TerritoryID TerritoryID DimTerritory

→下一步→可用的屬性:全勾→下一步→完成。

  1. 建立階層:
  • 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
  1. 右邊「方案總管」→CUBE→右鍵CUBE→下一步→勾”使用現有資料表”→下一步→量值群組資料表:勾選「成績單」→下一步→下一步→下一步→下一步→完成。

左上「處理」。

  1. 處理完成後,切換到「瀏覽器」頁籤,輸出到EXCEL建立樞紐分析表

─EXCEL─

  • 欄:Territory階層
  • 列:Order Date > Order Date.YMD
  • 值:v Fact Order > Order Qty
  • 年月資料要顯示成某”年”、某”月”

─SQL Server Data Tools For Visual Studio─

  1. 到資料來源檢視,vDimDate資料表標題處按右鍵「新增具名計算」
  • Year1
  • 資料行名稱:Year1
  • 運算式:convert(nvarchar, Year) + N’年’
  • Month1
  • 資料行名稱:Month1
  • 運算式:convert(nvarchar, Month) + N’月’
  • DD
  • 資料行名稱:DD
  • 運算式:convert(varchar, PDate, 111)
  1. 到DimDate維度再建立一個階層
  • YMD1:Year1>Month1>PDate
  1. 年月的排序依然撿角,所以必須修改維度的屬性,點選左邊Month1,修改右下屬性:
  • OrderBy:Name→Key
  • KeyColumn:Month1→Month
  1. 重新將Cube處理一次。
  2. 處理完成後,切換到「瀏覽器」頁籤,輸出到EXCEL建立樞紐分析表

─EXCEL─

  • 欄:Product階層
  • 列:Order Date > Order Date.YMD1
  • 值:v Fact Order > Order Qty

Leave a Reply