2- 數(shù)據(jù)建模

2.1 維度建模

2.1.1 維度建模的基本概念

2.1.1.1 事實表 (Fact Table)

• 包含業(yè)務(wù)過程的度量值
• 通常是數(shù)值型的、可累加的
• 例如: 銷售金額、數(shù)量等

2.1.1.2 維度表 (Dimension Table)

• 包含描述業(yè)務(wù)實體的屬性
• 通常是文本類型的描述性信息
• 例如: 產(chǎn)品、顧客、時間、地點等

2.1.1.3 維度 (Dimension)

• 提供分析數(shù)據(jù)的角度
• 用于過濾、分組和標(biāo)記

2.1.1.4 度量 (Measure)

• 事實表中的數(shù)值型數(shù)據(jù)
• 可以進(jìn)行數(shù)據(jù)運算(如: 求和、平均等)

2.1.2 維度建模的主要模型

2.1.2.1 星型模型 (Star Schema)

• 中心是事實表, 周圍是維度表
• 維度表直接與事實表相連
• 優(yōu)點: 查詢性能好, 易于理解
• 缺點: 可能存在數(shù)據(jù)冗余

2.1.2.2 雪花模型 (Snowflake Schema)

• 星型模型的變體, 維度表被進(jìn)一步規(guī)范化
• 減少了數(shù)據(jù)冗余, 增加了表的數(shù)量
• 優(yōu)點: 節(jié)省存儲空間, 方便維護(hù)
• 缺點: 查詢性能可能下降, 結(jié)構(gòu)復(fù)雜

2.1.2.3 星座模型 (Constellation Schema)

• 多個實時表共享維表
• 用于復(fù)雜的、多主體的數(shù)據(jù)倉庫

2.1.3 維度設(shè)計技術(shù)

2.1.3.1 緩慢變化維 (Slowly Changing Dimensions, SCD)

• 處理維度屬性隨時間變化的方法
• 類型1: 直接覆蓋舊值
• 類型2: 增加新記錄, 保留歷史
• 類型3: 增加新屬性列, 保留當(dāng)前和歷史
• 類型4: 增加歷史表
• 類型5: 結(jié)合1、2、3的混合方法

2.1.3.2 退化維度 (Degenerate Dimension)

• 存儲在事實表中的維度屬性
• 通常是業(yè)務(wù)過程產(chǎn)生的標(biāo)識符
• 例如: 訂單號、發(fā)票號等

2.1.3.3 巨型維度 (Junk Dimension)

• 將多個低基數(shù)的標(biāo)志或?qū)傩越M合成一個維度
• 減少維度數(shù)量, 簡化模型

2.1.3.4 角色扮演維度 (Role-Playing Dimension)

• 同一個維度表在事實表中扮演多個角色
• 例如: 日期維度可以是訂單日期、發(fā)貨日期等

2.1.3.5 橋接表 (Bridge Table)

• 用于處理多對多關(guān)系
• 鏈接事實表和維度表

2.1.4 維度建模的步驟

1. 選擇業(yè)務(wù)過程: 確定要建模的具體業(yè)務(wù)活動
2. 聲明粒度: 定義事實表中單條記錄代表的最小細(xì)節(jié)級別
3. 確定維度: 識別描述該業(yè)務(wù)過程的所有可能角度
4. 確定事實: 確定與該業(yè)務(wù)過程相關(guān)的可度量指標(biāo)
5. 存儲預(yù)計算結(jié)果: 在適當(dāng)?shù)那闆r下, 預(yù)先計算并存儲聚合數(shù)據(jù)

2.1.5 維度建模的最佳實踐

1. 使用代理鍵: 為每個維度表使用獨立的整數(shù)型代理鍵
2. 創(chuàng)建一致性維度: 垮多個事實表使用相同的維度定義
3. 創(chuàng)建日期維度: 包含豐富的日期相關(guān)屬性, 便于時間分析
4. 避免過度規(guī)范化: 在性能和易用性之間找平衡
5. 考慮未來的擴展性: 設(shè)計時預(yù)留空間為將來可能的變化

2.1.6 維度建模的優(yōu)勢

• 直觀易懂, 便于業(yè)務(wù)用戶理解
• 查詢性能優(yōu)秀, 特別是對于復(fù)雜的分析查詢
• 靈活性高, 易于應(yīng)對需求變化
• 支持自助式BI和OLAP分析

2.1.7 維度建模的挑戰(zhàn)

• 可能存在數(shù)據(jù)冗余
• 初次設(shè)計時可能難以確定最佳粒度
• 處理快速變化的維度可能比較復(fù)雜
• 需要權(quán)衡存儲空間和查詢性能

2.2 星型模型

星型模型是一種經(jīng)典的數(shù)據(jù)倉庫設(shè)計模式, 因其結(jié)構(gòu)類似于星星而得名 ; 它由一個中心事實表和多個圍繞它的維度表組成, 并通過外鍵關(guān)聯(lián) ;

這種簡單而直觀的結(jié)構(gòu)使其易于理解、查詢效率高, 被廣泛應(yīng)用于數(shù)據(jù)倉庫和商業(yè)智能領(lǐng)域 ;

2.2.1 組成部分

• 事實表 (Fact Table): 位于星型模型的中心, 存儲關(guān)鍵業(yè)務(wù)指標(biāo)的數(shù)值數(shù)據(jù), 例如銷售額、訂單數(shù)量、網(wǎng)站訪問量等 ; 每個事實表都與一個特定的業(yè)務(wù)過程相關(guān)聯(lián), 例如銷售、訂單、網(wǎng)站訪問等 ;
  • 事實表通常包含大量數(shù)據(jù)行, 因為他們記錄了每個業(yè)務(wù)事件的詳細(xì)信息 ;
  • 事實表的結(jié)構(gòu)相對簡單, 通常包含一下兩種類型的列:
    • 度量值 (Measures): 表示業(yè)務(wù)指標(biāo)的數(shù)值數(shù)據(jù), 例如銷售額、數(shù)量、成本、例如等 ;
    • 外鍵 (Foreign Keys): 用于鏈接到維表, 表示與該事實相關(guān)的維度信息 ;
• 維度表 (Dimension Table): 圍繞在事實表周圍, 提供關(guān)于事實的上下文, 例如時間、地點、產(chǎn)品、客戶等 ; 維度表回答了關(guān)于事實的 “誰、什么、何時、何地、為什么” 等問題 ;
  • 維度表通常包含較少的數(shù)據(jù)行, 因為他們描述的是業(yè)務(wù)體的屬性, 例如產(chǎn)品類別、地區(qū)、客戶類型等 ;
  • 維度表的結(jié)構(gòu)可以比較復(fù)雜, 可以包含多個層次結(jié)構(gòu), 例如時間維度可以包含年、季度、月、日等層次結(jié)構(gòu) ;
  • 維度表通常包含以下兩種類型的列:
    • 主鍵 (Primary Key): 唯一標(biāo)識維度表中的每一行數(shù)據(jù) ;
    • 屬性 (Attributes): 描述維度信息的文本或數(shù)值數(shù)據(jù), 例如產(chǎn)品名稱、類別、顏色、尺寸、地區(qū)名稱、郵編、客戶姓名、性別、年齡、地址等 ;

2.2.2 優(yōu)點

• 易于理解: 星型模型的結(jié)構(gòu)簡單直觀, 及時是非技術(shù)人員也能輕松理解, 方便業(yè)務(wù)人員理解數(shù)據(jù)結(jié)構(gòu)和進(jìn)行數(shù)據(jù)分析 ;
• 查詢性能高: 星型模型采用去規(guī)范化的設(shè)計, 將數(shù)據(jù)冗余存儲在維度表中, 避免復(fù)雜的多表連接, 因此查詢效率非常高 ;
• 易于擴展: 可以很容易地添加新的維度表或事實表, 以適應(yīng)不斷變化的業(yè)務(wù)需求, 例如增加新的產(chǎn)品線、開拓新的市場等 ;

2.2.3 應(yīng)用場景

星型模型適用于一下場景:

• 數(shù)據(jù)分析和報表: 星型模型非常適合用于構(gòu)建報表和儀表板, 因為它可以快速地對數(shù)據(jù)進(jìn)行切片、切塊、鉆取等操作 ;
• 商業(yè)智能(BI): 星型模型是許多BI工具的基礎(chǔ), 因為它可以提供對數(shù)據(jù)的快速訪問和分析 ;
• 數(shù)據(jù)挖掘: 星型模型可以用于構(gòu)建數(shù)據(jù)挖掘模型, 因為它可以提供結(jié)構(gòu)化的數(shù)據(jù), 方便算法進(jìn)行訓(xùn)練和預(yù)測 ;

2.2.4 示例

以一個電商網(wǎng)站的銷售數(shù)據(jù)為例, 我們可以設(shè)計一下的星型模型:

• 事實表: 銷售事實表 (FactSales)
  • 銷售額 (SalesAmount)
  • 成本 (Cost)
  • 利潤 (Profit)
  • 訂單 ID (外鍵) (OrderID)
  • 產(chǎn)品ID (外鍵) (ProductID)
  • 時間ID (外鍵) (TimeID)
  • 客戶ID (外鍵) (CustomerID)
• 維度表:
  • 訂單維度表 (DimOrders): 訂單ID (OrderID), 訂單日期 (OrderDate), 訂單狀態(tài) (OrderStatus)等
  • 產(chǎn)品維度表 (DimProducts): 產(chǎn)品ID (ProductID), 產(chǎn)品名稱 (ProductName), 產(chǎn)品類別 (ProductCategory), 產(chǎn)品價格 (Price) 等
  • 時間維度表 (DimTime): 時間ID(TimeID), 日期 (Date), 星期 (Week), 月份 (Month), 季度 (Quarter), 年份 (Year) 等
  • 客戶維度表 (DimCustomers): 客戶ID (CustomerID), 客戶姓名 (CustomerName), 客戶性別 (Gender), 年齡 (Age), 客戶地區(qū) (Region) 等

通過將這些表連接起來, 我們可以輕松地查詢各種銷售數(shù)據(jù), 例如:

• 2024 年第一季度華東地區(qū)各個產(chǎn)品類別的銷售額
• 2024 年 3 月份女性客戶購買的各個產(chǎn)品的銷售數(shù)量
• 不同年齡段客戶的平均訂單金額

2.2.5 總結(jié)

星型模型是一種簡單、高效的數(shù)據(jù)倉庫設(shè)計模型, 使用與各種數(shù)據(jù)分析和商業(yè)智能應(yīng)用 ; 其易于理解、查詢性能高和易于擴展的特性使其成為構(gòu)建數(shù)據(jù)倉庫的首選方案之一 ;

2.3 雪花模型

雪花模型是星型模型的一種擴展, 它進(jìn)一步將維度表規(guī)范化, 將具有層次關(guān)系的維度屬性分離到不同的表中, 形成類似雪花的分支結(jié)構(gòu) ;

2.3.1 定義

雪花模型是一種數(shù)據(jù)庫設(shè)計模型, 其中維度表被進(jìn)一步規(guī)范化, 形成多層結(jié)構(gòu), 看起來像雪花的形狀 ;

2.3.2 結(jié)構(gòu)組成

• 事實表
  • 位于模型的中心
  • 包含業(yè)務(wù)度量和指向主要維度表的外鍵
• 主要維度表
  • 直接與事實表相連
  • 包含部分描述性屬性和指向次級維度表的外鍵
• 次級維度表
  • 與主要維度表相連
  • 進(jìn)一步細(xì)分維度屬性

2.3.3 雪花模型的特點

• 高度規(guī)范化: 維度表被分解成多個相關(guān)的表
• 減少數(shù)據(jù)冗余: 通過規(guī)范化降低了數(shù)據(jù)重復(fù)
• 層次結(jié)構(gòu)清晰: 明確展示了維度之間的層次關(guān)系
• 靈活性: 可以詳細(xì)描述復(fù)雜的維度關(guān)系

2.3.4 設(shè)計步驟

• 創(chuàng)建星型模型: 先設(shè)計基本的星型結(jié)構(gòu)
• 識別維度層次: 確定維度內(nèi)的層次關(guān)系
• 規(guī)范化維度: 將維度表拆分成多個相關(guān)表
• 建立關(guān)系: 在拆分后的維度表之間建立關(guān)系

2.3.5 示例 以零售銷售為例:

• 事實表: 銷售事實
  • 包含: 日期ID、產(chǎn)品ID、商品ID、銷售額、銷售數(shù)量
• 主要維度表:
  • 產(chǎn)品維度: 產(chǎn)品ID、產(chǎn)品名稱、類別ID、品牌ID
  • 商店維度: 商店ID、商店名稱、城市ID
• 次級維度表:
  • 產(chǎn)品類別: 類別ID、類別名稱
  • 品牌: 品牌ID、品牌名稱
  • 城市: 城市ID、城市名稱、州ID
  • 州: 州ID、州名稱、國家ID
  • 國家: 國家ID、國家名稱

2.3.6 優(yōu)勢

• 數(shù)據(jù)一致性: 減少數(shù)據(jù)冗余, 提高了一致性
• 節(jié)省存儲空間: 規(guī)范化結(jié)構(gòu)減少了數(shù)據(jù)重復(fù)
• 維護(hù)方便: 更新某些屬性時只需要再一個地方修改
• 支持復(fù)雜的維度層次: 可以表示更復(fù)雜的維度關(guān)系

2.3.7 局限性

• 查詢性能可能下降: 需要更多的表連接操作
• 復(fù)雜度增加: 模型結(jié)構(gòu)比星型模型更復(fù)雜
• 不太直觀: 對非技術(shù)用戶來說可能較難理解

2.3.8 與星型模型的對比

• 雪花模型: 更規(guī)范化, 節(jié)省空間, 但查詢可能較慢
• 星型模型: 非規(guī)范化, 查詢性能好, 結(jié)構(gòu)簡單

2.3.9 實施考慮

• 性能優(yōu)化: 可能需要更復(fù)雜的索引策略
• 視圖: 考慮使用物化視圖來提高查詢性能
• ETL復(fù)雜性: 數(shù)據(jù)加載和轉(zhuǎn)換過程可能更復(fù)雜

2.3.10 使用場景

• 大型數(shù)據(jù)倉庫: 當(dāng)數(shù)據(jù)量巨大, 需要嚴(yán)格控制冗余時
• 復(fù)雜的維度層次: 當(dāng)維度有多層次結(jié)構(gòu)需要表示時
• 頻繁更新的維度屬性: 當(dāng)某些維度屬性經(jīng)常變化時

2.4 事實表和維度表設(shè)計

在數(shù)據(jù)倉庫設(shè)計中, 事實表和維度表是兩個最基本的概念, 采用維度模型設(shè)計 ; 事實表存儲時間度量值, 維度表描述業(yè)務(wù)實體 ; 兩者協(xié)同工作, 為分析和報表提供完整的數(shù)據(jù)視圖 ;

2.4.1 事實表設(shè)計

2.4.1.1 確定業(yè)務(wù)過程和粒度

首先需要明確要分析的業(yè)務(wù)過程是什么, 例如: 銷售、訂單、網(wǎng)站訪問等 ;

然后確定事實表的粒度, 即每行數(shù)據(jù)代表什么級別的業(yè)務(wù)事件 ;

• 粒度越細(xì), 數(shù)據(jù)越詳細(xì), 但也意味著數(shù)據(jù)量越大, 查詢銷量越低 ;
• 粒度越粗, 數(shù)據(jù)越匯總, 數(shù)據(jù)量越小, 但可能會丟失一些細(xì)節(jié)信息 ;

選擇合適的粒度需要權(quán)衡數(shù)據(jù)量、查詢性能和業(yè)務(wù)需求 ;

2.4.1.2 確定度量值

度量值是事實表中最重要的部分, 它們是可以計量的數(shù)值指標(biāo), 用于描述業(yè)務(wù)過程的關(guān)鍵特征; 常見的度量值包括:

• 數(shù)量: 例如銷售數(shù)量、訂單數(shù)量、訪問次數(shù)等 ;
• 金額: 例如銷售額、成本、利潤等 ;
• 時間: 例如訂單處理時間、頁面加載時間等 ;
• 比率: 例如轉(zhuǎn)化率、點擊率、滿意度等 ;

選擇度量值時需要考慮業(yè)務(wù)需求和數(shù)據(jù)可獲得性 ;

2.4.1.3 設(shè)計外鍵

事實表通過外鍵與維度表關(guān)聯(lián), 每個外鍵都對應(yīng)一個維度表的主鍵, 表示該事實發(fā)生在哪個維度下 ;

• 外鍵數(shù)量: 取決于事實表需要關(guān)聯(lián)的維度數(shù)量 ;
• 外鍵類型: 通常使用整數(shù)類型, 以提高查詢效率 ;

2.4.1.4 其它設(shè)計考慮

• 添加時間戳: 記錄每行數(shù)據(jù)的創(chuàng)建時間和更新時間, 方便數(shù)據(jù)跟蹤和審計 ;
• 預(yù)留擴展字段: 為未來可能增加的度量值預(yù)留空間, 提高數(shù)據(jù)模型的可擴展性 ;

2.4.2 維度表設(shè)計

2.4.2.1 確定維度

維度是描述業(yè)務(wù)實體的屬性, 例如時間、地點、產(chǎn)品、客戶等; 每個維度對應(yīng)一個維度表, 用于存儲該維度的所有可能取值 ;

2.4.2.2 設(shè)計屬性

維度表的屬性用于描述維度的特征, 例如:

• 時間維度: 年、月、日、小時、分鐘、秒等
• 地點維度: 國家、省份、城市、地區(qū)等
• 產(chǎn)品維度: 產(chǎn)品類型、名稱、品牌、型號、規(guī)格等
• 客戶維度: 客戶姓名、性別、年齡、地址、聯(lián)系方式等

選擇屬性時需要考慮業(yè)務(wù)需求和數(shù)據(jù)可獲得性 ;

2.4.2.3 設(shè)計層次結(jié)構(gòu)

一些維度可以按照層次結(jié)構(gòu)組織, 例如:

• 時間維度: 年 > 季度 > 月 > 日 > 小時 > 分鐘 > 秒
• 地點維度: 國家 > 省份 > 城市 > 區(qū)縣

層次結(jié)構(gòu)可以方便進(jìn)行多層次的鉆取分析, 例如從年度銷售額下鉆到月度銷售額, 再下鉆到每日銷售額 ;

2.4.2.4 其他設(shè)計考慮

• 使用代理鍵: 使用自增整數(shù)作為維度表的主鍵, 而不是使用業(yè)務(wù)主鍵, 可以提高查詢效率 ;
• 添加描述性字段: 為每個屬性添加簡短的描述信息, 提高數(shù)據(jù)可讀性 ;
• 處理緩慢變化維度: 對于隨時間緩慢變化的維度, 例如客戶地址, 需要采用合適的策略來處理歷史數(shù)據(jù) ;

2.4.3 總結(jié)

事實表和維度表的設(shè)計是數(shù)據(jù)倉庫建設(shè)的基礎(chǔ), 需要根據(jù)具體的業(yè)務(wù)需求和數(shù)據(jù)特點進(jìn)行設(shè)計 ;

一個良好的數(shù)據(jù)模型可以提高數(shù)據(jù)查詢效率, 方便進(jìn)行多維分析, 并支持業(yè)務(wù)決策 ;

end