Microsoft SQL Server 2005 資料生命週期–資料分割

  • 發行項

作者: Erin Welker (Scalability Experts)

SQL Server 技術文件
技術檢閱者：Grant Dickinson、Dave Wickert、Len Wyatt 以及 Stuart Ozer
適用於：SQL Server 2005

本頁內容

簡介
資料生命週期概觀
關聯分割
Cube Partitioning
ETL 變更
參考資料
結論

簡介

成功的商務智慧 (Business Intelligence，BI) 應用程式不只要靠工具來實現，背後還需要有成功執行實作的相關知識基礎 (也就是最佳作法) 做為輔助。目前 Microsoft® 及多個夥伴正透過 Project REAL 進行合作，藉由以真實客戶實例所建立出的參考實作，一起攜手開發以 Microsoft SQL Server 2005 為基礎的 BI 應用程式最佳作法。這表示我們納入客戶資料，用以模擬客戶在部屬期間所要面對的相同問題。這些問題包括：

  • 結構描述設計–關聯式與 Analysis Services 兩者的結構描述設計。

  • 資料引出、轉換與載入 (ETL) 程序的實作。

  • 專為報告與互動分析設計與部署客戶前端系統。

  • 為生產而調整系統大小。

  • 持續不間斷的系統管理與維護，包含資料的遞增更新。

我們藉由實行真實的佈署情況，徹底瞭解運用工具的方式，而我目標在於因應大型企業在實際部署過程中會面臨的所有困擾。

本文件深入探討如何在關聯資料倉儲或是 Analysis Services Cube 內進行資料分割。除了提供一般的步驟方法，我們還包含特定程式碼區段以及成功與失敗經驗分享，希望能嘉惠讀者。期望這裡的資訊能夠為在 SQL Server 2005 上建立 BI 系統的規劃與實作方面提供幫助。

如需 Project REAL 概觀，請參閱《REAL 專案–技術概觀》(英文) 文章的連結。在進行Project REAL 的期間，許多文件、工具與範例也會相繼而生。如需最新資訊，請查閱網站：

https://www.microsoft.com/sql/bi/ProjectREAL

Project REAL 是 Microsoft 與數位夥伴在 BI 領域方面的合作專案。這些夥伴包括：Apollo Data Technologies、EMC、Intellinet、Panorama、Proclarity、Scalability Experts 及 Unisys。Project REAL 中商務案例以及來源資料組由 Barnes & Noble 慷慨提供。

附註：  此份白皮書為草稿。它包含我們從 SQL Server 2005 早期 Communicty Technology Preview (CTP) 建構經驗中所得來的建議最佳實作方法。此份白皮書的內容到發行當日為止仍正確無誤。此文件中所描述之產品功能或最佳作法未來有可能有所異動或改進。SQL Server 2005 尚在 Beta 階段，未來會有所變動的機率很大。

資料生命週期概觀

在任何資料倉庫實作中，開發出適合專案並且吻合商務需求的資料生命週期策略相當重要。「資料生命週期」可以涵蓋專案的許多面，這裡我們將它定義為用來引進新資料、幫助移除舊資料的資料庫管理處理作業。因此我們將「資料生命週期」分為三個重要的範疇：資料分割、移動老化資料至低價的磁碟以及剪接維度。以下為您簡單說明各個範疇所涵蓋的內容。

資料分割

資料分割是將大筆資料集分成較小而易管理的區塊的方法。在此白皮書中，我們將針對分割 SQL Server 資料表與 Analysis Services 量值群組討論。 我們會將重點放在水平分割上，水平分割會根據分割欄中的值分割資料列。這在大型資料倉儲實作中是常用的策略，主要是當成管理資料、Cube 載入與維護的方法。這也有益於資料查詢，因為 SQL Server 與 Analysis Services 皆提供方法來識別與查詢有關的分割區，並認定查詢使用分割欄位來篩選資料。

移動老化資料至低價的磁碟

資料倉儲名符其實自然地會包含相當大量的資料。所以一定要採用技術，管理在維護如此大量資料集所產生的資源成本。日期分割資料策略有辦法按照時間順序區，判斷哪些舊資料已經不再被經常使用。這樣一來，就可以將相關資料移動至較低價的磁碟。這些資料仍可供查詢，但是不是效能比較弱一點，就是可用性降低，或是兩者皆是。這是企業在取決資源費用、查詢效能與資料可用性之間的平衡點時所要做的取捨。

剪接維度

剪接維度這種資料管理特點有時候會被忽略。這通常較是可用性的問題，和磁碟或是資料庫管理較沒有關係。以 Barnes and Noble 為例，最大的兩個維度就是「項目」(產品) 與「客戶」維度。當產品停止供應且客戶數目下降時，企業用戶根本不需要看這方面資料，更何況要過目的資料維度內有數百萬個會員。

剪接維度的重點在於不應該用事實資料參考用不上的維度。雖說 Analysis Services 2005 其實有辦法將「未知」維度索引鍵儲存在 (一或多個維度沒有出現在來源資料的) 事實記錄中，但是在大部分的情況中這種辦法並不理想。而且，既然有事實記錄，有相符的維度記錄其實有其意義。

企業需要作決定，判斷維度何時可以從資料倉儲中安全地移除，例如「兩年沒有與該產品相關的銷售與庫存資料存在」。但是從事實資料表上刪除舊資料的原則應該與此決定相符。

維度剪接實作將會在另一份白皮書中詳盡說明。

Barnes and Noble 現況

在 2004 年，Barnes and Noble 導入了採用 Microsoft Data Warehouse Framework 的資料倉儲。重點放在客戶銷售、分店庫存與 DC (發貨中心) 庫存上面。目前有三年銷售、一年分店庫存與半年 DC 庫存。資料會被載入週資料表，並實施了一套資料表命名標準來指認資料表內包含的資料。目前採用 SQL Server 2005 Integrated Services (SSIS) 來載入 SQL Server 2000 關聯資料倉儲，並使用 Analysis Services 2000 載入許多 Cube。關聯式資料倉儲資料庫大小總計約為 2 TB。雖然資料在概念上已經過分割，但實作 SQL Server 2005 資料表分割可能會有許多益處，我們將在本文稍後為讀者介紹。

在決定分割策略之前，必須先收集好幾項需求。有些需求是與資料如何載入有關，有些則與根據特定分割間隔決定分割區大小有關。企業規定資料應被保存有效多久。Barnes and Noble 的最終目標在於保存五年以來的銷售歷史記錄與三年的庫存。他們尚未達成那樣的時間量，所以我們在 Project REAL 中建立自己的需求來示範「Sliding Window」實作的首展功能，這稍後將會在此白皮書中討論到。我們的下一步是要評估 Barnes and Noble 資料分割的優缺點，而且假設資料分割是值得進行的，取決最佳的資料分割方法。

關聯分割

關聯分割指得是將基本關聯資料儲存分割。在傳統資料倉儲中，此資料是以維度格式儲存，通常被稱為星狀結構描述或是雪花結構描述。我們的資料就是如此，所以不是維度資料表也不是事實資料表。大部分的維度資料表都相當小，只有數欄跟數列而已。Barnes and Noble 有兩個非常大的維度資料表：「項目」維度 (7 百萬列，5 GB) 及「客戶」維度 (接近 6 百萬列，1 GB) 資料表。第三大的維度就是「分店」維度，有超過 4,00 列，而且使用了少於 2 MB 的磁碟空間。相較於事實資料表，「客戶」與「項目」維度是很小的。

事實資料表

列數

使用空間 (資料與索引)

載入資料分割數

Tbl_Fact_Store_Sales

1,366,052,628

306 GB

157

Tbl_Fact_Store_Inventory

8,450,555,562

1037 GB

53

Tbl_Fact_DC_Inventory

51,387,065

4 GB

18

Total

 

1347 GB

 

為了使這些資料表在裝載、備份/還原與索引維護時便於管理，最佳的作法是將它們水平分割成較小的資料表，也就是我們一直提到的「資料分割」方法。

資料分割的好處

好處在上述中稍微有提到。這裡再詳盡說明一下，所有的大型資料表管理操作在資料表被分割之後，都可以在更細微的層級上執行。資料表備份可以再進一步細分到分割資料表上。SQL Server 2005 中有一些新的功能可以使這些作業更方邊進行。當資料群組是唯讀時，SQL Server 將不再需要把交易記錄檔備份在資料群組還原案例中。在此例中，最新的資料分割可以放置在舊的靜態分割區之外的資料群組上。靜態資料分割區可以放置在它們自己的唯讀資料群組中，只需要維護與備份一次即可。到目前為止，只有動態資料分割區需要維護。其他像是索引維護等等的操作，在資料分割狀況中也較簡單了。這可能是主動管理多兆位元組資料庫與管理小於 100 GB 之資料分割區的差別。

另一個好處就是查詢效能，在資料倉儲環境中特別明顯。無論查詢是由 Analysis Services 在 Cube 處理中發出，或是一般使用者直接查詢關聯資料倉儲時，好處更是突出。以 Cube 處理為例，當目的地 Cube 使用與關聯來源相同的分割欄時，效能就會提升。當處理資料分割 Cube 時，Analysis Services 會發行一個只限對該分割區內所含資料的查詢。例如，假設目的地分割區包含最後一天是 2005/01/01 的那一週的資料，將會向 SQL Server 查詢僅限該週的資料。這樣能大幅縮減掃描的分割區數。

由使用者直接對照關聯資料倉儲所發出的查詢通常會有日期元件。一般查詢的目的在於比較目前時期與去年同期的銷售成績。這種情況下，SQL Server 經常可以限制參考的資料數量，藉此提升查詢效能。這也適用於 Analysis Services 的查詢。

SQL Server 2000 中的選項

SQL Server 2000 只有單一個資料分割選項「資料分割檢視」。如要建立一個資料分割檢視，您只需要定義一個能列出所有參與檢視資料表的檢視，然後使用「UNION ALL」陳述式來串聯它們在一起。為了讓查詢最佳化工具隔離出與特定查詢有關的分割區，我們得使用信任條件約束來報告哪些分割區包含哪些資料。「信任」條件約束指的是使用 [WITH CHECK] 選項建立的條件約束。如果建立條件約束未使用此選項，或是如果資料已大量載入/大量複製 (BCP) 至資料表，但每個載入過程中沒有 CHECK_CONSTRAINTS 提示存在，那麼資料表中就可能會有違反條件約束的資料。SQL Server 並不信任這些條件約束，所以而且這些約束條件的許多最佳化與功能益處將無法實現。

SQL Server 2005 中的選項

SQL Server 2005 提供一個資料分割的新選項–分割資料表與索引。分割資料表以在儘量不影響同一資料表其他使用者的情況下，載入與管理水平資料分割。關於分割資料表的詳盡討論已在其他論壇中涵蓋過，所以除了講述一些高階概念之外，其餘的將不在此贅述。關於其他資訊的參考資料可以在本文件的結尾找到。

資料表分割可讓資料表或是索引的每個邏輯獨特的區段被當成分別的實體。這些區段 (分割區) 可以在資料表之外被載入，所以載入過程不會影響該表的使用者。當分割區載入完成時，它會被「切換入」分割資料表。有幾項條件必須準備就緒才能成功的進行分割區轉換，最常見的條件為：

  • 分割資料表的架構必須與被切換入的資料表相同。這包含欄位名稱、資料類型、空值屬性、定序、有效位數與 Primary Key 條件約束。

  • 在分割資料表上的任何索引都必須出現在將被切換入的資料表上，而且除了名稱之外，所有的索引屬性都必須相符 (例如索引欄位、叢集屬性與特殊屬性)。

  • 目的地分割區必須為空白。

  • 來源資料表與目的地分割區必須位於相同的資料群組中。

  • 來源資料表必須要定義信任的檢查條件約束，且這個約束必須與目的地分割區相容。

再度提醒您，這是需求清單的一部份。您可以在《SQL Server 線上叢書》(SQL Server Books Online) 中找到更明確的限制。

有幾個名詞是專門用來描述 SQL Server 如何實作分割資料表的。這些詞彙將在以下為您簡短介紹，如需更詳盡的解說，請參閱《SQL Server 線上叢書》與 Kimberly Tripp 所著關於「分割資料表」的白皮書中 (請參閱此文件最後的參考資料)：

  • 分割函式

    「分割函式」是一個定義上層或是下層邊界的實體資料庫物件。

  • 分割配置

    「分割配置」是以「分割函式」為基礎的實體資料庫物件。「分割配置」替「分割函式」所定義的每個分割區定義出在磁碟上的位置。

  • 對齊

    如果分割資料表與索引是以相同的分割函式為基礎，理當已經對齊，不管明確指定與否。

  • 共置同區

    如果分割資料表與索引共用相等的分割函式 (即它們是對齊的) 與相等的分割配置，它們應該會共置在同區。因此它們的分割邊界是相同的，而與這些邊界對應的資料會位於相同的檔案群組中。

  • 左邊範圍/右邊範圍

    這或許是資料表分割中較為令人困惑的觀念之一。分割函式的定義不是 RANGE LEFT 就是 RANGE RIGHT。您可以用這種方法幫助記憶，RANGE LEFT 的函式表示與邊界相關之分割資料是在邊界左邊；RANGE RIGHT 表示與邊界相關之分割資料是在邊界右邊。所以如果邊界是「2005/01/01」，而分割函式明文為 RANGE LEFT，則「2005/01/01」就是上層邊界，而低於邊界的資料會在邊界的左邊。

  • 合併

    合併分割區將兩個分割區變成一個。「合併」的目的在於移除分割區。

  • 分割

    分割一個分割區就是將一個分割區變成兩個。「分割」的目的在於新增一個分割區。

比較分割資料表與分割檢視

分割檢視在 SQL Server 2005 中仍可使用，而且依然是在資料倉儲中分割資料的不錯選擇。大部份情況而言，分割資料表會比較容易管理。下表提供兩種方式優缺點的快速檢查表：

[表 1] SQL Server 2005 分割資料表 vs. 分割檢視

功能

分割資料表

分割檢視

資料表維護

將資料表當作單一實體管理。

每個參與的資料表都是其各自必須要有中繼資料變更的實體。

編製索引

每個分割區都必須要有相同的索引。

每個資料表可以有各自的索引策略。

實作

兩者在實作困難度上都大約相等。當有問題產生時，「分割資料表」比較容易產生錯誤。

有些隱藏因素會造成「分割檢視」無法如預期運作。一個常見的問題就是不用檢查目前的值就建立檢查條件約束。雖可以成功地實作「分割檢視」，但是不知為何將無法正確地最佳化。

編譯時間

由於所有的分割區都以相同的方式編製索引，最佳化工具可以針對每個分割區使用相同的執行計畫，而編譯時間將會更簡短。

由於分割區可以有不同的索引，最佳化工具必須評估每個資料表 (分割區) 最佳的執行計畫。在分割檢視時中有太多資料表時，會增加編譯時間。

載入

分割區可以在外部載入，降低對該資料表目前使用者的影響。

資料表可以在外部載入，降低對該檢視目前使用者的影響。

切換入新資料

這是快速且自然進入佇列的中繼資料操作。

這是中繼資料操作，但是 ALTER VIEW 陳述可能會無限期等待 SCHEMA 鎖定。

可更新性

建立出的分割資料表必須符合一些規則才能更新，此外並沒有其他特殊規定。分割資料表可以有識別欄位，不需要 Primary Keys 等其他資料。

有幾個限制，像是在參與資料表上沒有識別及 Primary Key 的存在，都會使得更新分割檢視造成阻礙。這通常表示必須要直接更新基礎資料表，並使 INSERT 與 UPDATE 的編碼複雜化。

備份/還原

根據檔案群組實作資料表的方式，分割區可以一併或分別進行備份/還原。

請注意： 除非檔案群組標示為「唯讀」，否則還原檔案群組必須從備份點重新套用交易記錄。

根據檔案群組實作資料表的方式，形成分割檢視的個別資料表可以一併或分別進行備份/還原。

分割檢視資料表可以存放在不同的資料庫，這樣就能允許不同的資料庫備份不同的分割區。

請注意：除非檔案群組標示為「唯讀」，否則還原檔案群組必須從備份點重新套用交易記錄。

資料庫的交互影響

分割資料表上的所有分割區必須要存放於相同的資料庫中。

在分割檢視中整合的資料表可以存放在不同的資料庫中。這樣有助於進行歷史分割區的備份與還原。

查詢平行處理

平行查詢計畫是以個別分隔區為單位。 如果查詢只需要接觸單一分割區，將不會使用平行處理來存取資料表。

在平行查詢存取中，分割檢視中的每個資料表都會被分別看待。如果查詢只需要接觸單一資料表，將會使用平行處理來存取該資料表。

大量載入

分割資料表可以直接成為大量插入/BCP 操作的目標。

分割檢視無法成為大量插入/BCP 操作的目標，必須將檢視內的個別資料表當成目標。

實作決定

Barnes and Noble 在 SQL Server 2000 中並沒有實作分割檢視，因為這麼作會需要太大量的編譯時間 (通常長達 30 秒)。目前有三年的銷售資料，關聯 156 個資料表分割區。由於跨整個分割檢視的查詢需要檢查每個基礎資料表來評估執行計畫，所以分割檢視變得無法使用。除了編譯時間之外，選擇分割資料表而非分割檢視最重要的原因在於：前者資料表維護更簡易，而且更便於跨多個分割區執行 INSERT、UPDATE 與 DELETE 。

在選擇資料分割方法之後，我們接下來得要決定如何分割欄位與最佳日期間隔。如果以日期欄位分割欄位，對於資料倉儲中分割資料有好幾種優點。在我們的案例中，欄位並不真的是日期時間資料類型，但是欄位中所包含的資料參照的日期 (CCYYMMDD) 已經儲存為整數。

資料分割設計決策

我們下第一個設計決策就是資料分割索引鍵。銷售事實資料表有一個 Transact_Date 欄位屬於 SQL Server 日期時間資料類型。乍看之下，這似乎是最理想的分割欄位。然而這個欄位在兩個庫存事實資料表上都沒有被定義。相反地「Date」欄位是以 SQL Server int 資料類型 (以 CCYYMMDD 的格式) 實作來降低空間配置。這個名為 SK_Date_ID 的欄位在所有三個事實資料表中都一致，所以選它作分割索引鍵的欄位。選擇此欄位的一個問題，由於它是真正的日期時間資料類型，故會傾向使用 Transact_Date 欄位來查詢銷售事實資料表。請記住，如果查詢篩選分割欄位，查詢最佳化工具只能靠限制掃瞄分割區來解決查詢。

我們下一個設計決策是資料分割間隔。目前 Barnes and Noble 資料以週資料表的方式儲存，因為庫存是按週載入的 (每週一開始會建立快照，而且每天更新)。這也會將分割區大小保持在每個分割區約 25 GB，適於管理。如果採用每月資料分割配置就比較難管理，因為每個分割區大小會達 100 GB。我們為「銷售」選擇相同的資料分割策略，藉此獲得分割資料表對齊的好處，這將會在稍後討論。

或許我們最大的考量在於分割區會如何部置在磁碟上。我們應該將使用一或多個檔案，將分割區對應到一或多個檔案群組嗎？如需瞭解使用兩個高層級策略進行分割區對應的優缺點，請參閱《在 Microsoft SQL Server 中分割關聯資料倉儲之策略》 (英文)，您可以在文末的參考資料中找到這份白皮書的連結。我們決定最好用單一的基礎檔，將每個分割區案對應到其各自的檔案群組。如此一來，我們可以控制分割區部置到磁碟上的方式。這種作法是否合用，其實說法不一。其中一種說法是可以藉著磁碟子系統，以個別檔案群組進行等量作業。實作個別檔案群組有三個主要理由：

  1. 唯讀屬性可以在檔案群組層級設定。當分割區存在超過 8 星期，也就是不會再有事實資料表插入的情況時，我們可以啟用唯讀屬性來啟動每個備份/還原狀況 (SQL Server 2005 中的新功能) 並且減少鎖定的情況。

  2. 釋出案例可能在磁碟層級執行。為了成全這項作業，必須將分割區在其各自的磁碟檔案中隔離起來。如果分割區已經等量放置於檔案群組的數個磁碟中，這個方法就不可行。

  3. 為了將舊分割區釋出到低價磁碟，我們需要至少兩個檔案群組，一個用在作用中磁碟陣列，一個用在非作用中磁碟陣列。這樣能防止我們將所有的東西都對應到單一檔案群組上。

實作分割資料表至 Barnes and Noble 環境中有兩個重點。首先，必須將既有的資料表盡數移動到我們建立的分割資料表中。接著需要修改目前的 ETL 流程，每週建立新的分割區，並且將新資料載入到這些分割區中。下文將進一步討論這些重點。

分割索引？

進行索引分割前，您必須作幾項決定。第一，究竟要不要將分割資料表上的索引分割。如果決定要進行，就必須決定索引是否要與基底資料表對齊。最後，我們需要決定是否要將索引與基底資料表共置於同個地方。分割索引是是比較簡單的決定。其實資料分割的整個概念主要考量在於是否便於管理。由於我們的資料只有一小部份是動態的，其實沒有必要管理大型索引，因為其中大部分資料不會變更。大部分的索引維護是有分割性的，因此我們可以在索引的子集而非整個索引上執行所需的維護。

除了叢集索引之外，其實您可以採用不同於基礎機底資料表的方式來分割索引。而且這在定期交換資料進出的狀況下是比較理想的作法。如果索引遵循相同的分割函式，因為資料是以相同方式分割，就可以更有效率地移入移出資料。

最後的考量重點在於應該將索引放在哪裡。依預設，叢集索引會遵循基底資料表的資料分割配置，也就決定分割區應該置放在磁碟上的何處。非叢集索引應該可以建立在個別的分割配置上，但是如果相關資料保證會在相同的檔案群組中，那麼 SQL Server 就可以更順利的進行平行處理。如需詳細資料，請參閱 Kimberly Tripp 所著的《SQL Server 2005 中的資料分割》(英文) 白皮書。

將目前的資料表轉換至分割資料表

要轉換現有資料表，首先得看看 Barnes and Noble 目前的環境。目前維度與事實資料表儲存於兩個資料庫中，一個是存放庫存事實資料表 (分店與發貨中心庫存) 的資料庫，另一個是存放銷售事實資料表與維度度資料表的資料庫。Project REAL 將資料遮罩過程中，將這些資料合併至一個單一資料庫 REAL_Warehouse 中。為了便於練習資料分割，我們只將重點放在處理事實資料表。

舊的銷售事實資料表將會在每週以實體 SQL Server 資料表呈現。每個資料表遵循命名慣例「Tbl_Fact_Store_Sales_WE_ccyy_mm_dd」，其中日期指的是該週結束 (星期六) 當天的日期。我們有一個範例資料表「Tbl_Fact_Store_Sales_WE_2003_12_27」，包含了從 2003/12/21 到 2003/12/27 的資料。

關於庫存事實資料表的相關資訊與上述類似。庫存事實資料表其實有兩份，一份是 DC (發貨中心) 庫存，一份是分店庫存。這些資料表的命名規則分別是「Tbl_Fact_DC_Inventory_WE_ccyy_mm_dd」與「Tbl_Fact_Store_Inventory_WE_ccyy_mm_dd」。由於這些資料表的中繼資料差異相當大，因此無法將它們合併。我們的分割欄位 SK_Date_ID 就像像是銷售資料表，具有相同的資料類型。

我們能夠在三個事實資料表中都選擇相同的分割欄位 SK_Date_ID。因為資料類型一致，便於對齊資料進行連結。現在我們要注意日期的整數表示方式。例如「2004 年 12 月 25 日」變成「20041225」。由於現有實體來源資料表是以每週遞增為基礎，所以對於分割方式的轉變相當有幫助。分割資料表上的一個分割區等於一個實體來源資料表。以下為讀者介紹載入每個邏輯事實資料表的各個步驟：

  • 建立所有檔案與檔案群組

  • 建立分割函數 - 藉此定義特定分割資料表的所有邊界。以下為 CREATE PARTITION FUNCTION 陳述式的子集：

    CREATE PARTITION FUNCTION pf_Range_Fact(int)
AS 
RANGE LEFT FOR VALUES (
  20020105,
  20020112,
  20020119,
  .
.
.
  20041231)

  • 建立資料分割配置 - 藉此定義每個分割區在磁碟上的部置方式。我們決定將庫存與銷售事實資料表對齊，但是儲存體並沒有對齊。這樣有助於連接分隔的分割區，但是將 I/O 分散到實體儲存體中。這裡所說的「對齊」代表分割的資料表共用相等的分割函式。雖然這不表示分割函式必須實際上相同，但是在邊界定義、分割索引鍵資料類型及分割區數量上必須相符。儲存體對齊表示兩個資料表上的一特定分割區會置放在相同的檔案群組中。

  • 建立分割資料表 - 這看起來就像是任何其他的 CREATE TABLE 陳述式，但是指明資料表要「放置」在上面指明的分割配置上。

  • 對每個來源資料表而言，如 dbo.Tbl_Store_Inventory_WE_2004_12_25：

    • SELECT INTO 將會選擇一個要切換入分割資料表的暫時資料表。

    • 新增一個與目的地分割區相呼應的 Check 條件約束 (WITH CHECK)。

    • 使用 $partition function:SELECT @PartitionNum = $partition.FactRangePFN(20041225) 來決定目的地分割區

    • SWITCH 將會把暫時資料表切換至目的地分割區

  • 建立索引

附註：如果舊的銷售以及/或是庫存被移動到相同資料庫的另一個分割資料表中，並且已經置放於想要的檔案群組上，我們可以略過 SELECT INTO 步驟，然後在建立檢查條件約束與變更分割欄位為 NOT NULL 之後，將資料表切換到適當的分割區中。由於這不是本例的狀況，所以將資料表切換到分割資料表之前，我們需要將來源資料表複製到理想資料群組內的另一個資料表中。

CREATE PARTITION FUNCTION 與 CREATE PARTITION SCHEME 命令是自動產生的。這樣就免去某週輸入資料的麻煩和可能的錯誤，而且能更靈活地定義分割邊界，確保分割配置與分割函數邊界相符。這個程式碼將會在 Project REAL 記錄中重複使用。

剛開始建立分割資料表可能要花許多的時間。本案例中，基本上我們複製了 1.5 TB 的資料，所面對的挑戰就是讓這個過程盡量便利。我們立刻就將 INSERT INTO 排除在外，以避免登入與鎖定造成的負擔。BCP 會要求先從磁碟上將該資料複製出來，然後使用 BULK INSERT 將資料整筆插回暫時資料表。這種方式比起 SELECT INTO 更能掌控檔案複製的方式，但其實執行 SELECT INTO 選項比光靠 BCP 讀出資料還要快。事實證明，SELECT INTO 的速度比 BCP out/BULK INSERT 的組合快八倍以上，即使 BCP 處於未登錄和最低鎖定的狀態，速度上仍是無法與其相比。

然而，SELECT INTO 的確有一項問題。它無法替資料表指出在過程中所建立的檔案群組。這是很大的問題，因為這個此暫時資料表的檔案群組必須要與其目的地分割區檔案群組相符。為了確保暫時資料表的檔案群組定義正確，資料庫的預設檔案群組會在執行 SELECT INTO 之前變更，以確保資料表建立在指定的檔案群組中。這樣一來，卻大幅限制我們平行處理資料表的能力，因為所有的並行 SELECT INTO 都必須使用相同的檔案群組 (一次只有一個預設資料庫檔案群組)！這也會引進磁碟作用點，而拖累平行處理。由此可看出，具有序列性質的 SELECT INTO 基本上還是比 BCP/BULK INSERT 更理想，因此我們取前者進行。

由於來源資料表並不包含納入資料當天採用的條件約束，因此來源分割欄位上必須有信任的 Check 條件約束，此欄位必須與目標分割邊界一致，才能順利切換。這個條件約束是在載入過程中，待 SELECT INTO 完成之後加入的。

另外，分割欄位 SK_Date_ID 在三個資料表中都會被定義為允許 Null。我們原本可以在我們的分割函數中包含一個邊界，指定將所有包含 SK_Date_ID = NULL 的列都去除。但因為我們的分割策略是以日期為基準時，所以這種方法派不上用場。此外，SK_Date_ID 等於 NULL 亦會違反我們的 CHECK 條件約束。我們已經與 Barnes and Noble 確認 SK_Date_ID 絕不能為 Null，並已在整個結構描述套用這項商務規則。

以下詳細分析其中一個較大的庫存事實資料表：

[表 2] 複製資料表分割詳細分析

步驟

經過時間 (分：秒)

佔全部的 %

SELECT INTO

7:33

74%

ALTER SK_Date_ID 為 Not Null

5:51

 

ALTER ADD Check 條件約束

2:42

26%

SWITCH 至分割資料表

0:00

0%

總計

10:15

 

請注意，我們並沒有使用 ALTER TABLE 陳述式將 NOT NULL 條件約束實作於目標資料表的 SK_Date_ID 欄位上。其實我們能夠在 SELECT INTO 陳述式過程中使用 ISNULL 功能來實作這點。為了闡釋這一點，請參考以下的陳述式：

SELECT <Column 1>, 
<Column 2>, 
isnull([SK_Date_ID], -1) as [SK_Date_ID],
.
.
.
<Column x>
INTO dbo.Tmp_NewPartition FROM Tbl_Fact_Store_Sales_WE_2005_01_01

在實作這個技巧之前，新增 NOT NULL 條件約束的 ALTER TABLE 陳述式在每個資料表上花費了接近 6 分鐘，能替此分割區在跨三個分割資料表間進行複製時省下大量的時間。單單就分店庫存事實資料表而言，使用此技巧預估能節省 5 小時以上的時間！

測試分割資料表

在載入新的分割資料表之後，首要就是檢視結果。乍看之下，所有的東西似乎都不太明顯。在初次檢閱時，似乎沒有方法可區分分割與非分割資料表，幸好資料庫中的物件很少。來源資料庫儲存有 229 個事實資料表；有分割資料表的資料庫含有 3 個資料表。我們可以藉由檢視 SQL Server Management Studio 中的屬性，更詳盡檢查 3 個事實資料表中的其中某個資料表。以下展現 Tbl_Fact_Store_Inventory 資料表的屬性：

Dd159898.realpart01(zh-tw,TechNet.10).gif

[圖 1] 分割資料表屬性

從列數很明顯地可以看出我們的載入作業成功。可以運用額外的驗證，來檢查來源資料表的列數總數是否等於此對話方塊中指出的列數。「儲存體」區段顯示資料表已被分割，而且指出分割區對應的「分割配置」。

可以透過查詢「動態管理檢視」 (DMV) ，更明確指明所有分割區上散發的列。然而，這項建議背後的代價不小，因為資料表中的每個分割都會實際執行計數，但是可以藉著以下的查詢瞭解分割資料表與列分散的情形。

SELECT $partition.pf_Range_Fact(o.SK_Date_ID) AS [Partition Number]
      , min(o.SK_Date_ID) AS [Min Date]
      , max(o.SK_Date_ID) AS [Max Date]
      , count(*) AS [Rows In Partition]
FROM dbo.Tbl_Fact_Store_Sales AS o
GROUP BY $partition.pf_Range_Fact(o.SK_Date_ID)
ORDER BY [Partition Number]

它為銷售事實資料表製造了以下的資訊 (為求簡便，這裡只包含結果集中的子集)。

分割區 #

最小日期索引鍵

最大日期索引鍵

分割區中列數

…

…

…

…

140

20040829

20040904

8061536

141

20040905

20040911

8308355

142

20040912

20040918

8044390

143

20040919

20040925

7824844

144

20040926

20041002

7864007

145

20041003

20041009

7853734

146

20041010

20041016

8056497

147

20041017

20041023

8017784

148

20041024

20041030

7684242

149

20041031

20041106

7924918

150

20041107

20041113

8845731

151

20041114

20041120

8963072

152

20041121

20041127

9361857

153

20041128

20041204

11201851

154

20041205

20041211

13974601

155

20041212

20041218

17549392

156

20041219

20041225

18736647

157

20041226

20041231

12016107

遞增處理

一但分割資料表就緒，就需要需要持續不斷的維護，包括在每週初為每個分割資料表建立新的分割區。除此之外，我們選擇實作「Sliding Window」，並且從高價的磁碟中將舊資料遷移至較低價的磁碟。

Sliding Window

「Sliding Window」實作可以在當新資料引入時，修改分割資料表中有效的日期範圍，並且移除舊分割區，因為歷史資料將不再被需要。分割資料表中的有效資料數完全取決於商務需求，這在每個實作中都不同。這些需求可以規定銷售的連續 3 年記錄必須永遠有效。這樣當新銷售記錄加入的同時，就可以排除歷史銷售記錄。為了詳加說明 Sliding Window 的實作，並且提供範例介紹如何在 SQL Server 2005 分割資料表中完成新增與移除分割區，我們假設以下情況：

  1. 替最後一天是 2005/01/01 的那一週將一些資訊載入到新的外部事實資料表之後，我們要讓新資訊在分割資料表中成為可用資訊。

    Dd159898.realpart02(zh-tw,TechNet.10).gif

    [圖 2]

  2. 首先我們「分割」分割函數中的最後一個分割區 (也就是 2004/12/25 之後的所有東西)，產生一個新邊界，涵蓋 2004/12/25 之後到 2005/01/01 為止的所有東西。

    Dd159898.realpart03(zh-tw,TechNet.10).gif

    [圖 3]

  3. 現在拿新建立的空白分割區與步驟 1 之前所載入的外部資料表切換。這會造成一個空白的外部資料表，我們會將它刪除。

    Dd159898.realpart04(zh-tw,TechNet.10).gif

    [圖 4]

  4. 我們現在的分割資料表即包含新資料。

    Dd159898.realpart05(zh-tw,TechNet.10).gif

    [圖 5]

  5. 接下來我們想要移除最舊分割區中的資料。先開始建立一個空白資料表，其資料表結構描述類似分割資料表 (包含索引)。

    Dd159898.realpart06(zh-tw,TechNet.10).gif

    [圖 6]

  6. 將分割資料表的第一個分割區與剛剛建立的外部資料表切換。

    Dd159898.realpart07(zh-tw,TechNet.10).gif

    [圖 7]

  7. 現在將包含所有小於或是等於 2002/01/05 資料的第一個分割區與第二個分割區合併。這樣第一個分割區就包含所有小於或是等於 2002/01/12 的資料了。由於以 2002/01/05 結束的該週資料目前放在分割資料表外面，我們可以將它儲存在某處，然後從 SQL Server 刪除資料表。

Dd159898.realpart08(zh-tw,TechNet.10).gif

[圖 8]

Sliding Window 實作通常代表藉由分割/切換與合併/切換動作，同時新增及移除分割區。然而新增及移除分割區並不直接相互依存，而且當然可以分別產生。如果出現不同的 Sliding Window，可能表示舊資料宜除頻率與新資料新增頻繁率不同。因此就需要例如整整三年的會計年度資料都必須有效的的商務需求。在此情況下，新會計年度是逐漸建立的，一週復一週或是一月復一月，直到達到整個會計年度。然後舊會計年度資料會一次全數清除。種種情況都可以透過實作「Project REAL」的 Sliding Window 來達成。不同之處在於合併/切換作業該如何且在何時執行。

Barnes and Noble 尚未在「銷售」或是「庫存」中累積到可以實施資料保存的資料量。因此我們降低了刪除週期，來測試 Sliding Window 實作。此過程是參數導向的，而且可簡易修改來調整計時。

老化資料

在最快速有效的磁碟子系統上維護最常用的資料，並且在較低速且/或較不有效的低價磁碟上裝載較不常用到的資料。這個概念經常被談論，但不常實踐。這特別與資料倉儲息息相關，因為資料倉儲有非常高的磁碟儲存需求，而且經常要在線上保存多年的資料。一般認為，通常每一兩年資料上就會出現的活動作用點。然而，這並不表示舊資料就不需要因偶爾查詢或是法務需求而有效。為了管理維護如此架構的耗費，我們在 Project REAL 中實作了一個機制，在遞增過程中將分割區從高價移動到低價的磁碟子系統，並透過新增及刪除分割區的漸進過程中逐漸實現這點。

理論上而言，移動到低價磁碟似乎有點複雜，但實際上相當簡單。您只要記住一點，我們事實上是將資料從一個磁碟子系統移動到另一個。這可能是非常昂貴的作業，尤其是當移動資料倉儲中找到的資料數量時更耗費資源。我們選擇移動每星期中的一個分割區，來降低任何時候的資料移動量。

現在我們來討論一下所牽涉到的複雜度。「分割配置」是用來規定分割函數中所定義的分割區在磁碟上的部置方式。一但分割配置已建立，就無法再重新命名或修改，除了指明新分割區的位置外。藉由分割與合併分割函數中的分割區，可以新增或移除分割區，但是對現有分割區存放的位置不會有影響。因此，為了「移動」分割區，我們必須建立新的分割配置，另行規定磁碟上分割區的部置方式。

Dd159898.realpart09(zh-tw,TechNet.10).gif

[圖 9] 將舊資料移動至低價磁碟

以下介紹我們搬動舊資料所採取的步驟。這個過程乍看之下稍嫌複雜，尤其是我們的 Project REAL 中「銷售」分割資料表有 150 多個分割區！但其實執行起來相當快速，因為除了資料移動的動作，上述所有步驟皆為中繼資料步驟。

  1. 依照現在分割函式完全複製現有分割配置建立新的分割配置，除了移動一或數個分割區之外。分割配置定義中的移動分割邊界將會指向低價磁碟上的一個檔案群組。

  2. 在新分割配置上建立新的分割資料表。

  3. 逐一掃過每個分割區，並從舊分割區切換至新分割區中的同一分割區號碼 (兩個分割資料表皆使用相同的分割函式)，直到達到移動分割區。 陰影方塊指的是填入的分割區，而白色方塊指的是空白的分割區。

    Dd159898.realpart10(zh-tw,TechNet.10).gif

    [圖 10]

  4. 移動的分割區需要確切地複製，因為資料需要移動。這可以藉由直接從舊分割區中複製資料到新分割區來達成，方法是使用 INSERT INTO..SELECT，我們也可以用 SELECT INTO，將位於同一檔案群組中的外部資料表當作目的地分割區。就如先前首度載入時，SELECT INTO 在執行上遠比 INSERT INTO 有效率，因此我們選擇前者來進行。

    Dd159898.realpart11(zh-tw,TechNet.10).gif

    [圖 11]

  5. 使用 SELECT INTO 方法時，需要將外部資料表切換到此表在新分割資料表中的最終目的地。

    Dd159898.realpart12(zh-tw,TechNet.10).gif

    [圖 12]

  6. 現在我們逐一處理目前分割配置中剩下的分割區，並且如同步驟 3，將分割區切換到新的分割資料表。

    Dd159898.realpart13(zh-tw,TechNet.10).gif

    [圖 13]

  7. 我們可以藉由刪除舊分割資料表與分割配置來進行清理，並且將新分割資料表重新命名為原始分割資料表的名稱。

    Dd159898.realpart14(zh-tw,TechNet.10).gif

    [圖 14]

編碼範例

以下進一步探討前面略述實作管理舊資料的過程。

  1. 建立新分割配置

    新分割配置是現有分割區的完全複製版本，唯一不同之處只有定義何時該「老化」分割區的日期邊界。名稱字尾加上目前該週結束的日期，就能使它成為唯一的名稱。由於分割配置名稱無法變更，所以此名稱將會一直保留到下一次的老化過程，由新分割配置取代這個名稱。

    在典型的每週建立新分割區程序中，只有一個分割區會被從「使用中」檔案群組移動到「老化」檔案群組。中繼資料上會建立一個資料指標，用來跨過邊界，並且為新分割配置建立指令碼。通常在任何 ETL 程序中不建議使用資料指標以免大幅拖累效能，但在此案例中，這種指標只是用來逐一處理少數的中繼資料物件，所以是例外。請注意，除了要從「使用中」移動到「老化」的分割區之外，所有檔案群組在兩個分割配置間必須保持一致。它的程式碼如下：

    DECLARE CurrentSchemePartitions CURSOR FOR 
SELECT FileGroupName, Boundary 
FROM dbo.fn_Get_FileGroupsByPartitionBoundary(@psOld_Scheme_Name)
ORDER BY Boundary ASC 

OPEN CurrentSchemePartitions

SET @psSQL_Text = 'CREATE PARTITION SCHEME ' + @psNew_Scheme_Name + '  
AS PARTITION pf_Range_Fact 
TO ('

  FETCH NEXT FROM CurrentSchemePartitions INTO @psFG_Name, @pnBoundary_Date

  WHILE @@FETCH_STATUS = 0
  BEGIN
      -- If the partition boundary is less than the beginning date, use the file   
      -- group for the new partition to move into the Agedd area
      IF @pnBoundary_Date < CONVERT(int, CONVERT(char(10), @pdLogical_Date, 112))
      BEGIN
  SET @psSQL_Text = @psSQL_Text + @psFG_Name + ', '   
        SET @psAged_FG_Name = @psFG_Name
      END
      -- If the partition boundary is less than or equal to the Aged date and was 
      -- previously in one of the "Current" filegroups, script the partition to 
      -- the Aged filegroup that will be relinquished when the old partition drops  
      -- off.
      ELSE IF @pnBoundary_Date <= CONVERT(int,CONVERT(char(10),@pdAged_Date, 112)) 
        AND @psFG_Name LIKE @psActive_FG_Prefix + '%'
      BEGIN
        SET @psSQL_Text = @psSQL_Text + @psAged_FG_Name + ', '
  SET @psActive_FG_Name = @psFG_Name
      END
      ELSE   
        SET @psSQL_Text = @psSQL_Text + @psFG_Name + ', '

      FETCH NEXT FROM CurrentSchemePartitions INTO @psFG_Name, @pnBoundary_Date

  END

  -- !!When we are done, we need to add a additional partition to the scheme. 
  -- This is for the right-most partition, which was not represented in our 
  -- cursor query because it is not in the partitioning function. Since we are 
  -- left partitioning, data will ever be in this final partition.
  SET @psSQL_Text = @psSQL_Text + '[Primary])'
  EXEC (@psSQL_Text)

  CLOSE CurrentSchemePartitions
  DEALLOCATE CurrentSchemePartitions

  2. 建立新分割資料表

    新分割資料表定義看起來與舊的完全一樣，唯一的差異在於新的分割資料表會被定義在我們剛建立的新分割配置上。我們也需要建立索引，以便分割區可以直接從舊分割資料表切換到新資料表。

  3. 這時會建立一個與與步驟 1 中完全相同的新資料指標。當迴圈跑過這個資料指標時，就能直接把每個分割區切換入到新資料表中的相等分割區。由於新舊分割區配置皆以相同的分割函式為基礎，來源與目的地分割區數目會一樣。

    SELECT @PartitionNum = $partition.pf_Range_Fact(@pnBoundary)
SET @psSQL_Text = 'ALTER TABLE ' + @psPartitioned_Table_Name + 
' SWITCH PARTITION ' + CONVERT(varchar(3), @PartitionNum) + ' TO ' 
+ @psNew_Partitioned_Table_Name + ' PARTITION ' + 
CONVERT(varchar(3), @PartitionNum)
EXEC (@psSQL_Text)

  4. 如果分割區正在移動，我們需要將資料複製到有相同構造的新外部資料表。前面所述，最有效率的方法就是在建立 Check 條件約束與索引之後，執行 SELECT INTO 作業。然後我們將外部資料表 (在建立索引之後，現為分割資料表) 切換到步驟 2 中所建立的分割資料表。這些步驟的程式碼片段看起來如下：

    SET @pnAged_Boundary_Date = @pnBoundary 

-- Get the name of the Aged FG that the moving partition will reside on by 
-- looking it up on the new partition scheme
SELECT @psNew_FG_Name = dbo.fn_Get_FileGroupForBoundary(@psNew_Scheme_Name, 
@pnBoundary)

-- Change the default filegroup to the filegroup the moving partition will 
-- reside on so the SELECT INTO will create the table on the correct filegroup.
EXEC etl.up_SetDefaultFG @pnBoundary, @psNew_Scheme_Name

IF (SELECT COUNT(*) FROM sys.tables WHERE name = 'MovingPartition') > 0
DROP TABLE MovingPartition

-- Copy the data from the moving partition on the old table to a temporary 
-- partition on the new filegroup.
SELECT @PartitionNum = $partition.pf_Range_Fact(@pnBoundary)
SET @psSQL_Text = 'SELECT * INTO MovingPartition FROM ' + 
@psPartitioned_Table_Name + 'WHERE $partition.pf_Range_Fact(SK_Date_ID) = ' 
+ CONVERT(varchar(4), @PartitionNum)
EXEC (@psSQL_Text)

-- Since constraints and indexes were lost during the SELECT INTO, create them 
-- to match those on the destination partitioned table.
SET @psBoundary = @pnBoundary
SET @pdWeek_Begin = SUBSTRING(@psBoundary, 5, 2) + '/' +
SUBSTRING(@psBoundary, 7, 2) + '/' + 
SUBSTRING(@psBoundary, 1, 4)

SET @psSQL_Text = 'ALTER TABLE MovingPartition WITH CHECK 
ADD CONSTRAINT MovingPartition_Date CHECK  
(SK_Date_ID BETWEEN ' + 
CONVERT(varchar(8), DATEADD(dd, -6, @pdWeek_Begin), 112) + ' AND ' + 
CONVERT(varchar(8), @pnBoundary, 112) + ')'
EXEC (@psSQL_Text)

EXEC etl.up_CreateIndexes 'MovingPartition', @psNew_FG_Name

-- Get the partition number for the moving partition and switch it in to the 
-- new partitioned table
SET @psSQL_Text = 'ALTER TABLE MovingPartition SWITCH TO ' + 
@psNew_Partitioned_Table_Name + ' PARTITION ' + 
CONVERT(varchar(3), @PartitionNum)
EXEC (@psSQL_Text)

DROP TABLE MovingPartition

  5. 刪除或重新命名舊分割資料表

  6. 重新命名新分割資料表

觀察與建議

一些觀察結果是根據整組資料實驗得來，這些主要與效能有關。我們將這些觀察記錄如下。

共用分割函式

邏輯上而言，三個事實資料表使用的是同一個分割函式。我們決定使用相同的方式分割這三個表，藉此促進它們的連結。例如，假設我們發行一個關聯查詢，詢問一項物品的銷售相較於目前年度每個月的存貨。SQL Server 可以指出這兩個分割資料表是對齊的，也就是說它們共用同一個的分割函式。合併兩個對齊的分割資料表時，最佳化工具可以先選擇是否要在分割區內進行合併，然後再將子集加入結果一起帶入。當資料表共用相同的分割函式時，這並不表示它們實際上得共用相同的「分割函式」。這些表各自的分割函式必須有相同的分割區數，而且分割索引鍵必須具有相同的資料類型。所以要遵循這些規則，最簡單方式就是實際上共用相同的分割函式。

然而，共用相同的分割函式會產生一些問題。分割最後一個分割區來新增新邊界時，所有參照該分割函式的分割配置都必須指明下一個分割區所在之處。使用合併函式移除分割區時，所有依存的分割配置都應該已清空分割區。如果第一個分割區尚未替所有參照分割函式的資料表清空，合併的動作會迫使資料列實際從已移除分割區上移動到分割區合併的地點。這雖然不是很嚴重的問題，但是會在列移動時使效能打折，而且將不會移除我們想要封存的列。

為接下來的示範作準備，我們在左邊列出分割函式，並在最右邊的欄位列出三個分割配置。分割最後一個分割區好替切入新資料做準備時，必須要在三個分割配置中適當設定 NEXT FG，這樣新資料才可以送達正確的位置。合併第一個分割區，並準備移除以 2002/01/05 結束的這一週時，所有使用這三個配置的分割資料表的第一個分割資料必須已經經過切換，而且必須為空白。假設 Tbl_Fact_Store_Inventory 資料表還有這一週的資料，合併動作將會造成此資料移動到「Aged FG 3」。

分割函式                            分割配置

Pf_Range_Fact

ps_FactStoreSales

ps_FactStoreInventory

ps_FactDCInventory

01/05/2002

Aged FG 1

Aged FG 2

Aged FG 3

2002/01/12

Aged FG 2

Aged FG 3

Aged FG 4

…

 

 

 

2004/12/18

Active FG 1

Active FG 2

Active FG 3

2004/12/25

Active FG 2

Active FG 3

Active FG 4

2005/01/01

Next FG

Next FG

Next FG

所有問題都可以藉由建立分別的分割函式來解決，雖然仍會執行相同的動作，但唯一的好處就是它們不需要一起執行。分隔分割函式也可能會在隨時造成事實資料表所有的分割區數量不等，反而消弭一開始擁有對齊資料表的好處。

索引建立

一開始填入分割資料表時，要先面對的問題就是要在分割資料表載入事前或事後建立索引。在一開始這個決定對效能似乎不會造成太大影響。這些選項簡述如下：

選項 1–在載入前建立索引在分割資料表上

  1. 建立分割資料表

  2. 在分割資料表上建立索引

  3. 在每個來源資料表上

    1. 建立外部資料表

    2. SELECT INTO 外部資料表

    3. 建立 CHECK 條件約束

    4. 建立相符於分割資料表上索引的索引

    5. 切換外部資料表至分割資料表上適當的分割區

選項 2–載入之後在分割資料表上建立索引

  1. 建立分割資料表

  2. 在每個來源資料表上

    • 建立外部資料表

      1. SELECT INTO 外部資料表

      2. 建立 CHECK 條件約束

      3. 將外部資料表切換至分割資料表上適當的分割區

  3. 在分割資料表上建立索引

有畫底線的步驟是這兩個選項之間的差異。無論是哪個案例，索引都是在資料載入後才建立的，所以這兩個選項之間不應該有太大的差別，但是其實還有差異存在。「選項 2」經證實要快 70% ，所以我們選擇在首度載入中使用它。在遞增載入中我們無法在這兩個選項中做選擇，因為我們最終還是要切入至已經建立好索引的分割資料表內。因此，還是必須在切換之前在外部資料表上建立相符的索引。

採用「選項 1」時，不管是用於初始載入或是遞增載入，請務必記得在目的地分割資料表所使用的分割配置上建立索引。這樣一來，就能將一個載入的分割區，讓外部資料表成為一個分割資料表。因此您必須在 SWITCH 時指明外部來源資料表以及目的地資料表兩者的分割區數。

使用者定義的中繼資料查詢函式

有幾個資料管理檢視 (DMV) 可以代表分割函式與配置中的中繼資料。在我們的實作中都需要用上這些函式，但是本文中建立兩個使用者自訂函式時簡化了複雜度。由於這些 DMV 在我們的開發期間並未曾記錄下來，您應該在分割函式及分割配置物件上編寫 CREATES 指令碼時，追蹤 SQL Server Management Studio 產生的查詢，藉此決定該使用哪種 DMV。

第一個函式將分割配置所有檔案群組的資料表結果集以及它們相關的分割邊界盡數傳回 (在我們的案例中是上層邊界，因為我們使用 LEFT 分割函式)。

CREATE FUNCTION dbo.fn_Get_FileGroupsByPartitionBoundary(@SchemeName varchar(50))
RETURNS TABLE
AS RETURN
(
SELECT sf.name AS FileGroupName, 
CONVERT(int, sprv.value) AS Boundary
FROM sys.partition_schemes AS sps
INNER JOIN sys.partition_functions AS spf 
ON sps.function_id = spf.function_id
INNER JOIN sys.destination_data_spaces AS sdd 
ON sdd.partition_scheme_id = sps.data_space_id 
AND sdd.destination_id <= spf.fanout
INNER JOIN sys.partition_range_values sprv 
ON sprv.function_id = spf.function_id
AND sprv.boundary_id = sdd.destination_id
INNER JOIN sys.filegroups AS sf 
ON sf.data_space_id = sdd.data_space_id
WHERE sps.name= @SchemeName
)

第二個使用者定義函式將早於或是等於特定邊界的最後一個檔案群組傳回。

CREATE FUNCTION dbo.fn_Get_FileGroupForBoundary (@SchemeName varchar(50), @Boundary int)RETURNS varchar(50)WITH EXECUTE AS CALLERAS-- Find last filegroup prior to or equal to specified boundaryBEGINDECLARE @FileGroupNamevarchar(50)SELECT TOP 1 @FileGroupName = sf.name FROM sys.partition_schemes AS spsINNER JOIN sys.partition_functions AS spf ON sps.function_id = spf.function_idINNER JOIN sys.destination_data_spaces AS sdd ON sdd.partition_scheme_id = sps.data_space_id AND sdd.destination_id <= spf.fanoutINNER JOIN sys.partition_range_values sprv ON sprv.function_id = spf.function_idAND sprv.boundary_id = sdd.destination_idINNER JOIN sys.filegroups AS sf ON sf.data_space_id = sdd.data_space_idWHERE sps.name = @SchemeName AND sprv.value <= @BoundaryORDER BY sprv.value DESCRETURN(@FileGroupName)END

Cube Partitioning

Benefits of cube partitioning

除了將關連式資料倉儲分割，我們也要分割 Analysis Services cube。我們在 Analysis Services 2005 更精確地分割量值群組，並將整個程序總稱為「Cube 分割」。分割大型 Cube 有許多好處，和分割關聯資料倉儲的很類似。一項最顯明的好處就是在於 Cube 維護。Cube 不管是完整或是遞增處理，都是在分割層級上進行。有許多分割區的 Cube 可以選擇性的處理。尤其是當變更來源資料表只有一個或是幾個分割區受到影響時特別可以派上用場。如此一來，就能大幅降低批次視窗的遞增處理。分割區也有其各自的彙總與儲存模式。依日期分割時，較舊的分割區可在較低的彙總層級上再處理過，或是使用不同的儲存模式 (ROLAP/HOLAP/MOLAP) 來降低所需的磁碟空間與處理時間。主動快取設定也同樣在分割層級中定義。分割區甚至可以儲存在遠端伺服器上，作為遠端分割區。這樣管理起來比較方面，因為舊分割區可以很簡易地被刪除，不用重新處理整個量值群組來辨識基礎關聯資料來源的保存資料。以分割索引鍵限制資料這種方式撰寫 Cube 查詢時，也要考量到查詢效能。

Analysis Services 2005 中的變更

大部分分割的原則都可以應用在 Analysis Services 2000 中，而且與 Analysis Services 2005 有關。有許多的變更都是上一版本功能的提升。

首先，分割區是在量值群組層級，而非在 Cube 層級進席定義。一個 Cube 包含一或多個與量值群組來源資料表中的邏輯事實資料表相關的量值群組。由於 Cube 或是量值群組處理會自動產生在基礎分割區平行處理中，所以可以大幅提高效能。在 Analysis Services 2000 中會連續處理分割區，除非自訂 DSO 程式明確強制平行處理，像是「平行處理」公用程式 (可在 Microsoft 網站中免費下載)。

在 Analysis Services 2000 中，只有在資料 Slice 在 Cube 分割區中定義，讓 OLAP 引擎知道哪個資料包含在哪個分割區中時，您才能感受到分割帶來的效益。 這類似分割檢視中的定義檢查條件約束，可讓 SQL Server 最佳化工具根據分割資料欄減少查詢的資料表數目。在 Analysis Services 2005 中，MOLAP Cube 不再需要使用資料 Slice 來進行這項作業，因為 MOLAP Cube 已經有 Heuristic 可以對應不同分割區中包含的資料。請注意，在主動快取重新建置期間等任何情況下還原至 ROLAP 的 Cube 時，將無法使用 Heuristic。這時如果不定義 Slice將會拖累效能。因此，如果 Cube 在將來可能會還原至 ROLAP，您應該要定義資料 Slice。

在 Analysis Services 2000 Cube 中的來源資料會直接從來源關聯資料庫中的資料表或是檢視定義出來。我們可以定義一個篩選來指定資料表或是檢視的子集。例如，如果使用了分割檢視，就可以把分割檢視的名稱當成 Analysis Services 2000 分割區的來源使用，並使用另一個篩選，藉此限制 Analysis Services 中的資料到分割檢視基礎資料表中的日期為止。在 Analysis Service 2005 中，應該來源指定為資料表/檢視或是查詢，兩者則一。如果基礎資料表或是檢視的子集將會填入分割區，那麼分割區定義就會指定一個名為「查詢繫結」的查詢。

最後，在 Analysis Services 2005 中還有更多的分割區建立自動化選項。以前 DSO (設計支援物件) 主要用來「複製」現有分割區、變更相關屬性，然後儲存新分割區。 現在 DSO 已經由 AMO (Analysis Management Objects) 所取代。所有來自於 AMO 的要求最後都會被轉譯成 XMLA (在 Analysis 中為 XML) 指令碼。由於 SSIS 有執行原生 XMLA 的能力，您也可使用它。哪個會是比較合適呢？這也是我們希望判定的。

關於 Analysis Services 分割策略的討論

在 Analysis Services 中實作分割的第一步驟就是決定分割策略，也就是該在哪個邊界劃分分割區。一個常用的策略就是依照日期來作分割 (類似關連資料倉儲中的作法)。因為資料通常是依照日期遞增載入，所以能簡化程序。這連帶也使保存更簡單，因為如先前所述，舊分割區可以在備份之後被刪除。使用基礎關聯資料倉儲相同的條件來分割 Analysis Services Cube 是有益的。因為只要用單一處理刪除資料倉儲及 Cube 中的適當分割區，就可以做到資料保存。這也是 Barnes and Noble 與 Project REAL 所選擇的策略。

Barnes and Noble 實作包含了在每個事實資料表上的檢視，來從相關維度會員中新增一些額外資訊。這在 Analysis Services 2000 Cube 分割區中被當成來源「資料表」。採納分割資料表後，檢視數量從每個事實資料表每週一個檢視資料 (229 個檢視)，下降到每個事實資料表只有一個檢視 (3 個檢視)。Analysis Services 2005 分割依照 WHERE 陳述式的相關檢視指定查詢，來限制資料至單一分割區，例如：

SELECT [SK_Store_ID], [SK_Parent_Store_ID], ...
FROM [dbo].[vTbl_Fact_Store_Inventory] 
WHERE [SK_Date_ID] BETWEEN 20041212 AND 20041218

有兩種方式可以自動建立與處理 Analysis Services 2005 中的 Cube。關於這兩種方式環繞的詳細資訊，包含優缺點，將在下節進一步說明。

XML/A 概觀

第一個方式為 XMLA (更精確說法應該是 ASSL)。XMLA 是一種為了查詢 OLAP 資料而在 2001 年 4 月 初次發行的 XML 規格 (您可以在 www.xmla.org 找到關於 XMLA 規格的更多資訊)。ASSL 由 Analysis Services 專用，屬於 OLAP 的一種 XML DDL 規格。XMLA 是 Analysis Services 2005 的原生資料交換協定。所有與 Analysis Services 的通訊最終都會透過 XMLA 執行，而且又不需要產生轉譯，所以是與 Analysis Services 2005 溝通的最快方式。然而這在大部分的中繼資料作業中或許不是很重大的因素，由於並不會有大量的查詢提交至 Analysis Services，而此轉譯的成本應該不大。

XMLA 指令碼乍看之下挺複雜。XMLA 與其他 XML 指令碼語言很類似，XMLA 中的項目是以階層式架構方式顯示，而且具有自我說明的特性。這個語言相當繁瑣，不是隨便就能從無到有撰寫出來。幸好 Analysis Services 2005 替撰寫 XMLA 提供一個好的開始。

XML/A 實作

在 Analysis Services 2005 中，Cube、維度與分割區等編寫物件指令碼的功能有莫大的提升。相比之下，Analysis Services 2000 一開始是沒有指令碼編寫能力的。此指令碼編寫能力使得使用 XMLA 成為合理的另類選擇，因為使用者不需要對 XMLA 規格有相當程度的知識。可以在 SQL Server Management Studio 中開啟新的 [Analysis Services XMLA 查詢] 視窗來執行 XMLA。也可以在 SQL Server Integration Services (SSIS) 中經由 [Analysis Services 執行 DDL] 工作來執行。這樣的特性能夠讓我們利用 XMLA，自動建立分割區及處理。

第一步驟就是為每個 Cube 量值群組分別產出個各 XMLA 指令碼。您可以在 SQL Server Management Studio 中，巡覽至量值群組中分割區、按一下滑鼠右鍵，然後選取 [Script Partition as] 來達成。接下來我們檢視 XMLA 來了解每個分割區需要做什麼變更，方法是將指令碼貼入 [SQL Server Management Studio XMLA 查詢] 視窗中。這樣就能將此指令碼貼上到一個 SSIS Script 工作上，而各個分割區之間所有變動的文字都會被依分割區而修改的變數所取代。此 XML 字串會被儲存為 SSIS 變數，並成為 [Analysis Services Execute DDL] 工作中的可執行檔。以下指令碼取自於 SSIS Script 工作中。這個工作用來替「REAL Warehouse」 Cube 中的「分店庫存」量值群組編寫分割區指令碼。會為分割名稱、分割區識別碼及對照關聯來源的查詢而建立變數。

sXMLA = sXMLA & "<Create 
https://schemas.microsoft.com/analysisservices/2003/engine"">"
sXMLA = sXMLA & "    <ParentObject>"
sXMLA = sXMLA & "        <DatabaseID>REAL Warehouse Partitioned</DatabaseID>"
sXMLA = sXMLA & "        <CubeID>REAL Warehouse</CubeID>"
sXMLA = sXMLA & "        <MeasureGroupID>Store Inventory</MeasureGroupID>"
sXMLA = sXMLA & "    </ParentObject>"
sXMLA = sXMLA & "    <ObjectDefinition>"
sXMLA = sXMLA & "        <Partition xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" 
xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">"
sXMLA = sXMLA & "            <ID> & sPartitionName & </ID>"
sXMLA = sXMLA & "            <Name> & sPartitionName & </Name>"
sXMLA = sXMLA & "            <Annotations>"
sXMLA = sXMLA & "                <Annotation>"
sXMLA = sXMLA & "                    <Name>AggregationPercent</Name>"
sXMLA = sXMLA & "                    <Value>13</Value>"
sXMLA = sXMLA & "                </Annotation>"
sXMLA = sXMLA & "            </Annotations>"
sXMLA = sXMLA & "            <Source xsi:type=""QueryBinding"">"
sXMLA = sXMLA & "                <DataSourceID>REAL Warehouse</DataSourceID>"
sXMLA = sXMLA & "                <QueryDefinition> & sQuery & "</QueryDefinition>"
sXMLA = sXMLA & "            </Source>"
sXMLA = sXMLA & "            <StorageMode>Molap</StorageMode>"
sXMLA = sXMLA & "            <ProcessingMode>Regular</ProcessingMode>"
sXMLA = sXMLA & "            <ProactiveCaching>"
sXMLA = sXMLA & "                <SilenceInterval>PT10M</SilenceInterval>"
sXMLA = sXMLA & "                <Latency>-PT1S</Latency>"
sXMLA = sXMLA & "                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>"
sXMLA = sXMLA & "                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>"
sXMLA = sXMLA & "                <Source xsi:type=""ProactiveCachingInheritedBinding"" />"
sXMLA = sXMLA & "            </ProactiveCaching>"
sXMLA = sXMLA & "            <EstimatedRows>2000000</EstimatedRows>"
sXMLA = sXMLA & "            <AggregationDesignID>AggregationDesign 2</AggregationDesignID>"
sXMLA = sXMLA & "        </Partition>"
sXMLA = sXMLA & "    </ObjectDefinition>"
sXMLA = sXMLA & "</Create>"

您也可以巡覽至 SQL Server Management Studio 中的量值群取分割區，按一下滑鼠右鍵，選取 [處理]，然後按下結果對話方塊上方的 [指令碼] 按鈕，以 XMLA 編寫 Cube 處理。處理 Cube 的 XMLA 指令碼範例看起來如下。

<Batch xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Object>
        <DatabaseID>REAL Warehouse Partitioned</DatabaseID>
        <CubeID>REAL Warehouse</CubeID>
        <MeasureGroupID>Store Inventory</MeasureGroupID>
        <PartitionID>Store Inventory WE 2004 12 11</PartitionID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

AMO 概觀

Analysis Management Objects (AMO) 是管理 Analysis Services 的一組完整 .NET 物件。AMO 取代在 Analysis Services 2000 中所使用的 DSO 物件模型及 SQL Server 7.0 中的 OLAP Services。Analysis Services 2000 中所有分析管理員 (Analysis Manager) 動作都是經由 DSO 實作。同樣地，在 Analysis Services 2005 中，所有管理 Analysis Services 執行個體與資料庫的機制都是透過 AMO 實作。AMO 是位於 XMLA 上面的一層，最後會產生 XMLA 來執行與 Analysis Services 執行個體之間的所有溝通。因此它的效能比 XMLA 稍微弱了一點。但除非有許多的 AMO 方法呼叫，效能上的差異其實微乎其微。

SQL Server 2005 中有其他功能加強，使得使用 AMO 比先前的 DSO 更容易許多。由於 DTS 中經常採用自動化作業，我們轉移到 VBScript 開發環境。這個環境並沒有如同在 SSIS 的 Script 工作中使用的 VSA (Visual Studio for Application) 介面具有豐富的程式碼 (IntelliSense、色彩編碼) 功能集。而且在《線上叢書》中關於 AMO 的文件仍然不多，所以功能上得靠 Project REAL 開發 AMO 程式碼。

DSO 最愚鈍的一點應該是 MDStores 介面實作要巡覽下至分割物件。這在 AMO 中已不存在，而且資料庫巡覽更直接。等一下我們會用程式碼範本為您說明。

請注意，Barnes and Noble 目前的 Analysis Services 2000 實作並未使用 DSO 和 DTS 建立 Cube 分割。分割區一年一次手動建立，而且因為 Analysis Services 2000 無法原生地平行處理分割區，所以採用「平行處理公用程式」(Parallel Process Utility) 實作處理。現在有個 Analysis Services 2005 的加強功能，我們可以將這些處理輕鬆地自動化，降低行政負荷。

AMO 實作

我們的目標就是使用 AMO 執行我們在上述中使用 XMLA 所達成的相同功能。實際結果顯示我們能夠以 AMO 物件模型中固有的迴圈機制為基礎來提升功能。 迴圈掃過每個量值群組，並且檢查量值群組是否有參照特定的事實資料表。如果有，使用特定處理日期來取決相關分割區是否已經存在。如果分割區不存在，就需要建立一個分割區。

XMLA 實作中分割區是這樣產生的：執行先前產生的執行碼，並且變更 XML 中指定分割區名稱、分割區識別碼及查詢定義的項目。AMO 可以讓您以 Clone 方法在量值群組中複製既有分割區。這裡我們不需要明確地設定程式碼中分割區屬性來建立分割區。我們使用「複製」方法從 [分割區] 集合的最後一個分割區複製所有一切，然後將 XMLA 指令碼中修改的同一個屬性加以變更。

以 Script 工作實作的 VSA 環境同時也允許我們獲取更多錯誤處理的能力。DTS 中的 VBScript 程式碼規定每個動作後面都要跟著檢查，以檢查錯誤，因為沒有方法可以全面處理錯誤。這樣卻導致更複雜、更不易讀懂的程式碼。在「SQL Server Integration Services (SSIS) Script」工作中使用 Visual Basic .NET，我們可以使用「Try..Catch」陳述式來，在單一組陳述式中以一貫的方式處理錯誤。我們同時也使用了宣告及初始化變數的功能來增加程式碼的可讀性。

我們還運用另一項技巧，從已存在的 SSIS 連線管理員收集連線資訊。這表示當部署此封裝至「品管與製造」環境時，我們只需要在一個地方修改「伺服器與資料庫」訊息即可。這在 SSIS 中也可以使用 Configurations 來實作 。

以下是建立分割區的程式碼最重要的組成部分。

Try

   Dim oDB As Database = oServer.Databases(sDatabase)
   Dim oCube As Cube = oDB.Cubes("REAL Warehouse")

   Dim dLogicalDate As Date = CDate(Dts.Variables("vdtLogical_Date").Value)
   Dim sTableName As String = CStr(Dts.Variables("vsPartitioned_Table_Name").Value)
   Dim sWeekEnd As String = GetIntegerDateFormat(dLogicalDate)

   ' Find all measure groups that reference the table being processed
   For Each oMeasureGroup In oCube.MeasureGroups
  oPartition = oMeasureGroup.Partitions(0)
      oQueryBinding = oPartition.Source
      If oQueryBinding.QueryDefinition Like "*" & sTableName & "*" Then
         ' Get the relevant boundary partition name and check to see if it 
         ' already exists
         sPartitionNew = GetNewPartitionName(sWeekEnd, oPartition.Name)
         oPartition = oMeasureGroup.Partitions.FindByName(sPartitionNew)
         If oPartition Is Nothing Then
            ' Get the last partition
            oPartition = oMeasureGroup.Partitions(oMeasureGroup.Partitions.Count - 1)
            ' Clone the properties from the last partition to the new partition.
            oPartitionNew = oPartition.Clone
            oPartitionNew.ID = sPartitionNew
            oPartitionNew.Name = sPartitionNew
            oQueryBinding = oPartitionNew.Source
            oQueryBinding.QueryDefinition = GetNewQuery(oPartition.Source, sWeekEnd)
            oMeasureGroup.Partitions.Add(oPartitionNew)
            oPartitionNew.Update()
         End If
      End If
   Next
   Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
   Dts.Events.FireError(0, "Create Partition", ex.Message, "", 0)
   Dts.TaskResult = Dts.Results.Failure
End Try
If oServer.Connected Then
   oServer.Disconnect()
End If

VSA 目前有一個問題，所有參考組件都必須要位於 <windows path>\ Microsoft.NET\Framework 路徑的適當版本子目錄中。AMO 及 SMO 組件必須要手動從 <SQL Server>\90\SDK\Assemblies 目錄中複製到上述的目錄中。在安裝 SQL Server 後續建置之後，可能會建立新的 VSA 版本目錄，而這些組件可能會產生變更，導致這些檔案需要重新被複製。以本份文件所參考的 AMO 程式碼而言，只有 AMO 組件是必要的 (Microsoft.AnalysisServices.DLL)。

在確認適當的檔案已經複製到 VSA 可以識別它們的地方之後，您必須在 Script 工作中替這些檔案新增參考。方法是在「專案總管」中的 [參考] 上按一下滑鼠右鍵，然後選取 [新增參考...]。找出「Analysis 管理物件」參考，然後將它新增。接著按照下圖的指示，新增一行文字到指令碼開端來「匯入 Microsoft.AnalysisServices」。

Dd159898.realpart15(zh-tw,TechNet.10).gif

[圖 15] 新增指令碼工作參考

觀察與建議

使用 SSIS 時實作 XMLA 指令碼及 AMO 選項更容易。以下將討論每個方法的一些觀察結果。特定實作讓我們更能看出某些選項的優勢。總而言之，我們發現 AMO 能提供比 XMLA 更突出的表現，詳述如下。

AMO 觀察

優點：

  • 更簡潔了當–雖然屬於主觀判斷，但大部分的情況都可以這麼說。

  • 可以用來包含未來物件–在檢閱 AMO 程式碼時，您將會注意到每個量值群組的第一個分割區都會被檢查有沒有所經事實資料表的查詢參考。有了設計良好的 Cube 之後，額外的 Cube 被新增至參考相同的事實資料表是不太可能的，但是此情況也已經有因應方法了。它也處理了量值群組重新命名的可能–量值群組名稱在 XMLA 實作中是硬式編碼。

  • 動態加入分割區屬性變更–由於最後一個分割區被複製了，任何針對彙總設計、儲存模式、主動快取等等的變更，都會被複製到新分割區。在大部分的情況下這是不錯的方法。

  • 全部程式都在一個簡單讀取的 Script 工作中–XMLA 實作需要一個 Script 工作及一個個別的 Execute Analysis Services DDL 工作。

  • 可以與像是處理這樣的額外工作合併成為一個單一工作–不管是經由 Execute Analysis Services DDL 工作或是經由 Analysis Services Processing 工作實作處理，還是需要一個獨立的工作。相較之下，只要新增單單一行額外文字到 AMO 指令碼中就可以實作處理。

缺點：

  • 在每個新 SQL Server 建立時必須手動複製組件至 Microsoft .NET
XMLA 觀察

優點：

  1. 每樣東西都顯露無遺–基礎物件的所有相關屬性都被顯示出來，使得取決何者該變更，何者不該變更變得更容易。

  2. 最低層級–由於 XMLA 是 Analysis Services 的原生通訊協定，速度最快。

缺點：

  • 必須手動編寫指令碼–但並非嚴格要求。可以發展出一個廣義的指令碼，使用變數來替換每個量值群組中都可能會變更的所有屬性。這些屬性必須為每個量值群組的硬式編碼，或是使用 AMO 從每個量值群組中擷取出來。額外的量值群組還是需要靠手動新增到指令碼中。

  • 還是需要 AMO 檢查分割區是否存在–在我們的情況中，我們想讓 ETL 處理可以從新啟動的。所以，在開始建立分割區之前，我們先檢查是否有理想分割區已存在了。這只能經由 AMO 完成：此程式碼在 AMO 實作情況中原本就加入在分割區建立程式碼中。

  • 如果變更任何不是原本編寫的內容，就需要重新編寫指令碼或是手動變更指令碼–這或許是使用 XMLA 指令碼來自動化建立分割區的最大問題。在一些少數的個案中，永遠保留原始編寫的屬性是較好的。此案例可以很簡單地使用 AMO 或是 XMLA 來實作。

概括觀察

兩種方法都受益於 SSIS 中的許多項加強功能：

  • 在 SSIS 中建立分割區不是難事–雖然 DTS 提供 DSO 編碼的 VBScript 工作，開發環境包含 VSA 環境中 SSIS Script 工作所使用的幾個益處。Execute Analysis Services DDL 工作讓執行 XMLA 更簡單。

  • 較佳的錯誤處理與除錯能力，閱讀程式碼更容易，開發程式碼更快速。

  • 當完全使用 AMO 時，您必須使用因應措施，這樣 VSA 才能參考 AMO 組件 (請參閱 AMO 觀察中最後一個項目)。

ETL 變更

現在所有元件已經定義來實作遞增分割區維護了，我們需要將它整合回現有的 ETL 處理。請記住，現有的實作在每個分割區上都使用外顯的 SQL Server 2000 資料表。由於沒有足夠的歷史資料來要求實作分割區保存，此處理並不存在目前的 ETL 中。相同地，也沒有處理來實作資料老化。

這是 Barnes and Noble 分店庫存與發貨中心事實資料表中，現有 ETL 處理的概觀，它適用於管理關聯分割區。

  • 預先執行處理

  • 複製「目前」資料表內容到新的「具名」資料表來反應出前一週狀況 (例如：Tbl_Fact_Store_Inventory_WE_2004_12_11)

  • 在「具名」資料表上建立索引 (因為使用了 SELECT INTO )

  • 重新初始化「目前」資料表來反應接下來的一週 (SK_Date_ID, Days_In_Stock, ETL_Load_ID)

  • 後執行處理

Dd159898.realpart16(zh-tw,TechNet.10).gif

[圖 16] Barnes and Noble 分割區建立–以前

銷售事實分割表並沒有相等的處理，因為它們是事先大量建立的。這是為了減除遞增建立處理的需求。銷售事實分割區維護包含在 Project REAL 遞增處理中，免去手動的處理的麻煩。遞增分割區維護的修改內容簡述如下：

  1. 預先執行處理–這並沒有變更

  2. 從上一個分割區，將資料複製至下個檔案群組中新的外部資料表 (我們可以在此步驟中重新初始化諸如 SK_Date_ID 欄位)

  3. 在新外部資料表中建立索引 (因為使用了 SELECT INTO)

  4. 分割最後一個分割區並且將新外部資料表切換進來

  5. 移除最舊的分割區並將保存分割區移動至低價磁碟–新功能

  6. 後執行處理

目前 Barnes and Noble 在 ETL 處理外部維護 Analysis Services 2000 Cube 分割區。銷售及庫存 Cube 分割區建立為到未來的一年，而在目前該週資料轉入之前不會經過處理。這種處理作業已經不再需要，因為我們很容易就可以將 Cube 分割維護添加至 ETL 處理中。

其他的需求

我們新增了兩個其他的需求到資料生命週期管理中的分割區維護。

  1. 重新啟動性

    我們想要確保以分割區維護觀點來看，此處理是可以重新啟動的。也就是說任何建立新分割區、移除舊分割區或是老化分割區的程式碼，都會執行檢查來查看這是否已經有發生過了。

  2. 星期的天數分隔 (在 SSIS 封裝執行中) 及分割區管理處理引動過程

分割區維護 SSIS 封裝可以在該週的任何一天呼叫。預存程序將會查詢中繼資料，檢查是否該是執行任何分割區維護函式。如果處理日期為星期一，還會再執行一道檢查，看看是否有相關分割區存在。這些查詢執行相當快速，並不會對每日處理造成重大負荷。這樣區別每天與每週 ETL 處理的要求就不需要派上用場。

連結資料表分割元件

當接近此功能的自動化時，一般人會想使用「Sliding Window」實作來整合資料移動活動。但是隨著時間推進，這個功能會變得很複雜，特別是從未來維護的觀點來看。此動作會需要建立新的分割區配置來包含新分割區及移除舊分割區。分割區的新增或是移除事實上是在分割函式層級發生的，而且此分割函式由所有分割配置所共用。因為我們嘗試要判斷這是不是舊配置上的分割區，而非新配置的分割區，很快地，程式碼就變得相當複雜。最後，將程式碼分開會比較簡單，而且如果企業需要指定，這也是一個實體分隔處理的機會。事實上，Sliding Window 實作也被分隔成兩個儲存過程。這樣能促進商務需求可能的未來變更。假如在後來決定每週新增新分割區，但是舊分割區每年移除一次，就可以輕鬆地進行變更。以下預存程序是可以用來將遞增分割區維護的邏輯封裝起來：

  • up_CreateNewPartition–以邏輯日期 (資料應用的日期) 為基礎，查看此資料有無分割區存在。如果沒有，則建立分割區。如果這是一個庫存分割區，使用前一個分割區的資料來初始化分割區，並且變更相關欄位，如 SK_Date_ID。如果這是一個銷售分割區，則不需要額外的步驟。

  • up_RemoveOldPartitions–以邏輯日期為基礎，檢視是否需要保存任何到期分割區。我們在 Project REAL 中直接刪除了舊分割區，但是您可能需要作磁帶保存。

  • up_MoveAgedPartitions–以邏輯日期為基礎，檢視是否有任何到期分割區需要移動到低價磁碟。

這三個儲存程序是由一個父儲存程序 up_MaintainPartitionedTable 所呼叫。整個分割區維護處理是封裝在不同的 SSIS 封裝，在整個 ETL 處理過程中會被呼叫。

Dd159898.realpart17(zh-tw,TechNet.10).gif

[圖 17] Barnes and Noble 分割區建立–之後

參考資料

結論

Barnes and Noble 可以藉由在 SQL Server 2005 中使用分割資料表獲得許多益處。Barnes and Noble 並沒有實作分割檢視，因為這會造成大量的編譯時間，有時高達 30 秒鐘。資料被分割至不同的資料表中，但是這些資料表是被分別管理的。ETL 處理靠程式碼判斷在載入資料時該在更新那個資料表。分割資料表移除了此需求，並能降低行政耗費。

其他相關資訊：

https://www.microsoft.com/sql/

此份文件對您有幫助嗎？請不吝賜教。從 1 (差) 到 5 (優) 評分， 您會給這篇文章打幾分？

下载

Dd159898.icon_Word(zh-tw,TechNet.10).gifProject REAL Data Lifecycle Partitioning.doc
676 KB
Microsoft Word 檔
更新日期: 2007 年 1 月 5 日