新手學SSIS 2008樞紐轉換與取消樞紐轉換

by Derrick 11. 一月 2012 13:46

前言


在 SQL Server 2005 Transact-SQL陳述式上,提供了「PIVOT 關係運算子」與「UNPIVOT 關係運算子」。可以用來將資料表值運算式變更為另一個資料表。

其中,PIVOT 會將運算式內一個資料行中的唯一值轉成輸出中的多個資料行,以旋轉資料表值運算式,然後依據最終輸出的需要,對其餘的任何資料行值執行必要的彙總。而UNPIVOT 執行的作業則與 PIVOT 相反,它會將資料表值運算式旋轉為資料行值。

在SSIS 2005上的「資料轉換工作」內,提供了「樞紐轉換(Pivot transformation)」與「取消樞紐轉換(Unpivot transformation)」來達成所需要的功能。

樞紐轉換的補充事項

但在SQL Server 2008 R2 線上叢書,並沒有特別說明幾個注意事項,筆者整理如下:

  • 在「樞紐」轉換上,沒有提供彙總運算的功能,請事先使用「彙總」轉換來處理彙總運算。
  • 在「樞紐」轉換上,擔任「Pivot Key」的資料行,其資料是不得有重複值,而且需要已經排序好的。

因此,請檢視其 SortKeyPosition 屬性的設定,並事先利用 ORDER BY 子句或是「排序」轉換執行運算處理。也就是說,若要使用「樞紐」轉換,請先使用「彙總」轉換,使用「排序」轉換後,才能使用「樞紐」轉換。

示範環境

SQL Server 2008 R2 x64版本。

實作練習一:認識樞紐轉換(Pivot transformation)

工作目標:

  • 使用資料流程工作,設定資料流程來源
  • 使用彙總轉換
  • 使用排序轉換
  • 使用樞紐轉換
  • 使用資料流程目的地
  • 執行封裝

工作一:使用資料流程工作,設定資料流程來源

步驟01. 檢視資料來源:myOrders01.xlsx

image

圖1:Excel2010_資料來源

步驟01. 在「封裝設計師」視窗,新增加一個「資料流程」工作,命名為:樞紐轉換_Pivot。

步驟02. 點選進入到「資料流程」頁面,在左邊的工具箱,由「資料流程來源」區域,拖曳所需的來源物件,設定與其連線的資訊連線。

在本次範例中,使用一個「OLE DB 來源」,連接到 Excel 2010 (*.xlsx) 為例:myOrders_Excel_2010。

image

圖2:增加_資料來源

工作二:使用彙總轉換

步驟01. 在左邊的工具箱,由「資料流程轉換」區域,選擇「彙總」轉換拖曳到右邊的封裝設計師頁面上,命名為:彙總轉換_SubTotal。

步驟02. 點選資料來源:myOrders_Excel_2010,將其綠色線路的「資料流程路徑」,拖曳到「彙總」轉換上。

步驟03. 滑鼠雙擊此「彙總」轉換,在「彙總轉換編輯器」視窗,設定以下參數:

  • 在「可用的輸入資料行」區域,勾選所需的資料行,例如:Customer、Product、Quantity。
  • 設定 Customer 的「作業」為:群組依據。
  • 設定 Product 的「作業」為:群組依據。
  • 設定 Quantity 的「作業」為:總和,並修改其「輸出別名」為:_SubTotal。

image

圖3:設定「彙總轉換編輯器」

步驟04. 在「彙總轉換編輯器」視窗,點選「確定」。

image

圖4:檢視已經加入彙總轉換

工作三:使用排序轉換

步驟01. 在左邊的工具箱,由「資料流程轉換」區域,選擇「排序」轉換拖曳到右邊的封裝設計師頁面上,命名為:排序轉換_Customer。

步驟02. 點選「彙總」轉換:彙總轉換_SubTotal,將其綠色線路的「資料流程路徑」,拖曳到「排序」轉換:排序轉換_Customer 上。

步驟03. 滑鼠雙擊此「排序」轉換,在「排序轉換編輯器」視窗,設定以下參數:

在「可用的輸入資料行」區域,勾選所需的資料行,例如:Customer。

image

圖5:設定「排序轉換編輯器」

步驟04. 在「排序轉換編輯器」視窗,點選「確定」。

image

圖6:檢視已經加入排序轉換

工作四:使用樞紐轉換

步驟01. 在左邊的工具箱,由「資料流程轉換」區域,選擇「樞紐」轉換拖曳到右邊的封裝設計師頁面上。

步驟02. 點選「排序」轉換:排序轉換_Customer,將其綠色線路的「資料流程路徑」,拖曳到「樞紐」轉換:樞紐轉換_Product 上。

步驟03. 滑鼠雙擊此「樞紐」轉換,在「樞紐 的進階編輯器」視窗,設定以下參數:

  • 點選「元件屬性」頁籤,在「Name」方塊,輸入:樞紐轉換_Product。

image

圖7:設定「元件屬性」,在「樞紐 的進階編輯器」視窗

  • 點選「輸入資料行」頁籤,在「可用的輸入資料行」區域,勾選所需要的資料行,例如:Customer、Product、SubTotal。

image

圖8:尚未勾選_在「可用的輸入資料行」區域

image

圖9:已經勾選_在「可用的輸入資料行」區域

  • 在「使用類型」區域,下拉選取可以看到有以下的類型:READONLY 與 READWRITE。

image

圖10:可以使用類型_READONLY 與 READWRITE

  • 點選下方的「重新整理」。

image

圖11:點選下方的「重新整理」


在上圖,點選「重新整理」後,關於「輸入資料行」的檢查錯誤之警告訊息應該會消失。

步驟04. 延續步驟03. ,繼續點選「輸入與輸出屬性」頁籤,設定以下參數:

image

圖12:展開「輸入與輸出屬性」頁籤

  • 在「輸入及輸出」區域,展開「樞紐預設輸入」\「輸入資料行」節點,可以觀察到先在「輸入資料行」頁籤上所勾選的資料行,例如:Customer、Product、SubTotal。
  • 設定「PivotUsage」屬性:
  • 點選「Customer」方塊,在右邊窗格,在「自定屬性」區域,點選「PivotUsage」,輸入:1。

image

圖13:Customer_「PivotUsage」屬性,輸入:1

  • 點選「Product」方塊,在右邊窗格,在「自定屬性」區域,點選「PivotUsage」,輸入:2。

image

圖14:Product_「PivotUsage」屬性,輸入:2

  • 點選「Quantity」方塊,在右邊窗格,在「自定屬性」區域,點選「PivotUsage」,輸入:3。

image

圖15:SubTotal_「PivotUsage」屬性,輸入:3

步驟05. 延續步驟04.,繼續點選「輸入與輸出屬性」頁籤,設定以下參數:

  • 在「輸入及輸出」區域,展開「樞紐預設輸出」」\「輸出資料行」節點,增加以下的「輸出資料行」,例如:客戶、Bike、Chain。
  • 其中,設定輸出資料行:「客戶」,應該對應到「輸入資料行」:Customer。因此,請先找出此資料行的歷程識別碼「LineageID」屬性,例如:84。請參考上圖:13_Customer_「PivotUsage」屬性,輸入:1。
  • 點選「加入資料行」,在右邊窗格,設定以下的參數:
  • 在「Name」方塊,輸入:客戶。
  • 在「SourceColumn」方塊,輸入:84。

image

圖16:設定客戶的「SourceColumn」方塊

  • 接下來,設定輸出資料行:Bike,應該對應到「輸入資料行」:SubTotal。因此,請先找出此資料行的歷程識別碼「LineageID」屬性,例如:94。請參考上圖:15_SubTotal_「PivotUsage」屬性,輸入:3。
  • 點選「加入資料行」,在右邊窗格,設定以下的參數:
  • 在「Name」方塊,輸入:Bike。
  • 在「PivotKeyValue」方塊,輸入:Bike。
  • 在「SourceColumn」方塊,輸入:94。

image

圖17:設定 Bike 的「PivotKeyValue」與「SourceColumn」方塊

  • 接下來,設定輸出資料行:Chain,應該對應到「輸入資料行」:SubTotal。因此,請先找出此資料行的歷程識別碼「LineageID」屬性,例如:94。
  • 點選「加入資料行」,在右邊窗格,設定以下的參數:
  • 在「Name」方塊,輸入:Chain。
  • 在「PivotKeyValue」方塊,輸入:Chain。
  • 在「SourceColumn」方塊,輸入:94。

image

圖18:設定 Chain 的「PivotKeyValue」與「SourceColumn」方塊


步驟06. 點選「確定」。

由上,已經完成設定輸出資料行:客戶、Bike、Chain。

image

圖19:檢視已經設定「樞紐轉換 」

工作五:使用資料流程目的地

步驟01. 在左邊的工具箱,由「資料流程目的地」區域,選擇「一般檔案目的地」物件,拖曳到右邊的封裝設計師頁面上,命名為:一般檔案目的地_myPivot。

步驟02. 點選資料轉換:合併聯結_內部的綠色線路之「資料流程路徑」,拖曳到「一般檔案目的地」物件。

步驟03. 滑鼠雙擊此「一般檔案目的地」物件,設定相關的連線資料。

image

圖20:檢視已經設定好_一般檔案目的地_myPivot

工作六. 執行封裝

步驟01. 執行此封裝。

image

圖21:執行封裝

步驟02. 停止與結束執行,在每個「資料流程路徑」上,加入「資料檢視器」,再度執行此封裝。

image

圖22:檢視來源資料_使用「資料檢視器」

image

圖23:檢視彙總轉換後_使用「資料檢視器」

image

圖24:檢視排序轉換後_使用「資料檢視器」

image

圖25:檢視樞紐轉換後_使用「資料檢視器」

image

圖26:檢視一般檔案目的地

認識樞紐選項

在先前的步驟中,我們設定PivotUsage 屬性,此屬性是以指定每個資料行在樞紐處理中執行的角色。

PivotUsage 的有效值為 0、1、2 和 3。請參考以下表格1的說明:

選項

描述

0

資料行未經樞紐,且資料行值已傳送至轉換輸出。

1

資料行是集索引鍵的一部分,可識別一個集中的一或多個資料列。
具有相同集索引鍵的所有輸入資料列會組合成一個輸出資料列。

2

資料行為樞紐資料行。至少應從每個資料行值建立一個資料行。

3

此資料行的值會置於樞紐後建立的資料行中。

表1:PivotUsage 選項

注意事項:

在設定「PivotUsage」屬性時,至少有一個資料行設定為 2,至少一個資料行設定為 3。而設定為 0 或 1 值部分,則是可以選擇一個輸入資料行擔任。

以下的表格2,描述樞紐轉換之輸出資料行的自訂屬性,其所有屬性都是可讀寫的。

屬性

資料類型

描述

PivotKeyValue

字串

資料行的其中一個可能值,此值會由其 PivotUsage 屬性的值標示為樞紐索引鍵。
此屬性的值可以使用屬性運算式指定。

SourceColumn

整數

包含樞紐值之輸入資料行的 LineageID,或 -1。值為 -1 表示此資料行不會用於樞紐作業中。

表2:樞紐轉換之輸出資料行的自訂屬性


實作練習二:認識取消樞紐轉換(Unpivot transformation)

工作目標:

  • 使用資料流程工作,設定資料流程來源
  • 使用取消樞紐轉換
  • 使用資料流程目的地
  • 執行封裝

工作一:使用資料流程工作,設定資料流程來源


步驟01. 檢視資料來源:myPivot.txt

image

圖27:myPivot_資料來源


步驟02. 在「封裝設計師」視窗,新增加一個「資料流程」工作,命名為:取消樞紐轉換_UnPivot。

步驟03. 點選進入到「資料流程」頁面,在左邊的工具箱,由「資料流程來源」區域,拖曳所需的來源物件,設定與其連線的資訊連線。

  • 在本範例中,使用一個「一般檔案來源」,連接到文字檔案為例:myPivot.txt,命名為:一般檔案來源_myPivot。

image

圖28:myPivot_資料來源

工作二:使用取消樞紐轉換

步驟01. 在左邊的工具箱,由「資料流程轉換」區域,選擇「取消樞紐」轉換拖曳到右邊的封裝設計師頁面上,命名為:取消樞紐轉換_數量。

步驟02. 點選資料來源:一般檔案來源_myPivot,將其綠色線路的「資料流程路徑」,拖曳到「取消樞紐」轉換:取消樞紐轉換_數量 上。

步驟03. 滑鼠雙擊此「取消樞紐」轉換,在「取消樞紐轉換編輯器」視窗,設定以下參數:

image

圖29:初始化的「取消樞紐轉換編輯器」

  • 在「可用的輸入資料行」區域,勾選:Bike 與 Chain。
  • 客戶部分,仍是設定為「通過」。
  • 在下方區域,對「輸入資料行」:Bike 與 Chain 的「目的資料行」,輸入:數量。
  • 而其「樞紐索引鍵值(Pivot Key Value)」區域,仍是使用其預設產生的值:Bike 與 Chain。
  • 在「樞紐索引鍵值資料行名稱」方塊,輸入:產品。

image

圖30:設定「取消樞紐轉換編輯器」

步驟04. 在「取消樞紐轉換編輯器」視窗,點選「確定」。

image

圖31:檢視_已經加入_取消樞紐轉換

工作三:使用資料流程目的地

步驟01. 在左邊的工具箱,由「資料流程目的地」區域,選擇「一般檔案目的地」物件,拖曳到右邊的封裝設計師頁面上,命名為:一般檔案目的地_myUnPivot。

步驟02. 點選「取消樞紐」轉換:取消樞紐轉換_數量的綠色線路之「資料流程路徑」,拖曳到「一般檔案目的地」物件。

步驟03. 滑鼠雙擊此「一般檔案目的地」物件,設定相關的連線資料。

image

圖32:檢視_已經設定好_一般檔案目的地_myUnPivot

工作四:執行封裝

步驟01. 執行此封裝。

image

圖33:執行封裝

步驟02. 停止與結束執行,在每個「資料流程路徑」上,加入「資料檢視器」,再度執行此封裝。

image

圖34:檢視_資料來源_使用「資料檢視器」

image

圖35:取消樞紐轉換_使用「資料檢視器」

image

圖36:檢視_一般檔案目的地

結語

在本期中,介紹了使用SSIS 2008 的「樞紐轉換(Pivot transformation)」與「取消樞紐轉換(Unpivot transformation)」來執行將資料表值運算式變更為另一個資料表。

Tags:

SQL Server資料庫 | 陳俊宇Derrick Chen

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List