數據倉庫學習與實踐（二）- 如何做好數據模型設計

我們知道，需求總是不斷變化的，數據倉庫也一樣，統計分析的需求也多種多樣，並非所有需求都能事先預知，在這樣的前提下，作為數據倉庫的建設著，唯一能做的就讓系統有能夠應對變化的能力，以不變應萬變。

那麼，如何做到以不變應萬變呢？

做好數據模型建設，可能是手段之一，也是最重要的手段之一。

本文基於關係型資料庫（RDBMS），介紹目前數據倉庫主流建模理念維度建模的核心要點，主要包含如下幾個方面：

1. 選擇業務流程

2. 維度建模基本概念

選擇業務流程

上一篇文章我們提到了，數據倉庫是為了輔助決策的，與業務流程（Business Process）息息相關。所以建設數據模型的首要任務便是選擇業務流程，為數據倉庫的建立提供指導方向，這樣才能反過來為業務提供更好的決策數據支撐，讓數據倉庫價值的最大化。

一個企業的運作，或者一個成熟的產品，一般都會多個業務流程相互配合而完成，它們彼此協作，相輔相成。對於每個業務流程，都需要進行獨立的數據建模，將業務系統中的 ER 模型轉化為數據倉庫中的維度數據模型，以便更好的查詢與分析。

為了方便理解，本文選取電商網站的訂單業務流程作為案例，闡述維度數據模型建設的一些要點。

維度建模基本概念

首先解釋維度建模兩個基本概念，一個是事實表（Fact Table），一個是維度表（Dimension Table）。

事實表

事實表一般由兩部分組成，維度（Dimension）和度量（Measurement）。

事實表可以通俗的理解為「什麼人在什麼時間做了什麼事」的事實記錄或者場景上下文，擁有最大的數據量，它是業務流程的核心體現。比如訂單的場景：

張三於2017年5月6日在亞馬遜花費1w元買了某款筆記本電腦

記錄了張三購買筆記本電腦的場景，主人公是張三，購買的商品是筆記本電腦，發生的時間是2017年5月6日。

而度量則是可量化的，比如這裡的1w元。度量在統計分析是一般會參與求和、求平均之類的運算。

以本文的訂單流程為例，一個簡化的事實表可以設計成這樣：

create table `f_orders` (n user_id int not null default 0, # 用戶IDn product_id int not null default 0, # 商品IDn address_id int not null default 0, # 地址IDn date_id int not null default 0, # 日期IDn promotion_id int not null default 0, # 促銷IDn sales float not null, # 商鋪訂單價格n primary key (n `user_id`, `product_id`, `address_id`,n `date_id`, `promotion_id`n )n);n

在這個表中，其主鍵為一個聯合主鍵，由各個維度的外鍵組成，外鍵不能為空值。事實表一般不包含非數字類型欄位，雖然數據量大，但佔用的空間並不大，保證更高的查詢效率。

維度表

維度表用於對事實表的補充說明，描述和還原事實發生時的場景。

如本文訂單的例子，我們定義了用戶、商品、地址、時間、促銷5個維度，通過這5個維度還原訂單發生時的場景，什麼人在什麼時間在什麼地方購買了什麼商品，以及購買該商品的促銷方式。

對於每一個維度而言，都有若干個屬性來描述，比如用戶有性別、年齡、所在地等信息。這些維度的屬性就是之後數據統計的依據，比如我們可以統計不同性別，不同年齡，不同地區在訂單中的差異，從向用戶制定更精細的營銷策略。

事實表的粒度

在事實表選取的時候，我們需要選擇最細粒度的操作數據作為事實表。因為其他所有的統計數據都可以根據這樣的事實表通過統計計算生成，也只有使用最細粒度數據作為事實表，數據倉庫才具有最大的靈活性和可擴展性，滿足未知的查詢統計需求，從而做到以不變應萬變。

維度的設計

反範式設計

所以在維度建模中，維度表一般採取反範式的設計，在一張維度表中扁平化的存儲維度的屬性，盡量避免使用外鍵。

比如商品（d_products）這張維度表，在三範式的設計中，一般商品的分類作為單獨的分類表而存在，然後在商品表使用外鍵關聯。

但維度建模一般直接將分類放在商品維度表上，比如這樣：

create table `d_products` (n id int not null, # 主鍵n category1 varchar(255) not null, # 一級分類n category2 varchar(255) not null, # 二級分類n name varchar(255) not null, # 商品名稱n primary key(`id`)n);n

這裡直接使用 category1 和 cateory2 存儲商品的一級和二級分類。

日期維度

日期維度是所有事實表都不可或缺的一個維度，日期維度將原本的一個簡單時間戳進行多方位的分解，比如按年、按月、按周等，讓統計分析更靈活高效。

比如說，這是一張最簡單的日期維度表：

create table `d_dates` (n id int not null, # 主鍵，一般存儲形式是 YYMMDD，如20170506n year int not null, # 年n mouth int not null, # 月n week int not null, # 周n day int not null, # 日n primary key(`id`)n);n

通過與日期維度的聯合查詢，我們可以方便進行按年、按月、按周和按天的統計分析。

空值的處理

在業務系統中，如果相關聯的信息為空，我們一般採用 0 或者 null 表示，但在數據倉庫中，這並不是一個好的方式，因為會讓查詢的編寫變得困難，尤其在各種條件相互組合的時候，很難保證結果的正確性。

比如本文訂單場景的促銷維度（promotion_id），大部分商品可能都沒有關聯的促銷信息。在數據倉庫中，我們的處理方式是在促銷維度表中添加一行特殊的記錄，表示沒有促銷。然後事實表的 promotion_id 外鍵則指向這條記錄。然後通過如下的方式查詢：

select n count(*) as 無促銷總數nfrom f_orders as orderninner join d_promotions as promotionn on promotion.id=order.promotion_idnwhere promotion.type=Nonen

還能方便的組合其他維度，實現更多樣化的統計查詢需求。

統計查詢與分析

進行了緯度建模之後，我們就可以通過 SQL 就行統計查詢分析了。

對於基於關係型資料庫的數據倉庫系統，由於反範式的應用，事實表趨於扁平，只需要掌握簡單的 SQL 聯合與聚合查詢，變成完成很大一部分分析需求。比如要查詢「獲取2017年 Computer 分類每個月的銷售量和銷售額」，我們可以通過如下的 SQL 完成：

select n date.mouth as 月份,n count(*) as 銷售量, n sum(order.sale) as 銷售額nfrom f_order as orderninner join d_date as date n on date.id=order.date_idninner join d_product as productn on product.id=order.product_idnwhere date.year=2017 and product.category1=Computer ngroup by date.mouthn

這樣的 SQL 很簡單，稍加修改便實現成各種各樣的統計分析需求，而且對編寫著要求不高，只需要其理解業務流程的各個維度就可以了，無需了解原始數據結構，降低了統計分析的難度，不需要專業開發人員參與。

經過維度建模後形成事實表與維度表已經高度標準化，我們還可以更進一步開發元數據系統和BI 報表系統，讓一些常規的統計分析工作可配置化，從而無需開發人員參與。

本文到此結束，更多精彩文章，盡在「代碼寫詩」，微信搜索「代碼寫詩」公眾號即可關注。

------------------------------------

本文首發於「代碼寫詩」微信公眾號：https://mp.weixin.qq.com/s?__biz=MzI4MjAwMTk0OA==&mid=2247483682&idx=1&sn=223ea3fc544b6cfdbd7f705b97697bd7

推薦閱讀：