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

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

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

在維度模型中，維度資料表描述與您的商務和分析需求相關的實體。 從廣義上講，維度資料表代表您要建立模型的內容。 這些內容可以是產品、人員、地點或任何其他概念，包括日期和時間。 若要輕鬆地識別維度資料表，您通常會在其名稱前面加上 d_ 或 Dim_。

維度資料表結構

若要描述維度資料表的結構，請考慮下列名為 d_Salesperson 的銷售人員維度資料表範例。 此範例會套用良好的設計做法。 下列各節將說明每組資料行。

CREATE TABLE d_Salesperson
(
    --Surrogate key
    Salesperson_SK INT NOT NULL,
    
    --Natural key(s)
    EmployeeID VARCHAR(20) NOT NULL,
    
    --Dimension attributes
    FirstName VARCHAR(20) NOT NULL,
    <…>
    
    --Foreign key(s) to other dimensions
    SalesRegion_FK INT NOT NULL,
    <…>
    
    --Historical tracking attributes (SCD type 2)
    RecChangeDate_FK INT NOT NULL,
    RecValidFromKey INT NOT NULL,
    RecValidToKey INT NOT NULL,
    RecReason VARCHAR(15) NOT NULL,
    RecIsCurrent BIT NOT NULL,
    
    --Audit attributes
    AuditMissing BIT NOT NULL,
    AuditIsInferred BIT NOT NULL,
    AuditCreatedDate DATE NOT NULL,
    AuditCreatedBy VARCHAR(15) NOT NULL,
    AuditLastModifiedDate DATE NOT NULL,
    AuditLastModifiedBy VARCHAR(15) NOT NULL
);

Surrogate 金鑰

範例維度資料表有一個代理索引鍵，其名稱為 Salesperson_SK。 代理索引鍵是一個單資料行唯一識別碼，它產生並儲存在維度資料表中。 它是主索引鍵資料行，用來與維度模型中其他資料表產生關聯。

代理索引鍵會努力將資料倉儲與來源資料變更隔離。 它們也會提供許多其他優點，讓您能夠：

• 合併多個資料來源 (避免重複識別碼衝突)。
• 將多個資料行自然索引鍵合併到更高效的單一資料行索引鍵中。
• 使用緩慢變更維度 (SCD) 類型 2 追蹤維度歷程記錄。
• 限制事實資料表寬度進行儲存最佳化 (選取盡可能最小的整數資料類型)。

代理索引鍵資料行是建議的做法，即使自然索引鍵 (如下所述) 似乎是可接受的候選方案。 您也應該避免為索引鍵值賦予意義 (日期和時間維度索引鍵除外，如稍後所述)。

自然索引鍵

範例維度資料表有一個自然索引鍵，其名稱為 EmployeeID。 自然索引鍵是儲存在來源系統中的索引鍵。 它允許將維度資料與其來源系統建立關聯，這通常由 Extract、Load 和 Transform (ETL) 流程完成以載入維度資料表。 有時候自然索引鍵稱為商務索引鍵，其值對商務使用者可能有意義。

有時候維度沒有自然索引鍵。 這可能適用於日期維度或查閱維度，或當您透過正規化一般檔案來產生維度資料時。

維度屬性

範例維度資料表也有維度屬性，例如 FirstName 資料行。 維度屬性會為儲存在相關事實資料表中的數值資料提供內容。 它們通常是文字資料行，在分析查詢中用於篩選和分組 (切片和切塊)，但無法自行彙總。 某些維度資料表包含少數屬性，而其他則包含許多屬性 (利用盡可能多的屬性來支援維度模型的查詢需求)。

如果您打算建立 Direct Lake 語意模型，則應該包含篩選和分組所需的所有可能資料行作為維度屬性。 這是因為 Direct Lake 語意模型不支援計算結果欄。

外部索引鍵

範例維度資料表有一個外部索引鍵，其名稱為 SalesRegion_FK。 其他維度資料表可以參考外部索引鍵，而且在維度資料表中的存在是一種特殊情況。 它表示資料表與另一個維度資料表相關，這意味著它可能是雪花維度的一部分，或它與支架維度相關。

Fabric Warehouse 支援外部索引鍵條件約束，但無法強制執行它們。 因此，載入資料時，您的 ETL 程序必須測試相關資料表之間的完整性。

建立外部索引鍵仍然是個好主意。 建立非強制外部索引鍵的一個好理由是允許模型化工具 (例如 Power BI Desktop)，在語意模型中自動偵測及建立資料表之間的關聯性。

歷史追蹤屬性

範例維度資料表也有各種歷史追蹤屬性。 歷史追蹤屬性是可選的，取決於是否在來源系統中發生特定變更時進行追蹤。 它們允許儲存值來支援資料倉儲的主要作用，也就是要準確地描述過去。 具體來說，當 ETL 程序將新的或已變更的資料載入維度時，這些屬性會儲存歷史內容。

如需詳細資訊，請參閱本文稍後的管理歷史變更。

稽核屬性

範例維度資料表也有各種稽核屬性。 稽核屬性是可選的，但建議使用。 它們可讓您追蹤建立或修改維度記錄的時間和方式，而且可以包含 ETL 程序期間引發的診斷或疑難排解資訊。 例如，您會想要追蹤誰 (或哪些程序) 更新了資料列，以及何時更新。 稽核屬性也可以協助診斷具有挑戰性的問題，例如 ETL 程序意外停止時。 它們也可以將維度成員標示為錯誤或推斷的成員。

維度資料表大小

通常，維度模型中最實用且最通用的維度是大而寬的維度。 它們在資料列方面很大 (超過數百萬)，在維度屬性數目方面很寬 (可能數百個)。 大小並不重要 (雖然您應該針對可能最小的大小進行設計和最佳化)。 重要的是維度支援事實資料所需的篩選、分組和精確的歷史分析。

大型維度可能來自多個來源系統。 在此情況下，維度處理必須結合、合併、反複製以及標準化資料；並指派代理索引鍵。

相較之下，有些維度很小。 它們可能代表只包含數個記錄和屬性的查閱資料表。 這些小型維度通常會在事實資料表中儲存與交易相關的類別值，而且它們會實作為具有代理索引鍵的維度，以與事實記錄相關。

維度設計概念

本節說明各種維度設計概念。

反正規化與正規化

維度資料表應該反正規化，情況幾乎總是如此。 雖然正規化是用來描述以減少重複資料的方式來儲存資料的術語，但反正規化是用來定義預先計算的備援資料所在位置的術語。 備援資料的存在通常是由於階層的儲存 (稍後討論)，這表示階層會扁平化。 例如，產品維度可以儲存子類別 (及其相關屬性) 和類別 (及其相關屬性)。

因為維度通常很小 (相較於事實資料表)，因此儲存備援資料的成本幾乎總是超過改善的查詢效能和可用性。

雪花式維度

反正規化的一個例外是設計雪花維度。 雪花維度已正規化，並將維度資料存在數個相關資料表中。

下圖描述由三個相關維度資料表組成的雪花維度：Product、Subcategory 和 Category。

請考慮在下列情況下實作雪花維度：

• 維度非常大，儲存體成本超過高查詢效能的需求。 (然而，定期重新評估情況是否仍然如此。)
• 您需要索引鍵，將維度與更高精細度的事實產生關聯。 例如，銷售事實資料表會將資料列儲存在產品層級，但銷售目標事實資料表會將資料列儲存在子類別層級。
• 您必須更精細地追蹤歷史變更。

階層

通常，維度資料行會產生階層。 階層可讓您在不同的摘要層級探索資料。 例如，矩陣視覺物件的初始檢視可能會顯示年度銷售額，而報表取用者可以選擇 向下切入以顯示每季和每月銷售額。

有三種方式可將階層儲存在維度中。 您可以使用：

• 來自單一、反正規化維度的資料行。
• 雪花維度包含多個相關資料表。
• 維度中的父子式 (自我參考) 關聯性。

階層可以是對稱的，也可以不對稱。 也請務必了解某些階層參差不齊。

對稱階層

對稱階層是最常見的階層類型。 對稱階層的層級數目相同。 對稱階層的常見範例是日期維度中的行事歷階層，其中包含年份、季度、月份和日期的層級。

下圖描述銷售區域的對稱階層。 它包含兩個層級，也就是銷售區域群組和銷售區域。

對稱階層的層級基於單一、反正規化維度的資料行，或基於構成雪花維度的資料表之資料行。 基於單一、反正規化維度時，代表更高層級的資料行會包含備援資料。

對於對稱階層，事實一律與階層的單一層級相關，通常是最低層級。 如此一來，就可以將事實彙總 (積存) 到階層的最高層級。 事實可以與任何層級相關，由事實資料表的精細度決定。 例如，銷售事實資料表可能會儲存在日期層級，而銷售目標事實資料表可能會儲存在季度層級。

不對稱階層

不對稱階層是不太常見的階層類型。 不對稱階層具有以父子式關聯性為基礎的層級。 因此，不對稱階層中的層級數目取決於維度資料列，而不是特定的維度資料表資料行。

不對稱階層的常見範例是員工階層，其中員工維度中的每個資料列都與相同資料表中的報表管理員資料列相關。 在此情況下，任何員工都可以是具有報告員工的經理。 當然，階層的某些分支會比其他分支擁有更多層級。

下圖描述不對稱階層。 其中包含四個層級，而階層中的每個成員都是銷售人員。 請注意，銷售人員在階層中有不同數目的上階，這取決於他們向誰報告。

不對稱階層的其他常見範例包括物料單、公司擁有權模型和總帳。

對於不對稱階層，事實一律與維度精細度相關。 例如，銷售事實與具有不同報表結構的不同銷售人員相關。 維度資料表會有代理索引鍵 (名為 Salesperson_SK) 和 ReportsTo_Salesperson_FK 外部索引鍵資料行，其會參考主索引鍵資料行。 沒有任何人可管理的每個銷售人員不一定處於階層中任何分支的最低層級。 當他們不在最低層級時，銷售人員可能會銷售產品，並擁有也會銷售產品的報告銷售人員。 因此，事實資料的彙總必須考慮個別銷售人員及其所有子系。

查詢父子式階層可能會很複雜且緩慢，尤其是針對大型維度。 雖然來源系統可能會將關聯性儲存為父子關係，但建議您將階層自然化。 在此實例中，自然化意味著將維度中的階層層級轉換且儲存為資料行。

不完全階層

有時候階層是不完全的，因為階層中成員的父代存在於一個不在其正上方的階層中。 在這些情況下，遺漏的層級值會重複父代的值。

請考慮對稱地理階層的範例。 當國家/地區沒有州或省時，就會有不完全的階層。 例如，紐西蘭既沒有州也沒有省。 因此，當您插入紐西蘭資料列時，也應該將國家/地區值儲存在 StateProvince 資料行中。

下圖描述地理區域的不完全階層。

管理歷史變更

必要時，可透過實作緩時變更維度 (SCD) 來管理歷史變更。 當新的或已變更的資料載入其中時，SCD 會維護歷史內容。

以下是最常見的 SCD 類型。

• 類型 1：覆寫現有的維度成員。
• 類型 2：插入新的以時間為基礎的版本型維度成員。
• 類型 3：使用屬性追蹤有限的歷程記錄。

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

通常不會使用 SCD 類型 3，部分原因是在語意模型中難以使用。 請仔細考慮 SCD 類型 2 方法是否更適合。

SCD 類型 1

SCD 類型 1 變更會覆寫現有的維度資料列，因為不需要追蹤變更。 此 SCD 類型也可以用來更正錯誤。 這是常見的 SCD 類型，它應該用於大多數變更屬性，例如客戶名稱、電子郵件地址和其他屬性。

下圖描述銷售人員維度成員的電話號碼變更前後的狀態。

此 SCD 類型不會保留歷史資料，因為現有資料列已更新。 這表示 SCD 類型 1 變更可能會導致不同的較高層級彙總。 例如，如果銷售人員指派給不同的銷售區域，SCD 類型 1 變更會覆寫維度資料列。 將銷售人員歷史銷售結果彙總到區域會產生不同的結果，因為它現在會使用新的目前銷售區域。 就好像該銷售人員總是被指派到新的銷售區域。

SCD 類型 2

SCD 類型 2 變更會產生新的資料列，它們代表維度成員的時間型版本。 總是有一個目前的版本資料列，它反映來源系統中維度成員的狀態。 維度資料表中的歷史追蹤屬性會儲存允許識別目前版本 (目前旗標為 TRUE) 及其有效性時間週期的值。 需要代理索引鍵，因為儲存多個版本時會有重複的自然索引鍵。

這是常見的 SCD 類型，但它應該保留給必須保留歷史資料的屬性。

例如，如果銷售人員指派給不同的銷售區域，SCD 類型 2 變更會涉及更新操作和插入操作。

1. 更新操作會覆寫目前的版本，以設定歷史追蹤屬性。 具體來說，結束有效性資料行會設定為 ETL 處理日期 (或來源系統中的適當時間戳記)，而目前的旗標會設定為 FALSE。
2. 插入操作會新增新的目前版本，將開始有效性資料行設定為結束有效性資料行值 (用來更新先前的版本)，並將目前的旗標設定為 TRUE。

請務必了解相關事實資料表的細微性不在銷售人員層級，而在銷售人員版本層級。 其歷史銷售結果彙總至區域會產生正確的結果，但將會有兩個 (或更多) 銷售人員成員版本進行分析。

下圖描述銷售人員維度成員的銷售區域變更前後的狀態。 由於組織想要依指派的區域來分析銷售人員的工作，因此會觸發 SCD 類型 2 變更。

請考慮下列 SCD 類型 2 歷史追蹤屬性。

CREATE TABLE d_Salesperson
(
    <…>

    --Historical tracking attributes (SCD type 2)
    RecChangeDate_FK INT NOT NULL,
    RecValidFromKey INT NOT NULL,
    RecValidToKey INT NOT NULL,
    RecReason VARCHAR(15) NOT NULL,
    RecIsCurrent BIT NOT NULL,

    <…>
);

以下是歷史追蹤屬性的目的。

• RecChangeDate_FK 資料行會儲存變更生效的日期。 它可讓您在變更發生時進行查詢。
• RecValidFromKey 和 RecValidToKey 資料行會儲存資料列有效性的有效日期。 請考慮為 RecValidFromKey 儲存在日期維度中找到的最早日期，以代表初始版本，並為目前版本的 RecValidToKey 儲存 01/01/9999。
• RecReason 資料行是可選的。 它允許記錄插入版本的原因。 它可以對哪些屬性已變更進行編碼，或者可能是來源系統中指出特定商務原因的程式碼。
• RecIsCurrent 資料行可只擷取目前的版本。 當 ETL 程序在載入事實資料表時查閱維度索引鍵時，就會使用它。

SCD 類型 3

SCD 類型 3 變更會使用屬性追蹤有限的歷程記錄。 當需要記錄最近的變更或最近一些變更時，此方法可能會很有用。

此 SCD 類型會保留有限的歷史資料。 只有在應該儲存初始值和目前值時，它可能很有用。 在此情況中，不需要過渡性變更。

例如，如果銷售人員指派到不同的銷售區域，SCD 類型 3 變更會覆寫維度資料列。 特別儲存前一個銷售區域的資料行會設定為先前的銷售區域，而新的銷售區域會設定為目前的銷售區域。

下圖描述銷售人員維度成員的銷售區域變更前後的狀態。 因為組織想要判斷任何先前的銷售區域指派，所以會觸發 SCD 類型 3 變更。

特殊維度成員

可以在維度中插入表示遺漏、未知、N/A 或錯誤狀態的資料列。 例如，可以使用下列代理索引鍵值。

索引鍵值	用途
0	遺漏 (在來源系統中無法使用)
-1	未知 (在事實資料表載入期間查閱失敗)
-2	N/A (不適用)
-3	錯誤

行事曆和時間

事實資料表幾乎無一例外地會儲存特定時間點的度量。 若要支援依日期 (也可能是時間) 的分析，必須有行事曆 (日期和時間) 維度。

來源系統具有行事歷維度資料的情況並不常見，因此必須在資料倉儲中產生它。 一般而言，它會產生一次，如果是行事歷維度，則會視需要擴展未來日期。

日期維度

日期 (或行事曆) 維度是用於分析的最常見維度。 它會為每個日期儲存一個資料列，並支援依特定日期段 (例如年、季度或月) 篩選或分組的常見需求。

日期維度的自然索引鍵應該使用日期資料類型。 代理索引鍵應該使用 YYYYMMDD 格式和 int 資料類型來儲存日期。 當代理索引鍵值具有意義且可讀取時，此公認的做法應該是唯一的例外狀況 (與時間維度一起)。 將 YYYYMMDD 儲存為 int 資料類型不僅有效且會按數值排序，而且也符合明確的國際標準組織 (ISO) 8601 日期格式。

以下是日期維度中會包含的一些常見屬性。

• QuarterNumberInYear、MonthNumberInYear – 對文字標籤排序時可能需要它們。
• FiscalYear、FiscalQuarter – 某些公司會計排程會從年中開始，因此日曆年度和會計年度的開始/結束是不同的。
• FiscalQuarterNumberInYear、FiscalMonthNumberInYear – 對文字標籤排序時可能需要它們。
• WeekOfYear – 有多種方式可標記年度週，包括具有 52 或 53 週的 ISO 標準。
• IsHoliday、HolidayText – 如果您的組織在多個地理位置運作，您應該維護多組假日清單，每個地理位置都作為單獨的維度進行觀察，或在日期維度的多個屬性中自然化。 新增 HolidayText 屬性有助於識別報告假日。
• IsWeekday - 同樣，在某些地理位置中，標準工作週不是星期一到星期五。 例如，許多中東地區的工作週是星期日到星期四，而其他地區則採用為期四天或六天的工作週。
• LastDayOfMonth
• RelativeYearOffset、RelativeQuarterOffset、RelativeMonthOffset、RelativeDayOffset - 支援相對日期篩選時可能需要它們 (例如上個月)。 目前期間使用零 (0) 位移；先前的期間會儲存位移 -1、-2、-3...；未來期間會儲存位移 1、2、3...。

如同其他維度一樣，重要的是它包含支援已知篩選、分組和階層需求的屬性。 可能還有一些屬性會將標籤的翻譯儲存為其他語言。

當維度用來與較高精細度事實關聯時，事實資料表可以使用日期週期的第一個日期。 例如，儲存每季銷售人員目標的銷售目標資料表會將季度的第一個日期儲存在日期維度中。 替代方法是在日期資料表中建立索引鍵資料行。 例如，季度索引鍵可以使用 YYYYQ 格式和 smallint 資料類型來儲存季度索引鍵。

維度應該填入所有事實資料表所使用的已知日期範圍。 它也應該包含資料倉儲可儲存有關目標、預算或預測相關事實的未來日期。 如同其他維度一樣，可能會包含表示遺漏、未知、N/A 或錯誤情況的資料列。

一般而言，在下一年開始時，ETL 程序應該將日期維度資料列延伸至未來特定年份。 當維度包含相對位移屬性時，必須每天執行 ETL 程序，才能根據目前日期 (今天) 更新位移屬性值。

時間維度

有時候，需要在某個時間點儲存事實 (如一天中的某個時間)。 在此情況下，請建立時間 (或時鐘) 維度。 它可以精細到分鐘 (24 x 60 = 1,440 個資料列) 或甚至秒 (24 x 60 x 60 = 86,400 個資料列)。 其他可能的精細度包括半小時或小時。

時間維度的自然索引鍵應該使用時間資料類型。 代理索引鍵可以使用適當的格式，並儲存具有意義且可讀取的值，例如使用 HHMM 或 HHMMSS 格式。

以下是時間維度中會包含的一些常見屬性。

• 時段標籤 (上午、下午、傍晚、晚上)
• 工作班次名稱
• 高峰或非高峰旗標

一致維度

某些維度可能是一致維度。 一致維度與許多事實資料表相關，因此它們可由維度模型中的多個星號共用。 它們具有一致性，並可協助您減少進行中的開發和維護。

例如，事實資料表通常會儲存至少一個日期維度索引鍵 (因為活動幾乎總是依日期和時間來記錄)。 因此，日期維度是常見的一致維度。 應確保您的日期維度包含與所有事實資料表分析相關的屬性。

下圖顯示 Sales 事實資料表和 Inventory 事實資料表。 每個事實資料表都與 Date 維度和 Product 維度相關，它們都是一致維度。

另一個範例是，您的員工和使用者可能是同一組人員。 在此情況下，合併每個實體的屬性來產生一個一致維度可能很合理。

角色扮演維度

在事實資料表中多次參考維度時，稱為角色扮演維度。

例如，當銷售事實資料表有訂單日期、出貨日期和交貨日期維度索引鍵時，日期維度會以三種方式關聯。 每個方式都代表不同的角色，但只有一個實際日期維度。

下圖描述 Flight 事實資料表。 Airport 維度是角色扮演維度，因為它作為 Departure Airport 維度和 Arrival Airport 維度與事實資料表關聯兩次。

雜項維度

當有許多獨立維度時，雜項維度很有用，特別是它們包含少數幾個屬性 (或者一個)，並且這些屬性有較低的基數 (值很少)。 雜項維度的目標是將許多小型維度合併成單一維度。 此設計方法可以減少維度數目，並減少事實資料表索引鍵的數目，從而減少事實資料表儲存大小。 它們也有助於減少 [資料] 窗格雜亂，因為它們向使用者呈現較少的資料表。

雜項維度資料表通常會儲存所有維度屬性成員的笛卡兒乘積，且具有代理索引鍵屬性。

良好的候選項包括旗標和標記、訂單狀態和客戶人口統計狀態 (性別、年齡組等)。

下圖描述名為 Sales Status 的雜項維度，它結合了訂單狀態值和傳遞狀態值。

變質維度

當維度與相關事實處於同一精細度時，可能會出現變質維度。 變質維度的常見範例是與銷售事實資料表相關的銷售訂單編號維度。 發票編號通常是事實資料表中的單一、非階層屬性。 因此，不會複製此資料來建立單獨的維度資料表，這是公認做法。

下圖描述了 Sales Order 維度，它是基於銷售事實資料表中 SalesOrderNumber 資料行的變質維度。 會作為檢視來執行此維度，它可擷取不同的銷售訂單編號值。

從 Power BI 語意模型化角度來看，可以使用 Power Query，將變質維度建立為單獨的資料表。 如此一來，語意模型就符合最佳做法，即用來篩選或分組的欄位來自維度資料表，而用來總結事實的欄位則來自事實資料表。

支架維度

當某個維度資料表與其他維度資料表相關時，稱為支架維度。 支架維度可協助遵守和重複使用維度模型中的定義。

例如，您可以建立地理維度來儲存每個郵遞區號的地理位置。 然後，客戶維度和銷售人員維度可以參考該維度，這會儲存地理維度的代理索引鍵。 如此一來，就可以使用一致的地理位置來分析客戶和銷售人員。

下圖描述屬於支架維度的 Geography 維度。 它與 Sales 事實資料表不直接相關。 相反，它透過 Customer 維度和 Salesperson 維度間接相關。

請考慮當其他維度資料表屬性儲存日期時，日期維度可以用作支架維度。 例如，可以使用日期維度資料表的代理索引鍵來儲存客戶維度中的出生日期。

多值維度

當維度屬性必須儲存多個值時，必須設計多值維度。 可以透過建立橋接資料表來實作多值維度 (有時稱為聯結資料表)。 橋接資料表會儲存實體之間的多對多關聯性。

例如，假設有銷售人員維度，而且每個銷售人員都會指派給一個或多個銷售區域。 在此情況下，建立銷售區域維度是合理的。 該維度只會儲存每個銷售區域一次。 單獨的資料表 (稱為橋接資料表) 會針對每個銷售人員與銷售區域關聯性儲存一個資料列。 實際上，從銷售人員維度到橋接資料表存在一對多關聯性，從銷售區域維度到橋接資料表存在另一個一對多關聯性。 從邏輯上講，銷售人員與銷售區域之間存在多對多關聯性。

在下圖中，Account 維度資料表與 Transaction 事實資料表相關。 因為客戶可以有多個帳戶，而且帳戶可以有多個客戶，因此 Customer 維度資料表透過 Customer Account 橋接資料表相關。

相關內容

在本系列的下一篇文章中，了解事實資料表的指引和設計最佳做法。