一、摘要
隨著物聯網的告訴發展，資料量呈現井噴式的增長，如何來分析和使用這些資料，使資料產生商業價值，已經變得越來越重要。值得高興的是，當前越來越多的人已經意識到了用資料分析決定商業策略的重要性，也都在進行著各行各業的資料分析。眾所周知資料分析的核心是資料，為了更容易的分析資料，資料模型的設計需要遵循一定的規範。當前最流行的聯機分析處理(OLAP)的規範為維度建模規範。本文介紹Quick BI如何進行維度建模，基於維度模型如何來自動化的生成分析查詢的SQL語句，從而使資料分析變得更容易。
關鍵字： Quick BI、OLAP、維度建模、SQL
二、維度模型的分類
OLAP（On-line Analytical Processing，聯機分析處理）根據儲存資料的方式不同可以分為ROLAP、MOLAP、HOLAP。ROLAP表示基於關聯式資料庫儲存的OLAP實現（Relational OLAP），以關聯式資料庫為核心,以關係型結構進行多維資料的表示和儲存；MOLAP表示基於多維資料儲存的OLAP實現（Multidimensional OLAP）；HOLAP表示基於混合資料儲存的OLAP實現（Hybrid OLAP），如低層用關係型資料庫儲存，高層是多維陣列儲存。接下來主要介紹基於關係型資料庫的ROLAP的建模原理。
ROLAP將多維資料庫中的表分為兩類：事實表和維度表。事實表用於儲存維度關鍵字和數值型別的事實資料，一般是圍繞業務過程進行設計，例如：銷售事實表，一般來儲存使用者在什麼時間、地點購買了產品，銷量和銷售額等資訊。維度表用於儲存維度的詳細資料，例如銷售事實表中儲存了產品維度的ID，產品維度表中儲存產品的名稱、品牌資訊，兩者通過產品ID進行關聯。
ROLAP根據事實表、維度表間的關係，又可分為星型模型(Star Schema)、雪花模型(Snowflake Schema)。
1.星型模型
星型模型它由事實表（FactTable）和維表（DimensionTable）組成。事實表中的維度外來鍵分別與相對應的維表中的主鍵相關聯，關聯之後由於形狀看起來像是一個星星，所以形象的稱為星型模型。以下示例為星型模型：其中sales_fact_1997為事實表，儲存客戶在某個時間、某個商店、購買了某個產品，購買量和銷售額的資訊，記錄的是一個下單過程。事實表sales_fact_1997通過外來鍵product_id、customer_id、time_id、store_id分別與維度表product(產品維表)、customer(客戶維表)、time_by_day(時間維表)、store(商店維表)相關聯，關聯關係為多對一關聯。
2.雪花模型
雪花模型是當有一個或多個維表沒有直接連線到事實表上，而是通過其他維表連線到事實表上時，其圖解就像一個雪花，故稱雪花模型。下面示例product（產品）維度表與product_class(產品類別)維度表通過product_class_id相關聯，關聯關係為多對一。product_class沒有與sales_fact_1997事實表直接關聯。
三、基於ROLAP模型的SQL生成原理
模型構建好了後，接下來的重點就是針對分析需求來生成滿足分析需要的SQL語句，然後將SQL語句下發到DB中來查詢資料，返回分析結果。下面通過具體的需求場景來介紹如何生成SQL語句。
1.基於星型模型(或雪花模型)生成SQL
需求場景：
按日期、產品檢視總的銷售額、銷售量，日期限定在1997年，總銷售額限定在1000元以上，結果按照總的銷售額倒序排列，看前5個。
2.生成SQL思路
1.分析需要用到的欄位和表，目標是明確查詢需要用到哪些表、表間關係、表上分組欄位、聚合欄位，確定SQL中select和from資訊。
2.分析篩選條件，目標是明確SQL中where中需過濾的值。
3.分析分組維度，目標是明確SQL中group by的欄位。
4.分析聚合後的篩選條件，目標是明確having中需要過濾的值。
5.分析需要排序的列和排序型別(升序還是降序)。
6.生成結果個數限制條件
7.根據以上資訊生成查詢SQL：
select 分組欄位、聚合欄位 from 表(含表關聯) where 篩選條件 group by 分組維度 having 聚合後的篩選條件 order by 排序資訊 結果條數限制。
3.生成SQL
按照上面的步驟，和本例子中的需求，分析查詢中的關鍵資訊（以下步驟與生成SQL思路中的步驟一一對應）
1.用到的分組欄位：the_date、product_name, 其中分組欄位the_date為日粒度，需處理為年粒度：DATE_FORMAT(`the_date` , '%Y')
聚合欄位：store_sales、unit_sales，聚合方式都為sum；
用到的表：sales_fact_1997、product、time_by_day；
表間關係：sales_fact_1997. product_id= product. product_id
sales_fact_1997. time_id= time_by_day .time_id
2.篩選條件：
the_date`= STR_TO_DATE('1997-01-01 00:00:00' ,'%Y-%m-%d %H:%i:%s')
3.分組維度：DATE_FORMAT(`the_date` , '%Y')、product_name
4.聚合後的篩選條件：SUM(`store_sales`) > 1000
5.排序：order by 聚合後的別名 desc
6.限制結果個數：limit 0,5
7.生成的SQL如下
SELECT
DATE_FORMAT(TIME_T_4_.`the_date` , '%Y') AS TIME_THE_5_ ,
PRODUCT_T_2_.`product_name` AS PRODUCT_PRODUCT_6_ ,
SUM(SALES_T_1_.`store_sales`) AS SALES_STORE_7_ ,
SUM(SALES_T_1_.`unit_sales`) AS SALES_UNIT_8_
FROM
`quickbi_test`.`sales_fact_1997` AS SALES_T_1_
LEFT JOIN `quickbi_test`.`product` AS PRODUCT_T_2_ ON SALES_T_1_.`product_id` = PRODUCT_T_2_.`product_id`
LEFT JOIN `quickbi_test`.`time_by_day` AS TIME_T_4_ ON SALES_T_1_.`time_id` = TIME_T_4_.`time_id`
WHERE
四、附錄-用到的表
下面羅列出以上示例中用到的表的建表語句，需要在 MySQL資料庫下執行，其他型別資料庫需要做一些調整。
1.sales_fact_1997表
CREATE TABLE `sales_fact_1997` (
`product_id` int(11) DEFAULT NULL,
`time_id` int(11) DEFAULT NULL,
`customer_id` int(11) DEFAULT NULL,
`promotion_id` int(11) DEFAULT NULL,
`store_id` int(11) DEFAULT NULL,
`store_sales` decimal(19,4) DEFAULT NULL,
`store_cost` decimal(19,4) DEFAULT NULL,
2.product表
CREATE TABLE `product` (
`product_class_id` int(11) DEFAULT '0',
`product_id` int(11) NOT NULL,
`brand_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`product_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`SKU` double DEFAULT NULL,
`SRP` decimal(19,4) DEFAULT '0.0000',
`gross_weight` float DEFAULT '0',
`net_weight` float DEFAULT '0',
`units_per_case` smallint(6) DEFAULT '0',
`cases_per_pallet` smallint(6) DEFAULT '0',
3.product_class表
CREATE TABLE `product_class` (
`product_class_id` int(11) NOT NULL,
`product_subcategory` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`product_category` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`product_department` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`product_family` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
4.time_by_day表
CREATE TABLE `time_by_day` (
`time_id` int(11) NOT NULL,
`the_date` date DEFAULT NULL,
`the_day` varchar(15) CHARACTER SET utf8 DEFAULT NULL,
`the_month` varchar(15) CHARACTER SET utf8 DEFAULT NULL,
`the_year` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`day_of_month` smallint(6) DEFAULT NULL,
`week_of_year` double DEFAULT NULL,
5.customer表
CREATE TABLE `customer` (
`customer_id` int(11) NOT NULL DEFAULT '0',
`account_num` double DEFAULT '0',
`lname` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`fname` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`mi` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
`address1` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`city` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`state_province` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`postal_code` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`country` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`customer_region_id` int(11) DEFAULT '0',
`phone1` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`occupation` varchar(50) CHARACTER SET utf8 DEFAULT NU
6.store表
CREATE TABLE `store` (
`store_id` int(11) NOT NULL,
`store_type` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`region_id` int(11) DEFAULT '0',
`store_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`store_number` double DEFAULT NULL,
`store_street_address` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`store_city` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`store_state` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`store_postal_code` varchar(50) CHARACTER SET utf8 DEFAULT NULL,