共用方式為

Microsoft網狀架構倉儲中的維度模型化：載入數據表

  • 發行項

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

注意

本文構成文章維度模型系列的一部分。 本系列著重於Microsoft網狀架構倉儲中維度模型化的相關指引和設計最佳做法。

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

注意

在本文中，數據倉儲一詞 是指企業數據倉儲 ，可全面整合整個組織的重要數據。 相反地，獨立字詞 倉儲 是指網狀架構倉儲，這是一種軟體即服務 （SaaS） 關係資料庫供應專案，可供您用來實作數據倉儲。 為了清楚起見，本文中將後者稱為 網狀架構倉儲。

提示

如果您不熟悉維度模型化，請考慮這一系列文章您的第一個步驟。 它的目的不是提供關於維度模型設計的完整討論。 如需詳細資訊，請參閱 Ralph Kimball 等廣泛採用的已發佈內容，例如 數據倉儲工具組：維度模型 化的最終指南（2013 年第 3 版，2013 年）。

載入維度模型

載入維度模型牽涉到定期執行擷取、轉換和載入 （ETL） 程式。 ETL 程式會協調執行其他進程，這通常與暫存源數據有關、同步處理維度數據、將數據列插入事實數據表，以及記錄稽核數據和錯誤。

針對網狀架構倉儲解決方案，您可以使用 Data Factory 來開發和執行 ETL 程式。 此程式可以將源數據暫存、轉換及載入維度模型數據表。

具體而言，您可以：

  • 使用 數據管線 來建置工作流程來協調 ETL 程式。 數據管線可以執行 SQL 腳本、預存程式等等。
  • 使用 數據流 來開發低程式碼邏輯，從數百個數據源擷取數據。 數據流支持結合多個來源的數據、轉換數據，然後將它載入目的地，例如維度模型數據表。 數據流是使用目前可在許多Microsoft產品上使用的熟悉 Power Query 體驗所建置，包括 Microsoft Excel 和Power BI Desktop。

注意

ETL 開發可能很複雜，開發可能很困難。 據估計，60-80% 的數據倉儲開發工作是專用於 ETL 程式。

協調流程

ETL 程式的一般工作流程是：

  1. 選擇性地 載入臨時表。
  2. 處理維度數據表。
  3. 處理事實數據表。
  4. 或者，執行後續處理工作，例如觸發相依網狀架構內容的重新整理（例如語意模型）。

圖表顯示 ETL 程式的四個步驟，如上一段所述。

必須先處理維度數據表，以確保它們會儲存所有維度成員，包括自上次 ETL 程式之後新增至來源系統的成員。 當維度之間有相依性時，如同 使用外部維度的情況，應該依相依性的順序處理維度數據表。 例如，客戶維度所使用的地理位置維度和廠商維度應該在另外兩個維度之前處理。

處理所有維度數據表之後，即可處理事實數據表。

處理所有維度模型數據表時，您可能會觸發相依語意模型的重新整理。 傳送通知給相關人員，以通知他們 ETL 程式的結果也是個好主意。

暫存數據

暫存源數據可協助支持數據載入和轉換需求。 它牽涉到擷取來源系統數據，並將它載入至臨時表，您可以建立該數據表以支援 ETL 程式。 建議您暫存源數據，因為它可以：

  • 將操作系統的影響降到最低。
  • 用來協助和優化 ETL 處理。
  • 提供重新啟動 ETL 程式的能力，而不需要從來源系統重載數據。

暫存數據表中的數據不應該提供給商務使用者使用。 它只與 ETL 程序相關。

注意

當您的數據儲存在 Fabric Lakehouse 時，可能不需要將數據暫存於數據倉儲中。 如果實作 獎章架構，您可以從銅層、銀級或金層來源數據。

建議您在倉儲中建立架構，可能命名為 staging。 臨時表在數據行名稱和數據類型方面應該盡可能類似源數據表。 每個數據表的內容都應該在 ETL 程式開始時移除。 不過，請注意，網狀架構倉儲數據表 無法截斷。 相反地，您可以卸除並重新建立每個臨時表，再載入數據。

您也可以將數據虛擬化替代方案視為預備策略的一部分。 您可以使用：

轉換資料

源數據的結構可能不像維度模型數據表的目的地結構。 因此，您的 ETL 程式必須重新調整源數據，以配合維度模型數據表的結構。

此外，數據倉儲必須提供已清理和符合的數據，因此可能需要轉換源數據，以確保品質和一致性。

注意

垃圾傳入的概念 、 垃圾當然適用於數據倉儲，因此，請避免將垃圾（品質低）數據載入維度模型數據表。

以下是 ETL 程式可執行的一些轉換。

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

載入資料

您可以使用下列 數據擷取選項，在網狀架構倉儲中載入數據表。

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

提示

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

記錄

ETL 程式通常需要專用的監視和維護。 基於這些原因，我們建議您將 ETL 程式的結果記錄到倉儲中的非維度模型數據表。 您應該為每個 ETL 進程產生唯一識別碼，並用它來記錄每個作業的詳細數據。

請考慮記錄：

  • ETL 程式：
    • 每個 ETL 執行的唯一識別碼
    • 開始時間和結束時間
    • 狀態（成功或失敗）
    • 發生的任何錯誤
  • 每個暫存和維度模型資料表：
    • 開始時間和結束時間
    • 狀態（成功或失敗）
    • 插入、更新和刪除的數據列
    • 最終數據表數據列計數
    • 發生的任何錯誤
  • 其他作業：
    • 語意模型重新整理作業的開始時間和結束時間

提示

您可以建立專用於監視和分析 ETL 程式的語意模型。 程序持續時間可協助您找出可能受益於檢閱和優化的瓶頸。 數據列計數可讓您瞭解每次執行 ETL 時累加負載的大小，也有助於預測數據倉儲的未來大小（以及適當時相應增加網狀架構容量的時機）。

處理維度數據表

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

下圖描述用來處理維度數據表的邏輯。

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

請考慮維度數據表的程式 Product 。

  • 當新產品新增至來源系統時，數據列會 Product 插入維度數據表中。
  • 修改產品時，維度數據表中的現有數據列會更新或插入。
    • 當 SCD 類型 1 套用時，會對現有的數據列進行更新。
    • 當套用 SCD 類型 2 時，會更新以 讓目前的數據列版本過期 ，並插入代表目前版本的新數據列。
    • 當套用 SCD 類型 3 時，會發生類似 SCD 類型 1 的程式，更新現有的數據列而不插入新的數據列。

Surrogate 索引鍵

我們建議每個維度數據表都有 Surrogate 索引鍵，其應該使用最小的可能整數數據類型。 在通常藉由建立身分識別數據行來完成的 SQL Server 型環境中，但網狀架構倉儲不支援此功能。 相反地，您必須使用產生唯一 標識符的因應措施技術 。

重要

當維度數據表包含自動產生的 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）。 目前、最新版本的維度成員可能同樣會以 或 IsActive 數據行中的IsCurrent布爾值標示。 所有報告查詢和 Power BI 語意模型都應該篩選掉虛刪除的記錄。

日期維度

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

您應該在每個年開始時載入 日期維度 數據表，以將其數據列延伸至未來特定年份。 可能有其他商務數據，例如會計年度數據、假日、周數，以定期更新。

當日期維度數據表包含相對位移屬性時，必須每天執行 ETL 程式，以根據目前日期（今天）更新位移屬性值。

建議您將擴充或更新日期維度數據表的邏輯寫入 T-SQL，並封裝在預存程式中。

處理事實數據表

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

注意

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

如果維度索引鍵查閱失敗，表示來源系統發生完整性問題。 在此情況下，事實數據列仍必須插入事實數據表中。 仍然必須儲存有效的維度索引鍵。 其中一種方法是儲存 特殊維度成員 （例如 Unknown）。 當已知時，此方法需要更新，才能正確指派真正的維度索引鍵值。

重要

因為網狀架構倉儲不會強制執行外鍵，因此 ETL 程式在將數據載入事實數據表時檢查完整性非常重要。

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

下圖描述用來處理事實數據表的邏輯。

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

盡可能以累加方式載入事實數據表，這表示會偵測並插入新的事實。 累加式載入策略可更擴充，可減少來源系統和目的地系統的工作負載。

重要

特別是對於大型事實數據表，它應該是截斷和重載事實數據表的最後手段。 這種方法在處理時間、計算資源以及來源系統的可能中斷方面非常昂貴。 當事實數據表維度套用 SCD 類型 2 時，它也牽涉到複雜性。 這是因為維度索引鍵查閱必須在維度成員版本的有效期間內完成。

希望您可以依賴來源系統標識碼或時間戳，有效率地偵測新的事實。 例如，當來源系統可靠地記錄順序中的銷售訂單時，您可以儲存擷取的最新銷售訂單號碼（稱為 高水印）。 下一個程式可以使用該銷售訂單號碼來擷取新建立的銷售訂單，然後再次儲存下一個程式擷取以供使用的最新銷售訂單號碼。 您也可以 使用建立日期數據 行來可靠地偵測新訂單。

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

最後，您可以使用屬性，將源數據與事實數據表相互關聯。 例如，銷售訂單號碼和銷售訂單明細編號。 不過，對於大型事實數據表，偵測新的、變更或刪除的事實可能是非常昂貴的作業。 當來源系統封存作業數據時，也可能有問題。

推斷的維度成員

當事實載入進程插入新的維度成員時，即稱為 推斷成員。 例如，當酒店客人簽入時，系統會要求他們以忠誠成員身分加入旅館連鎖店。 立即發出成員資格號碼，但在來賓提交檔之前，來賓的詳細數據可能不會遵循（如果有的話）。

所有關於維度成員的已知都是其自然索引鍵。 事實載入程式需要使用未知的屬性值來建立新的維度成員。 重要的是，它必須將 audit 屬性設定IsInferredMember為 TRUE。 如此一來，當延遲抵達的詳細數據來源時，維度載入程式就可以對維度數據列進行必要的更新。 如需詳細資訊，請參閱 本文中的管理歷程記錄變更 。

事實更新或刪除

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

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

最後，如果使用特殊維度成員插入事實數據（例如 Unknown），您必須執行定期程式，以擷取這類事實數據列的目前源數據，並將維度索引鍵更新為有效值。

相關內容

如需將數據載入網狀架構倉儲的詳細資訊，請參閱：