分享方式：

Microsoft Fabric Warehouse 中的維度模型化：載入資料表

  • 文章

適用於：✅ Microsoft Fabric 中的 SQL 分析端點和倉儲

注意

本文是維度模型化系列文章的一部分。 此系列著重於 Microsoft Fabric Warehouse 中維度模型化的相關指導和設計最佳做法。

本文提供在維度模型中載入維度和事實資料表的指導和最佳做法。 它提供 Microsoft Fabric 中 Warehouse 的實際指引，這是支援許多 T-SQL 功能的體驗，例如在資料表中建立及管理資料。 因此，您可以完全控制建立維度模型資料表，並向其加載資料。

注意

在本文中，術語資料倉儲是指企業資料倉儲，可全面整合整個組織的重要資料。 相反，獨立術語倉儲是指 Fabric Warehouse，這是一種軟體即服務 (SaaS) 關聯式資料庫服務，可供您用來實作資料倉儲。 為了清楚起見，本文中將後者稱為 Fabric Warehouse。

提示

如果您不熟悉維度模型化，請先查閱這一系列的文章。 其目的不是提供關於維度模型化設計的完整討論。 如需詳細資訊，請直接參考廣泛採用的已發佈內容，例如 The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (2013 年第 3 版)，其作者為 Ralph Kimball 和其他人。

載入維度模型

載入維度模型涉及定期執行擷取、轉換和載入 (ETL) 處理序。 ETL 處理序會協調其他流程的執行，這些處理序通常與檢閱及測試來源資料、同步維度資料、將行插入事實資料表，以及記錄稽核資料和錯誤有關。

針對 Fabric 倉儲解決方案，您可以使用資料處理站來開發和執行 ETL 處理序。 此程式可以檢閱及測試、轉換來源資料，並將其載入維度模型資料表。

具體而言，您可以：

  • 使用資料管線來建置工作流程，以協調 ETL 處理序。 資料管線可以執行 SQL 指令碼、預存程序等等。
  • 使用資料流程開發低程式碼邏輯，從數百個資料來源擷取資料。 資料流程支援結合多個來源的資料、轉換資料，然後將其載入目的地，例如維度模型資料表。 資料流程是透過使用熟悉的 Power Query 體驗建立的，此體驗目前可在許多 Microsoft 產品中使用，包括 Microsoft Excel 和 Power BI Desktop。

注意

ETL 開發可能很複雜，也可能很有挑戰性。 據估計，60-80% 的資料倉儲開發工作專門針對 ETL 處理序。

協調流程

ETL 處理序的一般工作流程是：

  1. 可選擇 [載入暫存表格]。
  2. 處理維度資料表。
  3. 處理事實資料表。
  4. 或者，執行後處理工作，例如觸發相依 Fabric 內容 (例如語意模型) 的重新整理。

圖表顯示 ETL 處理序的四個步驟，如上一段所述。

必須先處理維度資料表，以確保它們會儲存所有維度成員，包括自上個 ETL 處理序之後新增至來源系統的成員。 當維度之間有相依性時 (如子維度)，應該依相依性的順序處理維度資料表。 例如，客戶維度所用的地理維度和廠商維度應該在另外兩個維度之前處理。

一旦處理完所有維度資料表，即可處理事實資料表。

處理完所有維度模型資料表後，您可以觸發相依語意模型的重新整理。 傳送通知給相關人員以通知他們 ETL 處理序的結果也是個好主意。

暫存資料

檢閱及測試來源資料可協助支援資料載入和轉換需求。 其涉及擷取來源系統資料，並將其載入暫存表格，您可以建立該資料表以支援 ETL 處理序。 建議您檢閱及測試來源資料，因為這樣可以：

  • 將對作業系統的影響降到最低。
  • 用來協助和最佳化 ETL 處理序。
  • 可用於重新啟動 ETL 處理序，而不需要從來源系統重新載入資料。

暫存表格中的資料不應該提供給商務使用者使用。 它只與 ETL 處理序相關。

注意

當您的資料儲存在 Fabric Lakehouse 時，可能不需要將其資料暫存於資料倉儲中。 如果實作獎章結構，您可以從青銅、白銀或黃金層取得資料。

建議您在倉儲中建立結構描述，可能命名為 staging。 暫存表格在資料行名稱和資料類型方面應盡可能類似於來源資料表。 每個資料表的內容都應該在 ETL 處理序開始時移除。 不過，請注意，Fabric 倉儲資料表無法截斷。 相反，您可以刪除並重新建立每個暫存表格，再載入資料。

您也可以考慮將資料虛擬化替代方案作為暫存策略的一部分。 您可以使用：

轉換資料

您的來源資料結構可能與維度模型資料表的目的地結構不太相似。 因此，您的 ETL 處理序需要重塑來源資料以與維度模型資料表的結構保持一致。

此外，資料倉儲必須提供經過清理且符合要求的資料，因此可能需要轉換來源資料，以確保品質和一致性。

注意

垃圾進，垃圾出的概念當然適用於資料倉儲，因此，請避免將垃圾 (低品質) 資料載入到維度模型資料表中。

以下是 ETL 處理序可執行的一些轉換。

  • 合併資料：不同來源的資料可以根據相符索引鍵進行整合 (合併)。 例如，產品資料會儲存在不同的系統 (例如製造和行銷)，但它們都使用一般庫存單位 (SKU)。 也可以附加資料，只要資料共用通用結構即可。 例如，銷售資料儲存在多個系統中。 每個系統的銷售資料的並集可以產生所有銷售資料的超集。
  • 轉換資料類型：資料類型可以轉換成維度模型資料表中定義的類型。
  • 計算：可以完成計算以產生維度模型資料表的值。 例如，對於員工維度資料表，您可以串連名字和姓氏來產生全名。 另一個範例是，對於銷售事實資料表，您可以計算總銷售額，也就是單價與數量的乘積。
  • 偵測及管理歷史變更：變更可被偵測並適當儲存在維度資料表中。 如需詳細資訊，請參閱本文稍後的管理歷史變更。
  • 彙總資料： 彙總可用來減少事實資料表維度和/或提高事實的細微性。 例如，銷售事實資料表不需要儲存銷售訂單編號。 因此，依所有維度索引鍵分組的彙總結果可用來儲存事實資料表資料。

載入資料

可以使用下列資料擷取選項，在 Fabric 倉儲中載入資料表。

  • COPY INTO (T-SQL)：當來源資料包含儲存於外部 Azure 儲存體帳戶 (例如 ADLS Gen2 或 Azure Blob 儲存體) 中的 Parquet 或 CSV 檔案時，此選項非常有用。
  • 資料管線：除了協調 ETL 處理序之外，資料管線還可以包含執行 T-SQL 陳述式、執行查找或者將資料從資料來源複製到目的地的活動。
  • 資料流程：作為資料管線的替代方案，資料流程提供無程式碼的體驗來轉換和清理資料。
  • 跨倉儲擷取：當資料儲存在同一工作區時，跨倉儲擷取功能允許聯結不同的倉儲或 Lakehouse 資料表。 它支援 INSERT…SELECT、SELECT INTO 和 CREATE TABLE AS SELECT (CTAS) 等 T-SQL 命令。 當您想要從同一工作區內的暫存表格轉換和載入資料時，這些命令特別有用。 它們也是以設定為基礎的作業，可能是載入維度模型資料表最有效率且最快的方式。

提示

如需這些資料擷取選項的完整說明 (包括最佳做法)，請參閱將資料內嵌至倉儲。

記錄

ETL 處理序通常需要專門的監視和維護。 基於這些原因，我們建議您將 ETL 處理序的結果記錄到倉儲中的非維度模型資料表。 您應該為每個 ETL 處理序產生唯一的 ID，並用其記錄每個操作的詳細資料。

請考慮記錄：

  • ETL 處理序：
    • 每個 ETL 執行的唯一 ID
    • 開始時間和結束時間
    • 狀態 (成功或失敗)
    • 發生的錯誤
  • 每個暫存和維度模型資料表：
    • 開始時間和結束時間
    • 狀態 (成功或失敗)
    • 插入、更新和刪除的資料列
    • 外部資料表資料列計數
    • 發生的錯誤
  • 其他作業：
    • 語意模型重新整理作業的開始時間和結束時間

提示

您可以建立專門用於監視和分析 ETL 處理序的語意模型。 處理序持續時間可協助您找出可能受益於審閱和最佳化的瓶頸。 資料列計數不僅能幫助您了解每次執行 ETL 時累加負載的大小，也有助於預測資料倉儲的未來大小 (以及適當時增大 Fabric 容量的時機)。

處理維度資料表

處理維度資料表涉及與來源系統同步資料倉儲資料。 來源資料會先轉換並做好準備以載入到其維度資料表。 接著，此資料會藉由聯結商務索引鍵，與現有的維度資料表資料進行比對。 然後，可以判定來源資料是否代表新的或修改過的資料。 當維度資料表套用緩慢變更維度 (SCD) 類型 1 時，會藉由更新現有的維度資料表資料列來進行變更。 當資料表套用 SCD 類型 2 變更時，現有版本會過期，系統會插入新版本。

下圖描述用於處理維度資料表的邏輯。

圖表顯示一個流程，描述如何將新的和已變更的來源資料列載入維度資料表，如下段所述。

請考慮 Product 維度資料表的處理序。

  • 當新產品新增至來源系統時，資料列會插入到 Product 維度資料表。
  • 產品被修改時，系統會更新或插入維度資料表中的現有資料列。
    • 當套用 SCD 類型 1 時，會對現有的資料列進行更新。
    • 當套用 SCD 類型 2 時，將進行更新以使目前資料列版本過期，並插入代表目前版本的新資料列。
    • 當套用 SCD 類型 3 時，會發生類似 SCD 類型 1 的處理序，更新現有的資料列而不插入新的資料列。

Surrogate 索引鍵

我們建議確定每個維度資料表都有 Surrogate 索引鍵，並應使用可能的最小整數資料類型。 在通常藉由建立身分識別資料行來完成的 SQL Server 型環境中，Fabric 倉儲不支援此功能。 相反，必須使用可產生唯一識別碼的因應措施技術。

重要

當維度資料表包含自動產生的 Surrogate 索引鍵時，切勿對其執行截斷和完全重新載入。 因為這樣做會使載入至使用維度之事實資料表的資料失效。 此外，如果維度資料表支援 SCD 類型 2 變更，可能無法重新產生歷史版本。

管理歷史變更

當維度資料表必須儲存歷史變更時，您必須實作緩慢變更維度 (SCD)。

注意

如果維度資料表資料列是推斷成員 (透過事實載入處理序插入)，則應將任何變更視為遲到的維度詳細資訊，而不是 SCD 變更。 這種情況下，應該更新任何已變更的屬性，並將推斷的成員旗標資料列設定為 FALSE。

維度可能支援 SCD 類型 1 和 SCD 類型 2 變更。

SCD 類型 1

偵測到 SCD 類型 1 變更時，請使用下列邏輯。

  1. 更新已變更的屬性。
  2. 如果資料表包含上次修改日期以及上次修改執行者資料行，設定執行修改的最新日期和處理序。

SCD 類型 2

偵測到 SCD 類型 2 變更時，請使用下列邏輯。

  1. 將結束日期有效性資料行設定為 ETL 處理日期 (或來源系統中的適當時間戳記)，並將目前的旗標設定為 FALSE，使目前版本過期。
  2. 如果資料表包含上次修改日期以及上次修改執行者資料行，設定執行修改的最新日期和處理序。
  3. 插入新的成員，其開始日期有效性資料行已設定為結束日期有效性資料行值 (用來更新舊版)，並已將目前的版本旗標設定為 TRUE。
  4. 如果資料表包含建立日期和建立者，設定執行插入的最新日期和處理序。

SCD 類型 3

偵測到 SCD 類型 3 變更時，使用類似邏輯處理 SCD 類型 1 以更新屬性。

維度成員刪除

如果來源資料表明維度成員已被刪除 (因為未從來源系統檢索它們，或它們已被標記為已刪除)，請小心。 不應與維度資料表同步刪除作業，除非維度成員是在錯誤的情況下建立的，而且沒有與之相關的事實記錄。

處理來源刪除的適當方式是將其記錄為虛刪除。 虛刪除會將維度成員標示為不再可用或有效。 為了支援此案例，您的維度資料表應該包含具有位元資料類型的布林值屬性，例如 IsDeleted。 將任何已刪除維度成員的此資料行更新為 TRUE (1)。 維度成員目前的最新版本可能類似地在 IsCurrent 或 IsActive 資料行中以布林 (位元) 值標記。 所有報告查詢和 Power BI 語意模型都應篩選掉虛刪除的記錄。

日期維度

行事曆和時間維度屬特殊案例，因為它們通常沒有來源資料。 相反，它們是使用固定邏輯產生的。

應該在每個新年度開始時載入日期維度資料表，以將其資料列向前擴展特定年數。 可能有其他商務資料，例如定期更新的會計年度資料、假日、週數。

當日期維度資料表包含相對位移屬性時，必須每天執行 ETL 處理序，才能根據目前日期 (今天) 更新位移屬性值。

建議使用 T-SQL 撰寫擴充或更新日期維度資料表的邏輯，並將其封裝在預存程序中。

處理事實資料表

處理事實資料表涉及與來源系統事實同步資料倉儲資料。 來源資料會先轉換並準備好載入其事實資料表。 然後，對於每個維度索引鍵，查找將決定要儲存在事實資料列中的 Surrogate 索引鍵值。 當維度支援 SCD 類型 2 時，應該擷取目前版本維度成員的 Surrogate 索引鍵。

注意

通常可以為日期和時間維度計算 Surrogate 索引鍵，因為它們應使用 YYYYMMDD 或 HHMM 格式。 如需詳細資訊，請參閱行事曆和時間。

如果維度索引鍵查找失敗，表示來源系統存在完整性問題。 這種情況下，事實資料列仍必須插入事實資料表中。 有效的維度索引鍵仍然必須儲存。 其中一種方法是儲存特殊的維度成員 (例如未知)。 此方法需要稍後更新，才能在已知的情況下正確指派真正的維度索引鍵值。

重要

因為 Fabric 倉儲不會強制執行外部索引鍵，因此 ETL 處理序在將資料載入事實資料表時檢查完整性非常重要。

當確信自然索引鍵有效時，另一種方法是插入新的維度成員，然後儲存其 Surrogate 索引鍵值。 如需詳細資訊，請參閱本章節稍後的推斷的維度成員。

下圖描述用於處理事實資料表的邏輯。

圖表顯示一個流程，描述如何將新的來源資料列載入事實資料表，如先前段落所述。

盡可能以累加方式載入事實資料表，這表示偵測到並插入了新的事實。 累加式載入策略的可擴縮性更強，可減少來源系統和目的地系統的工作負載。

重要

特別是對於大的事實資料表，它應該是截斷和重新載入事實資料表的最後手段。 就處理時間、計算資源以及可能對來源系統造成的干擾而言，這種方法代價高昂。 當事實資料表維度套用 SCD 類型 2 時，也涉及複雜性。 這是因為維度索引鍵查找必須在維度成員版本的有效期間內完成。

希望您可以依賴來源系統識別碼或時間戳，有效率地偵測新的事實。 例如，當來源系統可靠地按順序記錄銷售訂單時，您可以儲存擷取到的最新銷售訂單編號 (稱為高水位線)。 下一個處理序可以使用該銷售訂單編號來擷取新建立的銷售訂單，然後再次儲存擷取到的最新銷售訂單編號，以供下一個處理序使用。 也可以使用建立日期資料行來可靠地偵測新訂單。

如果無法依賴來源系統資料有效率地偵測新事實，可能可以依賴來源系統的一項功能來執行累加式載入。 例如，SQL Server 和 Azure SQL 受控執行個體具有稱為異動資料擷取 (CDC) 的功能，可追蹤資料表中每個資料列的變更。 此外，SQL Server、Azure SQL 受控執行個體和 Azure SQL 資料庫具有稱為變更追蹤的功能，可識別有變更的資料列。 啟用時，它可協助您有效率地偵測任何資料庫資料表中的新增或有變更的資料。 也可以將觸發程式新增至關係型資料表，以儲存插入、更新或刪除的資料表記錄的索引鍵。

最後，可以使用屬性將來源資料關聯至事實資料表。 例如，銷售訂單編號和銷售訂單行號。 不過，對於大型事實資料表，偵測新增、變更或刪除的事實可能是成本高昂的作業。 當來源系統封存作業資料時，也可能出現問題。

推斷的維度成員

當事實載入處理序插入新的維度成員時，即稱為推斷的成員。 例如，酒店住客辦理入住手續時，系統會要求他們加入連鎖酒店成為忠誠會員。 會員號碼會立即簽發，但住客的詳細資料可能要等到住客提交文書 (如有) 後才會發出。

關於維度成員，我們已知的只有其自然索引鍵。 事實載入處理序需要使用未知的屬性值來建立新的維度成員。 重要的是，它必須將 IsInferredMember 稽核屬性設定為 TRUE。 如此一來，當獲取遲到的詳細資訊時，維度載入處理序可以對維度資料列進行必要的更新。 如需詳細資訊，請參閱本文的管理歷史變更。

事實更新或刪除

您可能需要更新或刪除事實資料。 例如當銷售訂單取消或訂單數量變更時。 如先前的載入事實資料表所述，您需要有效率地偵測變更，然後對事實資料執行適當修改。 在這個已取消訂單的範例中，銷售訂單狀態可能會從 [開啟] 變更為 [已取消]。 這項變更需要更新事實資料，而不是刪除資料列。 對於數量變更，必須更新事實資料列數量量值。 這種使用虛刪除的策略保留了歷史記錄。 虛刪除會將資料列標示為不再作用中或有效，而且所有報告查詢和 Power BI 語意模型都會篩選掉虛刪除的記錄。

當您預期事實將更新或刪除時，應該在事實資料表中包含屬性 (例如銷售訂單編號及其銷售訂單行號)，以協助識別要修改的事實資料列。 請務必為這些資料行編製索引，以支援有效率地執行修改作業。

最後，如果使用特殊維度成員插入事實資料 (例如未知)，必須執行定期處理序，以擷取這類事實資料列的目前來源資料，並將維度索引鍵更新為有效值。

相關內容

如需將資料載入 Fabric 倉儲的詳細資訊，請參閱：