角色扮演維度

  • 建立資料來源─檢視表

─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

緩時變維度

  • 緩時變維度

維度是經由時間緩慢的變化。

Type0:Retain Original 假設學生資料表,有兩位在98學年度轉學。

  • 建立Analysis Services專案、維度、Cube

─SQL Server Data Tools For Visual Studio─

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

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

  1. 新增資料來源檢視:

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

  1. 設定邏輯主索引鍵、建立關聯

針對[科目表.科目編號]、[學生表.座號]、[年度段考表.段考代號],右鍵→設定邏輯主索引鍵,並建立資料表關聯(如圖)。

  1. 新增維度:

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

主資料表 索引建資料行 名稱資料行 命名為
年度段考表 段考代號 段考代號 段考維度
科目表 科目編號 科目編號 科目維度
學生表 座號 座號 學生維度

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

  1. 建立階層:

建立「段考階層」:年度>段考。

  1. 新增Cube

右邊「方案總管」→CUBE→右鍵CUBE→下一步→勾”使用現有資料表”→下一步→量值群組資料表:勾選「成績單」→下一步→下一步→下一步→下一步→完成。

左上「處理」。

  • 如果執行撿角,請回去SQL Server授予目前使用者(as)讀取A123456資料庫的權限(db_owner或db_datareader)。

(安全性→登入→選取目前使用者(as)→右鍵”屬性”→左邊”使用者對應”→右邊勾選資料庫和權限)

  • 變更「學生表」資料,新增「學年」欄位與資料

─SQL Server─

  1. 開啟SQL Server 2014 → 伺服器名稱:.、驗證:Windows驗證
  2. 在[A123456].[學生表.dbo]右鍵設計,新增一個欄位:
  • 資料行名稱:學年
  • 資料類型:nvarchar(50)

(必須要跟年度段考表的年度串起來,所以資料類型要相同。)

,左上儲存。

  1. 在[A123456].[學生表.dbo]右鍵編輯前200個資料列,點選左上角空白,全選資料表,複製到EXCEL貼上

─Excel─

  1. 將剛剛複製的「學生表」內容貼上,再到底下貼上一次(共兩次)。
  2. 輸入「年度」欄位的資料:
  • 第一次貼上的部分:97
  • 第二次貼上的部分:98
  1. 把98年最後兩個同學刪除後,整個資料複製回去SQL Server覆蓋。

─SQL Server─

  1. 在[A123456].[學生表.dbo]右鍵編輯前200個資料列,點選左上角空白,全選資料表,右鍵>刪除,右鍵>貼上。
  • 重新處理Cube(解決學生表主索引鍵重複的錯誤)
  • 如果直接去重新整理,把學生維度的年度拉進來後,處理Cube時會發生錯誤。因為座號是主索引鍵,而剛剛複製兩份學生表資料,造成主索引鍵重複。)

─SQL Server Data Tools For Visual Studio─

  1. 到資料來源檢視,學生表最上面按右鍵>新增具名計算
  • 資料行名稱:Key
  • 運算式:年度+cast(座號 AS varchar(10))

把「Key」設定成邏輯主索引鍵,學生表最上面按右鍵>瀏覽資料,確認資料有沒有串起來。

  1. 到「Dim_學生」,把剛剛新增的「Key」加進來,左邊屬性點選「Key」,右下角屬性把「Usage設定成Key」。(可是這樣處理Cube會出錯,所以重新建立學生表的維度。)
  2. 重新建立「Dim_學生」,並新增兩個階層:
  • 年度座號階層:年度>座號
  • 年度姓名階層:年度>姓名

,左上角「處理」。

  • 非對稱性維度
  • 建立Analysis Services專案、維度

─SQL Server Data Tools For Visual Studio─

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

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

  1. 新增資料來源檢視:

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

  1. 設定邏輯主索引鍵

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

  1. 新增維度:

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

主資料表 索引建資料行 名稱資料行 命名為
DIM REGION REGION_KEY REGION_KEY Dim_REGION

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

  1. 建立階層:

建立「REGION階層」:GROUP NAME>COUNTRY NAME>REGION NAME。

  1. 左上「處理」維度,到「瀏覽器」頁籤檢視資料,會發現:

各地區都有一層一層向下的階層資料,例如:大中華區>台灣>台北,但是有一筆資料是:大中華區>香港>香港,就很奇怪。

  1. 回到dim,到「維度結構」頁籤,點選階層裡面的「REGION NAME」,到右下角屬性設定
  • HideMemberIf:ParentName

(如果下面這層跟上一層的資料一樣,該層不顯示)(ex:香港)

  • 循環對應維度的處理
  • 加入資料表、建立維度
  1. 在資料來源檢視中,對空白處按右鍵加入/移除資料表:DIM_ EMPLOYEE。
  2. 將「EMPLOYEE _KEY」設定成邏輯主索引鍵。
  3. 將「MANAGER_KEY」拖曳對應到「EMPLOYEE _KEY」,成為循環對應
  4. 新增維度:

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

主資料表 索引建資料行 名稱資料行 命名為
DIM EMPLOYEE EMPLOYEE_KEY MANAGER_KEY Dim_REGION

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

  1. 回到Dim EMPLOYEE.dim切換到「維度結構」頁籤
  • 點擊左上角屬性:EMPLOYEE KEY,到右下角屬性去設定:找Keycolumn>Namecolumn下拉式清單選EMPLOYEE NAME
  • 點擊左上角屬性:MANAGER KEY,到右下角屬性去設定:找Keycolumn>NamingTemplate下拉式清單定義:總經理、副總、協理
  • 點擊左上角屬性:MANAGER KEY,到右下角屬性去設定:找MembersWithData:NonLeafDataHidden。

SSDT新增量值

由於資料庫備份檔的「科目表」沒有「類別」欄位,但是SSDT的專案有,而此次不須用到「類別」欄位,故直接在SSDT更新資料庫資料即可。

  • SQL Server
  1. 還原bak資料庫。
  • SQL Server Data Tools
  1. 開啟MultidimensionalProject2專案
  2. 切換到「學生維度.dim」視窗,左側的屬性視窗,把「類別」刪除。
  3. 切換到「dsv」視窗→上面工具列「資料來源檢視」→重新整理。
  4. 切換到「cube」視窗,左上「處理」CUBE,「瀏覽器」頁籤,用EXCEL的樞紐分析表檢視此資料。
  • 上禮拜課堂做的「前次分數」會撿角是因為樞紐分析表沒有拉正確。
  • 篩選:科目階層 (4)
  • 欄:段考代號 (2)
  • 列:姓名 (1)
  • 值:分數(3)>前次分數(5)

–SQL Server Data Tools

  • 編輯趨勢KPI:「成績KPI」
  1. 複製下列內容:
CASE

WHEN [Measures].[分數]-[Measures].[前次分數]>5 THEN 1

WHEN [Measures].[分數]-[Measures].[前次分數]<-5 THEN -1

ELSE 0

END

  1. 切換到cube視窗(A12345.cube)的「KPI」頁籤,選取「成績KPI」,將剛剛複製的內容貼到「趨勢KPI」欄位。
  2. 重新處理CUBE,到EXCEL→「資料」頁籤→全部重新整理。
  3. 拉一張新的樞紐分析表,「資料」頁籤→外部資料→現有連線→選擇剛剛的連線。樞紐分析表:
  • 篩選:段考代號>科目階層
  • 欄:(空)
  • 列:姓名
  • 值:成績KPI(內容(分數))>成績KPI(目標)>成績KPI(狀態)>成績KPI(預測趨勢)
  • 胡曰:

用SSDT>EXCEL>樞紐分析表,SQL資料庫只要新增資料,我只要從EXCEL選取段考代號即可檢視新資料。雖然一開始建置的時候比較辛苦,但是之後比較容易檢視及維護。

而如果這個表示用SQL語法硬拉出來,資料庫有新增資料的話,就必須每次更改語法,造成實作及維護不易。

  • 新增量值:科目目標

訂定每個科目的目標。

  1. 在SQL Server,A12345資料庫「科目表」資料表新增一個欄位:
資料行名稱 資料類型
科目目標 int
  1. 編輯「科目表」資料表,自行亂數輸入「科目目標」
  2. 回到SQL Server Data Tools,資料來源檢視視窗(A12345.dsv),上面工具列資料來源檢視→重新整理。
  3. Cube視窗(A12345.cube),「Cube結構」頁籤,左邊「量值」欄按右鍵「新增量值」::
  • 使用方式:總和
  • 來源資料表:科目表
  • 來源資料行:科目目標
  • 「顯示所有資料行」不打勾
  1. 將量值:成績單→成績單 計數,改為「考試人數」
  2. 重新處理CUBE,到EXCEL「資料」頁籤→連線→全部重新整理,即可看到剛剛建立的「科目目標」,放到樞紐分析表「值」欄位。
  • 篩選:段考代號>科目階層
  • 欄:(空)
  • 列:姓名
  • 值:成績KPI(內容(分數))>成績KPI(目標)>成績KPI(狀態)>成績KPI(預測趨勢)>科目目標
  • 新增量值:科目目標

訂定每一次不同考試,各科目的目標。

  1. 在SQL Server,在A12345資料庫新增資料表,,儲存為:段考科目,
資料行名稱 資料類型
段考代號 nvarchar(50)
科目編號 float
段考科目目標 int

並且設定「段考代號」及「科目編號」為主索引鍵。

  1. 編輯「段考科目」資料表,自行亂數輸入「段考科目目標」
  2. 回到SQL Server Data Tools,資料來源檢視視窗(A12345.dsv),左下「資料表」欄位右鍵「加入/移除資料表」→把「段考科目」資料表加到右邊欄位→確定,上面工具列資料來源檢視→重新整理。
  3. 建立資料表關聯:
  • [段考科目.dbo].[段考代號]→[年度段考表.dbo].[段考代號]
  • [段考科目.dbo].[科目編號]→[科目表.dbo].[科目編號]
  1. 新增維度:「段考科目」

勾選「使用現有的資料表」,下一步→主資料表:段考科目,索引鍵資料行:段考代號、科目編號,名稱資料行:段考代號,下一步→相關資料表:年度段考表、科目表,下一步→可用的屬性:段考代號、段考科目目標、科目編號,下一步→命名為:段考科目,完成。

  1. Cube視窗(A12345.cube),「Cube結構」頁籤,左邊「維度」欄按右鍵「加入Cube維度」:把「段考科目」維度加進來。
  2. Cube視窗(A12345.cube),「Cube結構」頁籤,左邊「量值」欄按右鍵「新增量值」:
  • 使用方式:總和
  • 來源資料表:段考科目
  • 來源資料行:段考科目目標
  • 「顯示所有資料行」不打勾
  1. 重新處理CUBE,到EXCEL「資料」頁籤→連線→全部重新整理,即可看到剛剛建立的「段考科目目標」,放到樞紐分析表「值」欄位。
  • 篩選:科目階層
  • 欄:年度段考階層
  • 列:姓名
  • 值:分數>段考科目目標
  • 新增量值:年度目標

訂定每一次不同考試,各科目的目標。

  1. 在SQL Server,在A12345資料庫新增資料表,,儲存為:年度目標,
資料行名稱 資料類型
年度 nvarchar(50)
目標 int

並且設定「年度」為主索引鍵。

  1. 編輯「年度目標」資料表,自行亂數輸入「目標」
  2. 回到SQL Server Data Tools,資料來源檢視視窗(A12345.dsv),左下「資料表」欄位右鍵「加入/移除資料表」→把「段考科目」資料表加到右邊欄位→確定,上面工具列資料來源檢視→重新整理。
  3. 新增維度:「年度目標」

勾選「使用現有的資料表」,下一步→主資料表:年度目標,索引鍵資料行:年度,名稱資料行:年度,下一步→可用的屬性:年度、目標,下一步→命名為:年度目標,完成。

  1. Cube視窗(A12345.cube),「Cube結構」頁籤,左邊「維度」欄按右鍵「加入Cube維度」:把「年度目標」維度加進來。
  2. Cube視窗(A12345.cube),「Cube結構」頁籤,左邊「量值」欄按右鍵「新增量值」:
  • 使用方式:總和
  • 來源資料表:年度目標
  • 來源資料行:目標
  • 「顯示所有資料行」不打勾
  1. 定義關聯性:(此步驟沒做的話,目標會自動加總,資料錯誤)

Cube視窗(A12345.cube),「維度使用方式」頁籤,定義關聯性,

  • 選取關聯性類型:一般
  • 資料粒度屬性:年度
  • 維度資料表:年度段考表
  • 量值群組資料表:年度目標
  • 關聯性:
  • 維度資料行:年度
  • 量值群組資料行:年度
  1. 重新處理CUBE,到EXCEL「資料」頁籤→連線→全部重新整理,即可看到剛剛建立的「段考科目目標」,放到樞紐分析表「值」欄位。
  • 篩選:年度段考階層>科目階層
  • 欄:年度段考階層
  • 列:姓名
  • 值:分數>年度目標.目標
  • 更換「成績KPI」條件
  1. 切換到Cube視窗(A12345.cube),「KPI」頁籤,左上工具列按「重新連接」,這樣左下量值群組欄位才不會錯誤,並顯示最新的資料表。
  2. 把目標運算式:改成「[年度目標].[目標]」
  3. 把狀態運算式:把「分數>=80」的80換成「[年度目標].[目標]」,下面亦同。
CASE

WHEN [Measures].[分數]>=[Measures].[目標] THEN 1

WHEN [Measures].[分數]<[Measures].[目標] THEN -1

ELSE 0

END

  1. 重新處理CUBE,到EXCEL「資料」頁籤→連線→全部重新整理。
  • 篩選:科目階層
  • 欄:年度段考階層
  • 列:姓名
  • 值:成績KPI>成績KPI目標>成績KPI狀態

SSDT計算屬性對話方塊

–SQL Server Data Tools

一、建立Cube

  1. 到E3下載bak,開啟SQL Server,並還原資料庫
  2. 開啟SQL Server Data Tools 2015,新增→專案→Analysis Services多維度和資料採礦專案
  3. 新增資料來源:

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

  1. 新增資料來源檢視:

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

  1. 設定邏輯主索引鍵、建立關聯

針對[科目表.科目編號]、[學生表.座號]、[年度段考表.段考代號],右鍵→設定邏輯主索引鍵,並建立資料表關聯(如圖)。

  1. 新增維度:

右邊「方案總管」→維度→右鍵新增維度→下一步→勾「使用現有資料表」→下一步→下一步→可用的屬性:全勾→下一步→完成。

  1. 建立階層:

建立「段考階層」:年度>段考。

  1. 新增Cube

右邊「方案總管」→CUBE→右鍵CUBE→下一步→勾”使用現有資料表”→下一步→量值群組資料表:勾選「成績單」→下一步→下一步→下一步→下一步→完成。

左上「處理」。

※如果執行撿角,請回去SQL Server授予目前使用者(as)讀取A12345資料庫的權限(db_owner或db_datareader)。

(安全性→登入→選取目前使用者(as)→右鍵”屬性”→左邊”使用者對應”→右邊勾選資料庫和權限)

二、計算「平均分數」

  1. 在CUBE的「計算」頁籤,上方工具列或「指令碼組合管理區域」右鍵,新增導出成員,
  • 名稱:[平均分數] (中括號不能刪!)
  • 運算式:[Measures].[分數]/[Measures].[成績單 計數]
    (可從左下拉進來)
  1. 上方工具列「處理」,重新處理CUBE完成後,「瀏覽器」頁籤→到EXCEL查看結果。
  • 欄:科目
  • 列:姓名
  • 值:平均分數

三、建立群組檢視─科目

如果科目要做群組檢視的話(例如:語文、文科、理科…)

※方法一:在資料表新增欄位

  1. 到SQL Server「科目表」資料表新增一個欄位:類別,
  2. 回到CUBE的資料來源檢視的視窗(A12345.dsv),上面工具列→資料來源檢視→重新整理,會有提示視窗顯示有哪些變更。
  3. 到科目維度把剛剛建立的「類別」拉進來。
  4. 建立「科目階層」:類別>科目
  5. CUBE處理完成後,到EXCEL「資料」頁籤「全部重新整理」,把剛剛建立的「科目階層」放到右下「篩選」欄。

※方法二:新增命名集

  1. 在CUBE「計算」頁籤,新增命名集,
  • 名稱:[語文類]
  • 運算式:{[科目維度].[科目].&[英語], [科目維度].[科目].&[國語]}
    (可從左下拉進來,前後加的是大括號)
  1. CUBE處理完成後,到EXCEL,會發現「科目維度」項目下多了「集」,展開後可以看到剛剛建立的「語文類」,放到右下”欄”。

※資料錯誤

如果量值資料表出現「載入中繼資料時發生錯誤:使用者沒有資料庫的存取權,或者資料庫不存在。」錯誤的話,重新處理CUBE,到上面工具列按「重新連接」即可。

四、建立「觀察學生」的檢視

  1. 在CUBE「計算」頁籤,新增命名集,
  • 名稱:[觀察學生]
  • 運算式:{ {[學生維度].[姓 名].&[王○慈],[學生維度].[姓   名].&[吳○慈], [學生維度].[姓   名].&[李○瑄]}}
    (可從左下拉進來,前後加的是大括號)
  1. CUBE處理完成後,到EXCEL,會發現「學生維度」項目下多了「集」,展開後可以看到剛剛建立的「觀察學生」,放到右下「列」。

這樣每次要看觀察名單內的學生,直接拉進去就可以看嚕。

五、建立「前三名」的檢視

  1. 在CUBE「計算」頁籤,新增命名集,
  • 名稱:[前三名]
  • 運算式:topcount([學生表].[姓名].children, 3, [Measures].[分數])
    (topcount函數:以分數為標準,從學生表中挑出前三位學生)
  1. CUBE處理完成後,到EXCEL,會發現「學生維度」項目下多了「集」,展開後可以看到剛剛建立的「前三名」,放到右下「列」。

六、建立「成績」的KPI

值運算式去跟目標運算式比較,狀態運算式看要顯示什麼燈號,狀態運算式要傳回三個值:1、0、-1。

  1. 在CUBE「KPI」頁籤,新增KPI,
  • 名稱:[成績]
  • 值運算式:[Measures].[分數]
  • 目標運算式:85
  • 狀態指標:形狀
  • 狀態運算式:

  1. CUBE處理完成後,到EXCEL,會發現多了「KPI」項目,把KPI→成績→狀態,放到右下「列」。

※一個公司通常不建議設定很多KPI,KPI的設定是很嚴謹的,門檻值的設定也是一門學問。

七、建立「前次分數」的檢視

  1. 在CUBE「計算」頁籤,新增導出成員,
  • 名稱:[前次分數]
  • 運算式:

睡著的同學:http://data.bilab.pro:999/bi/_CUBE/_MDX.TXT

SSDT建立不同語系的欄位翻譯

※SQL Server Data Tools

一、建立Cube

  1. 到E3下載bak,開啟SQL Server,還原資料庫
  2. 開啟SQL Server Data Tools 2015,新增→專案→Analysis Services多維度和資料採礦專案
  3. 新增資料來源:

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

  1. 新增資料來源檢視:

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

  1. 設定邏輯主索引鍵、建立關聯

針對[科目表.科目編號]、[學生表.座號]、[年度段考表.段考代號],右鍵→設定邏輯主索引鍵,並建立資料表關聯。

  1. 新增維度:

右邊「方案總管」→維度→右鍵新增維度→下一步→勾「使用現有資料表」→下一步→下一步→可用的屬性:全勾→下一步→完成。

  1. 建立階層:

「段考階層」:段考代號>年度>段考。

  1. 建立Cube

右邊「方案總管」→CUBE→右鍵CUBE→下一步→勾「使用現有資料表」→下一步→量值群組資料表:勾選「成績單」→下一步→下一步→下一步→下一步→完成。

左上「處理」。

  1. 「瀏覽器」頁籤,用EXCEL的樞紐分析表檢視此資料,
  • 篩選:
  • 欄:科目
  • 列:座號(不要做小計,以列表方式呈現)、姓名
  • 值:分數

※如果執行撿角,請回去SQL Server授予目前使用者(as)讀取A12345資料庫的權限(db_owner或db_datareader)。

(安全性→登入→選取目前使用者(as)→右鍵「屬性」→左邊「使用者對應」→右邊勾選資料庫和權限)

二、更新異動資料到CUBE

  1. 到SQL Server,在[A12345].[學生表.dbo],新增一個欄位,
  • 資料行名稱:性別
  • 資料類型:nchar(1)

,然後儲存。

  1. 編輯「學生表」資料表,填入每一位學生的性別(隨機填入即可)。
  2. 到SQL Server Data Tools,「資料來源檢視」→上面工具列「資料來源檢視」→重新整理。
  3. 切換到「學生維度.dim」視窗,把剛剛建立的欄位(性別)從右側資料來源檢視的學生表,拉到左側的屬性。
  4. 將CUBE再「處理」一次,到EXCEL「資料」頁籤→上面工具列「全部重新整理」,就可以看到剛剛學生表新增的欄位。

※檢視方塊

每一個都有各自很多的Fact表、量值,依據不同的資料做分類

一、建立檢視方塊

實作:利用檢視方塊選取不同的量值跟維度的檢視

  1. 切換到cube視窗→「檢視方塊」頁籤
  2. 對著A12345資料表按右鍵→新增檢視方塊,科目維度不勾選,「處理」CUBE。
  3. 開啟新的EXCEL,「資料」頁籤→取得外部資料:從其他來源→從Analsis Server→伺服器名稱:.→下一步→選取專案→選取剛剛建立的檢視方塊→完成。
  4. 檢視此資料的方式:樞紐分析表→確定。

※因為一般來說使用者可能不知道資料的關聯性,如果可以依據不同的檢視方塊,就可以依據不同類別資料做更細部分析。

二、建立翻譯─針對「維度階層名稱」

實作:利用翻譯來讓資料能轉譯成多國語言-先做Cube英譯

  1. 切換到cube視窗→「翻譯」頁籤
  2. 對著A12345資料表按右鍵→新增翻譯→選取新翻譯的語言:「英文(美國)」→OK→輸入對譯英文→「處理」CUBE。
  3. 開啟新的EXCEL,「資料」頁籤→取得外部資料:從其他來源→從Analsis Server→伺服器名稱:.→下一步→選取專案→選取CUBE(A12345.cube)→點擊「瀏覽」→複製資料夾路徑到檔案總管開啟(預設路徑通常是:C:\Users\user\Documents\我的資料來源)→儲存→完成。
  4. 檢視此資料的方式:樞紐分析表→確定。

這時候會發現:資料(量值階層)還是中文資料,因為資料讀取是依據系統語系才做變動,但可以用一個方法去強制修改資料讀取語系。

  1. 到剛剛的資料夾路徑下,對剛剛建立的.odc檔案(A12345.odc)按右鍵→edit with notepad++。
  2. 在<odc:ConnectionString>那行加入粗體字串:
    ;Initial Catalog=MultidimensionalProject1;Locale Identifier=1033</odc:ConnectionString>
    →儲存。

※Google Keyword:locale Identifier list,或參考Microsoft Locale ID Values

  1. 重新開啟剛剛的.odc檔案:

開啟新的EXCEL,「資料」頁籤→取得外部資料:現有連線→選剛剛設定的.odc,就會發現項目名稱變成剛剛設定的英文。

三、建立翻譯─針對「維度的欄位」─以「科目維度」為例

  1. 切換到科目維度.dim視窗→「翻譯」頁籤
  2. 對著科目維度按右鍵→新增翻譯→選取新翻譯的語言:「英文(美國)」→OK→依據下圖輸入對譯英文→「處理」CUBE。
  3. 到EXCEL「資料」頁籤→上面工具列「全部重新整理」,就可以看到剛剛輸入的英文欄位。

四、建立翻譯─針對資料表內容─以「科目表」為例

  1. 到SQL Server,在[A12345].[學生表.dbo],新增一個欄位:
  • 資料行名稱:SubjectName
  • 資料類型:nchar(10)

,然後儲存。

  1. 編輯「科目表」資料表,輸入每個科目的英文翻譯。
  2. 到SQL Server Data Tools,「資料來源檢視」→上面工具列「資料來源檢視」→重新整理。
  3. 切換到「科目維度.dim」視窗,把剛剛建立的欄位(SubjectName)從右側資料來源檢視的科目表,拉到左側的屬性。
  4. 將CUBE再「處理」一次,到EXCEL「資料」頁籤→上面工具列「全部重新整理」。

關聯檢測結果

※比較新舊資料

  1. 建立預存程序

http://data.bilab.pro:999/db/SELECT_DISTINCT%20(2).txt複製語法,新增查詢之後貼上,將ALTER PRODEDURE改成CREATE PRODEDURE,執行

  1. 執行預存程序

AdventureWorks2012資料庫→可程式性→預存程序,找到剛剛建立的預存程序[new_auto_Calc_distinct],右鍵「執行預存程序」→確定,將執行結果複製到EXCEL(執行結果左上角按右鍵「隨標頭一同複製」)。

  1. 重複上述步驟1~2,將兩個步驟執行語法的資料庫改成[AdventureWorks2016CTP3],執行後一樣把結果複製到EXCEL。
  2. 比較兩個資料表差異

※【SSDT】

  1. 開啟SSDT
  2. 新增專案→Integration Services專案
  3. 左邊工具箱→通用→新增一個「資料分析工作」→點兩下→
  • 「一般」頁籤→Destination:下拉清單「新增檔案連接」→使用類型:建立檔案→命名Data1→確定
  • 「設定檔要求」頁籤→設定檔類型:資料行統計資料設定檔要求,後面的「要求識別碼」會自然產生→點上面檢視:資料行統計資料設定檔要求→下面Connection Manager:下拉清單「新增NET連接」→伺服器:.、資料庫:AdventureWorks2012→OK→TableOrView:[Sales].[SalesOrderHeader]、Column:SubTotal。

※關聯檢測結果

一、資料準備

  1. 以系統管理員身分執行SQL Server
  2. 到bilab.pro:999/bi/,下載DB關聯分析.zip,解壓縮後,把「CH07範例資料庫」附加到SQL Server。

二、新增資料表,命名為:關聯檢測結果

資料行名稱  資料類型

測試日期   datetime

表1      nvarchar(50)

表1欄位    nvarchar(50)

表1值     nvarchar(50)

表2      nvarchar(50)

表2欄位    nvarchar(50)

表2值     nvarchar(50)

三、建立兩個檢視表

※訂單 & 訂單明細

  1. 選取[訂單]、[訂單明細]資料表,並做FULL OUTER JOIN關聯建立兩個檢視表
  2. 篩選條件,勾選:
  • [訂單].[訂單編號],篩選:IS NULL
  • [訂單明細].[訂單編號],或:IS NULL
  1. 依序新增資料