角色扮演維度

  • 建立資料來源─檢視表

─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. 依序新增資料行
  • Getdate() AS 日期
  • ‘訂單’ AS 表1
  • ‘訂單編號’ AS 表1欄位
  • 勾選[訂單].[訂單編號],別名:訂單_訂單編號
  • ‘訂單明細’ AS 表2
  • ‘訂單編號’ AS 表2欄位
  • 勾選[訂單明細].[訂單編號],別名:明細_訂單編號

※訂單明細 & 產品資料

  1. 選取[訂單明細]、[產品資料]資料表,並做FULL OUTER JOIN關聯
  2. 篩選條件,勾選:
  • [訂單明細].[產品編號],篩選:IS NULL
  • [產品資料].[產品編號],或:IS NULL
  1. 依序新增資料行
  • Getdate() AS 日期
  • ‘訂單明細’ AS 表1
  • ‘明細編號’ AS 表1欄位
  • 勾選[訂單明細].[產品編號],別名:明細_產品編號
  • ‘產品資料’ AS 表2
  • ‘產品編號’ AS 表2欄位
  • 勾選[產品資料].[產品編號],別名:產品_產品編號

四、「關聯檢測結果」資料表

  1. 在兩個檢視表的語法最前面加上:INSERT INTO 關聯檢測結果,一起複製,新增查詢,貼上並執行。

資料指標2

一、SQL

  1. 在AdventureWork2012資料庫建立查詢,查詢COLUMNS、INFORMATION_SCHEMA.TABLES所有資料,並將兩段語法分別複製起來。

(AdventureWork2012→檢視→系統檢視表→INFORMATION_SCHEMA.COLUMNS)

(AdventureWork2012→檢視→系統檢視表→INFORMATION_SCHEMA.TABLES)

  1. 建立一個新資料庫,新增檢視表,將剛剛兩段語法分別貼上,執行後,分別儲存成vAdv2012Columns、vAdv2012Tables兩張檢視表。
  2. 建立新檢視表,加入vAdv2012Columns、vAdv2012Tables兩個資料表,用Table_Name做關聯。
  3. 開啟EXCEL,「資料」頁籤→取得外部資料→從SQL Server→伺服器名稱:.、WINDOWS驗證,下一步,選取剛剛建立的檢視表(vAdv2012AllCharColumns)→下一步→完成執行成儲存成vAdv2012AllCharColumns檢視表。
  4. 修改欄位名稱:
  • TABLE_NAME→資料表
  • COLUMN_NAME→欄位名稱
  • IS_NULLABLE→NULL
  • CHARACTER_MAXIMUM_LENGTH→長度
  • 新增一欄:說明

二、統計Product.Color各種字元長度的資料筆數。

  1. 查詢Product資料表中,Color、Name、ProductNumber三個欄位的平均長度。
SELECT ‘Color’ AS 欄位名稱, avg(Len([Color])) AS 平均長度

FROM [AdventureWorks2012].[Production].[Product]

union

SELECT ‘Name’ AS 欄位名稱, avg(Len([Name])) AS 平均長度

FROM [AdventureWorks2012].[Production].[Product]

union

SELECT ‘ProductNumber’ AS 欄位名稱, avg(Len([ProductNumber])) AS 平均長度

FROM [AdventureWorks2012].[Production].[Product]

  1. 用子查詢(data)的結果,依ColorLength做分組(GROUP BY),再依ColorLength做排序
SELECT ColorLength, count(*) as 資料筆數

FROM

–名為data的子查詢

(

SELECT TOP 1000 [Name]

,[Color]

,Case When [Color] is Null then 0 else Len([Color]) end ColorLength

FROM [AdventureWorks2012].[Production].[Product]

order by Case When [Color] is Null then 0 else Len([Color]) end

) data

GROUP BY ColorLength

ORDER BY ColorLength

三、查詢AdventureWorks2012資料庫中各個表格的每個欄位中的字元長度

  1. 把vAdv2012AllCharColumns檢視表增加Table_Catalog、vAdv2012Tables.Table_Schema兩個欄位,並移到最上面,IS_NULLABLE欄為增加篩選條件= ‘NO’。
  1. http://data.bilab.pro:999/db/Select_Min.txt的語法複製到新的查詢(語法可參考20170309_Cursor)。
    Declare mydata Cursor for

    –因為最後cmd1的指令會超過定義的字串長度,故用top20限制資料來源筆數

    SELECT          top 20 dbo.vAdv2012Tables.TABLE_CATALOG, dbo.vAdv2012Tables.TABLE_SCHEMA, dbo.vAdv2012Columns.TABLE_NAME,

    dbo.vAdv2012Columns.COLUMN_NAME, dbo.vAdv2012Columns.IS_NULLABLE,

    dbo.vAdv2012Columns.CHARACTER_MAXIMUM_LENGTH

    FROM              dbo.vAdv2012Columns INNER JOIN

    dbo.vAdv2012Tables ON dbo.vAdv2012Columns.TABLE_NAME = dbo.vAdv2012Tables.TABLE_NAME

    WHERE          (dbo.vAdv2012Columns.DATA_TYPE = N’nvarchar’) AND (dbo.vAdv2012Tables.TABLE_TYPE = ‘BASE TABLE’) AND

    (dbo.vAdv2012Columns.IS_NULLABLE = ‘NO’)

    declare @cmd1 nvarchar(4000)

    –上面資料來源查詢了6個欄位,所以要宣告6個變數來儲存

    declare @colCatalog nvarchar(100)

    declare @colSchema nvarchar(100)

    declare @colTable nvarchar(100)

    declare @colColumn nvarchar(100)

    declare @colNull nvarchar(100)

    declare @colLen nvarchar(100)

    Open mydata

    Fetch Next from mydata into @colCatalog, @colSchema, @colTable, @colColumn, @colNull, @colLen

    Declare @flag int

    set @flag=0

    set @cmd1=”

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @flag=1

    begin

    set @cmd1=@cmd1 + ‘ union ‘;

    end

    SET @cmd1=@cmd1+’ Select ”’ + @colTable + ”’ as ‘+ N’表格名稱’ + ‘, ”’+@colColumn+ ”’ as ‘+N’欄位名稱’+’, avg(Len(‘ + @colLen + ‘)) as ‘ + N’平均長度’ + ‘ from ‘ + @colCatalog + ‘.’ + @colSchema + ‘.’ + @colTable

    set @flag=1

    FETCH NEXT FROM mydata INTO @colCatalog, @colSchema, @colTable, @colColumn, @colNull, @colLen

    END

    print (@cmd1)

    exec (@cmd1)

    CLOSE mydata

    DEALLOCATE mydata

資料指標1

一、資料管理(Data Management)

  1. Data Profiling
  2. Data Standardization
  3. Geocoding
  4. Matching or linking
  5. Monitoring
  6. Batch and Real time

二、Data Profiling

確認資料的定義是什麼。

  1. Data Domain, Dictionary, Glossary
    1. Field Name
    2. Data Type
    3. Occurrence of Null

確認資料裡面有沒有NULL值,或能不能包含NULL值。EX性別、學號。或者,某個欄位的NULL值比例太高,就要評估這個欄位是否有存在的必要性。

  • 練習:查詢[SalesOrderHeader].[CreditCardID]欄位,空值與非空值的資料筆數。

SELECT SUM(CASE WHEN CreditCardID IS NULL THEN 1 ELSE 0 END) AS NULL_COUNT, SUM(CASE WHEN CreditCardID IS NULL THEN 0 ELSE 1 END) AS NOTNULL_COUNT

FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]

D. Indexing

索引會影響資料處理的速度(效能)。

E. String Pattern

資料裡面的文字描述格式是否一致。EX地址。

F. Constraint, Validation rules

驗證規則

G. Description

  • 練習:用變數儲存語法。查詢salesorderheader資料表的所有資料。

declare @cmd1 nvarchar(100)

set @cmd1 = ‘select * from ‘

set @cmd1 = @cmd1 + ‘sales.salesorderheader’

–print @cmd1

exec(@cmd1)

  1. Relationship Discovery

資料表的關係,去發掘資料表之間有什麼相關性。

A. ER-Diagram/ER-Model

B. Relationship

C. Cardinality

D. Referential Test

3. Data Quality Summary

Minimum, maximum, mean, mode, percentile, standard deviation, frequency, variation, aggregates such as count and sum

(1) 查詢SalesOrderHeader資料表中,銷售金額的相關計算:

建立檢視表,選取[SalesOrderHeader]資料表,先使用COUNT計算全部資料筆數、SalesOrderID筆數、CustomerID筆數,再計算SubTotal的合計、最小值、最大值、平均、變異數

SELECT          COUNT(*) AS TOTAL_COUNT, COUNT(DISTINCT SalesOrderID) AS SALES_COUNT, COUNT(DISTINCT CustomerID) AS CUSTOMER_COUNT, SUM(SubTotal) AS 銷售金額_合計, MIN(SubTotal) AS 銷售金額_最小, MAX(SubTotal) AS 銷售金額_最大, AVG(SubTotal) AS 銷售金額_平均, STDEV(SubTotal) AS 銷售金額_變異

FROM              Sales.SalesOrderHeader

(2) 查詢檢視SalesOrderHeader資料表中,多個欄位的相關計算:

建立檢視表,選取[SalesOrderHeader]資料表,用上一個練習的語法修改,並在最前面補上一個欄位(N’欲查詢欄位名稱’ AS ‘欄位’)。

SELECT          N’銷售金額’ AS ‘欄位’, SUM(SubTotal) AS 合計, MIN(SubTotal) AS 最小, MAX(SubTotal) AS 最大, AVG(SubTotal) AS 平均, STDEV(SubTotal) AS 變異

FROM              Sales.SalesOrderHeader

UNION

SELECT          N’稅金’ AS ‘欄位’, SUM(TaxAmt) AS 合計, MIN(TaxAmt) AS 最小, MAX(TaxAmt) AS 最大, AVG(TaxAmt) AS 平均, STDEV(TaxAmt) AS 變異

FROM              Sales.SalesOrderHeader

  1. Error Discovering

illegal values, misspellings, missing values, varying value representation, and duplicates

三、SQL Server Information_Schema

  1. 建立查詢,查詢COLUMNS中,TABLE_SCHEMA、TABLE_NAME、COLUMN_NAME三個欄位,TABLE_NAME = ‘SALESORDERHEADER’,DATA_TYPE = ‘int’

(AdventureWork2012→檢視→系統檢視表→INFORMATION_SCHEMA.COLUMNS)

SELECT [TABLE_SCHEMA]

,[TABLE_NAME]

,[COLUMN_NAME]

FROM [AdventureWorks2012].[INFORMATION_SCHEMA].[COLUMNS]

WHERE TABLE_NAME = ‘SALESORDERHEADER’ AND DATA_TYPE = ‘int’

四、函數

declare @cmd1 nvarchar(100)

set @cmd1 = ‘select * from ‘

set @cmd1 = @cmd1 + ‘sales.salesorderheader’

print @cmd1

exec(@cmd1)

五、資料指標(Cursor)

練習:查詢SALESORDERHEADER資料表中,資料類型為整數的欄位有哪些,並計算每個欄位的最大最小值、平均、變異。

–宣告Cursor for 迴圈

Declare mydata Cursor for

–for迴圈裡面的資料

SELECT [COLUMN_NAME]

FROM [AdventureWorks2012].[INFORMATION_SCHEMA].[COLUMNS]

WHERE TABLE_NAME = ‘SALESORDERHEADER’ AND DATA_TYPE = ‘int’

–宣告變數

DECLARE @cmd1 nvarchar(2000)

DECLARE @colname nvarchar(100)

–開始run cursor

OPEN mydata

FETCH NEXT FROM mydata INTO @colname  –將第一筆資料填入變數

–宣告變數、設定變數值

DECLARE @flag int

SET @flag = 0

SET @cmd1 = ”

WHILE @@FETCH_STATUS = 0  –檢查是否有讀取到資料;WHILE用來處理迴圈,當為TRUE時則進入迴圈執行

BEGIN  –BEGIN…END表示一個區塊,通常搭配IF…ELSE或WHILE一起使用

IF @flag=1

BEGIN

SET @cmd1 = @cmd1 + ‘ union ‘;

END

SET @cmd1= @cmd1 + ‘Select ”’ + @colname + ”’ AS ‘ + N’欄位名稱’ + ‘, MAX(‘ + @colname + ‘) AS ‘ + N’最大值’ + ‘, MIN(‘ + @colname + ‘) AS ‘ + N’最小值’ + ‘, AVG(‘ + @colname + ‘) AS ‘ + N’平均’ + ‘, STDEV(‘ + @colname + ‘) AS ‘ + N’變異’ + ‘ from Sales.SalesOrderHeader’

SET @flag = 1

FETCH NEXT FROM mydata INTO @colname  –將下一筆資料填入變數

END

print (@cmd1)

exec (@cmd1)

CLOSE mydata  –關閉cursor與參數的關聯

DEALLOCATE mydata  –將cursor物件從記憶體移除

  • 資料庫資料表名稱是否區分大小寫

資料庫右鍵→屬性→「一般」頁籤→定序

CI:不區分大小寫,CS:區分大小寫

使用erwin建立資料表實體模型

【20170302_建立環境】

一、安裝ERwin:

  1. 「130.33.122:8080」或「140.130.33.153:8080」下載erwin64bit.exe和ERwinEvalLicense.lic.zip,將「ERwinEvalLicense.lic.zip」檔名修改成「ERwinEvalLicense.lic」。
  2. 執行exe,下一步 → 同意 → 下一步到底。
  3. 安裝完成後,執行erwin Data Modeler r9.7 (64-bit).exe,User Local licenese→Install License File→選擇lic檔案→OK。

二、開啟erwin程式

  1. 左上New→Type:Logical→OK。
  2. 按上面工具列,新增下列三個Entity。

● 輸入Title:學生→按Tab鍵輸入:學號→按Tab鍵依序輸入:姓名、性別、生日、地址、手機、家裡電話、地址、籍貫、父親、母親、身分證。(按Enter鍵輸入下一筆)

● 輸入Title:課程→按Tab鍵輸入:課程代號→按Tab鍵依序輸入:名稱、學分。

● 輸入Title:老師→按Tab鍵輸入:代號→按Tab鍵依序輸入:姓名、身分證、性別。

  1. 點選上面建立「多對多連線」,「學生:學號」與「課程:課程代號」兩個Entity建立關聯。
  2. 上面Model→Domains…→最上面new→新增:

● Name:身份證型別

● Logical Data Type:CHAR(10)

→下面頁籤

● 「General頁籤」→Null Option:Not Null

● 「Constraint頁籤」→new→

● Name:身分證長度10

● Validation Rule:下拉清單進去新增

● 上面Name:身分證長度10

● 下面「Definition」頁籤:Len(身份證)=10

● 這部分也可以從「學生」Entity右鍵→Attribute Properties進去新增,差別在於:直接從Domains進去新增的話,適用於新增一個廣泛規則,利於未來做一次性規則修改。
(Ex:身分證變成12碼,可直接修改Domains規則,套用此規則得依其改變)

  1. 修改欄位資料型態(Logical Data Type)

● 「學生」Entity右鍵→Attribute Properties:

學號:CHAR(8)、性別:CHAR(2)、地址:CHAR(50)、
生日:DATE、身分證:CHAR(10)

● 「課程」Entity右鍵→Attribute Properties:

學分:INT

● 「老師」Entity右鍵→Attribute Properties:

● 身分證:「Constraint」頁籤→左下規則:選上面建立的身分證型別

  1. 左上角儲存。
  2. 進行Derive:

● 上面Action→Desing Layers→Derive New Model→New Model Type:Physical、Target Database:SQL server→下一步到底→Derive

  1. 在「老師」Entity新增「手機」欄位
  2. 異動Physical Model方法(視窗點擊回Physical,才能選sync Derive with Model:

● 在(Derive之後的Diagram)中,上面Action→Desing Layers→sync Derive With Model,點擊下面「Sync」按鈕,點選「手機」(檢查哪些資料有異動),按上面「→」(更新到SQL Server)

  1. 開啟SQL Server,建立「A+學號」資料庫
  2. 回到erwin,Action→Forward Engineer→Schema→General

Server:.

Database:剛剛建立的「A+學號」資料庫

→Connect

  1. 反向工程:Action→Reverse Engineer,只勾選table,New Model Type:Physical,先用AdventureWork2012資料庫→Connect