上一篇文章我們簡(jiǎn)單聊了SSAS技術(shù)，今天我們將從基本概念、BI 語(yǔ)義模型和分析場(chǎng)景來(lái)介紹 MDX 與 SQL 的區(qū)別?？赐瓯疚闹?，相信您會(huì)更加了解為什么 MDX 比 SQL 加適合復(fù)雜分析場(chǎng)景。相信從事過數(shù)據(jù)分析相關(guān)工作的小伙伴，對(duì)于SQL都不陌生。在各行各業(yè)往往都會(huì)有復(fù)雜的分析需求，如半累加，多對(duì)多，時(shí)間窗口分析等，編寫SQL語(yǔ)句在處理這些分析場(chǎng)景時(shí)就有些捉襟見肘。而這些分析場(chǎng)景的處理使用MDX語(yǔ)法是最合適的。下面我們來(lái)一起看一些具體對(duì)比分析。

一、MDX 和 SQL 基本概念

1.1、MDX 是什么？

MDX ，多維表達(dá)式(Multidimension eXpressions) 是一種 OLAP 多維數(shù)據(jù)集的查詢語(yǔ)言，類似于SQL的數(shù)據(jù)庫(kù)查詢語(yǔ)言。最初由微軟在1997 年作為 OLEDB for OLAP 規(guī)范引入，隨后集成在 SSAS 技術(shù)中。MDX僅僅只是一個(gè)微軟的規(guī)范，并不是一個(gè)開放的標(biāo)準(zhǔn)。但是這個(gè)規(guī)范正在被越來(lái)越多的OLAP服務(wù)提供商所采用。這正是MDX生命力頑強(qiáng)的體現(xiàn)。

一個(gè)基本的MDX Select語(yǔ)句樣例如下：

with member [Measures].[DoubleAmount] as '[Measures].[Sales Amount] * 2'

select [Account].[Accounts].Levels(1).AllMembers on columns,

{[Measures].[Sales Amount],? [Measures].[DoubleAmount]} on rows

from [Adventure Works]

where ([Date].[Fiscal].[Fiscal Year].&[2002])

“with” 語(yǔ)句部分；樣例中的第1行。該語(yǔ)句用于獲取數(shù)據(jù)前的預(yù)處理。在預(yù)處理的過程中，我們可以定義各種各樣的計(jì)算，這些計(jì)算包括可命名集合和計(jì)算成員。在本樣例中，我們定義的是計(jì)算成員。

通過分析這個(gè)MDX語(yǔ)句樣例，我們可以看出MDX語(yǔ)句有以下幾個(gè)部分：

“select” 語(yǔ)句部分；樣例中的第2，3行。該語(yǔ)句用于選擇可獲取的數(shù)據(jù)。

“from” 語(yǔ)句部分；樣例中的第4行。該語(yǔ)句用來(lái)定義我們從哪個(gè)數(shù)據(jù)塊獲取數(shù)據(jù)。

“where” 語(yǔ)句部分；樣例中的第5行。該語(yǔ)句用來(lái)切割數(shù)據(jù)塊，并合計(jì)數(shù)據(jù)。

在這4個(gè)部分中，我們可以根據(jù)自己的需求寫出很復(fù)雜的MDX語(yǔ)句以獲得相應(yīng)的數(shù)據(jù)。

1.2、SQL 是什么？

SQL，結(jié)構(gòu)化查詢語(yǔ)言 (Structured Query Language) 是一種用于管理關(guān)系型數(shù)據(jù)庫(kù)的編程語(yǔ)言，包含 DQL(查詢)、DML(增刪改)、DDL(定義修改元數(shù)據(jù)) 和 DCL(權(quán)限、事務(wù)控制)。SQL的應(yīng)用范圍也非常廣，本文為了更好讓小伙伴們了解SQL和MDX的區(qū)別，本文只涉及兩種語(yǔ)言的查詢部分的對(duì)比。一個(gè)基本的SQL Select語(yǔ)句樣例如下：：

select * from 數(shù)據(jù)表 where字段名?= 字段值 ?group by 字段名 order by 字段名 [desc]"。

通過分析這個(gè)sql語(yǔ)句樣例，我們可以看出SQL語(yǔ)句有以下幾個(gè)部分：

select返回用指定的條件在一個(gè)數(shù)據(jù)庫(kù)中查詢的結(jié)果

WHERE子句篩選出滿足條件的行集；

GROUP BY子句對(duì)行集中的行做分組合并，使得多個(gè)行對(duì)應(yīng)于結(jié)果集中的一行；

應(yīng)用ORDER BY子句對(duì)結(jié)果集中的行排序。

二、MDX 和 SQL 查詢方式的主要區(qū)別

2.1、 MDX Select 部分是維度度量或其表達(dá)式。

SQL Select 部分是列或列的表達(dá)式。

2.2、MDX From部分是多維數(shù)據(jù)集（Cube），是提前關(guān)聯(lián)并聚合好的數(shù)據(jù)，查詢時(shí)不需要指定關(guān)聯(lián)關(guān)系。

? ? SQL From部分是關(guān)系表（Table），是一條條的明細(xì)記錄，查詢時(shí)需要指定表之間的關(guān)聯(lián)關(guān)系。

注意：MDX 和SQL雖然有語(yǔ)法區(qū)別，但是它們?cè)诤芏嗲闆r下是可以等同于的，舉個(gè)例子我們需要查詢Adventure Works 公司 2004年不同銷售渠道的銷售額。

用 MDX 表示為：

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS ) } ?ON ROWS FROM ?[Adventure Works] WHERE ( [Date].[Fiscal Year].&[2004] )

用 SQL 表示為：

select case when OnlineOrderFlag=1 then 'Internet' ?

? ? ? ? ? else 'Reseller' end ?[Sales Channel],sum(LineTotal) [Sales Amount] from [Sales].[SalesOrderDetail] ?t1 left join [Sales].[SalesOrderHeader] t2 on t1.SalesOrderID=t2.SalesOrderID where year(t2.OrderDate)='2004'

? ? group by case when OnlineOrderFlag=1 then 'Internet' ?

? ? ? ? ? else 'Reseller' end

三、BI 語(yǔ)義模型

上一篇文章中我們也提到了目前主流的 BI 產(chǎn)品（Tableau, Power BI，Qlik等）都支持通過 SQL 接口（JDBC/ODBC）連接關(guān)系數(shù)據(jù)庫(kù)，也支持 MDX 接口（XMLA）連接多維數(shù)據(jù)庫(kù)。但 BI 通過兩種接口獲取到的語(yǔ)義模型有較大的差異下面將具體介紹兩者的差異性。下面使用的工具有SE-DWA高效數(shù)倉(cāng)搭建平臺(tái)和數(shù)據(jù)分析工具。

3.1、MDX 語(yǔ)義模型包含維度（維度別名），度量（度量別名），維度層級(jí)結(jié)構(gòu)等，數(shù)據(jù)分析師在 BI 端不需要對(duì)模型進(jìn)行業(yè)務(wù)語(yǔ)義的定義，這樣的好處是建模工程師可以在OLAP工具中統(tǒng)一定義業(yè)務(wù)用戶分析時(shí)使用的語(yǔ)義模型，而業(yè)務(wù)在使用 BI 工具分析時(shí)無(wú)需理解底層表結(jié)構(gòu)，直接使用同步到 BI 工具的維度、度量、層級(jí)結(jié)構(gòu)、計(jì)算度量等進(jìn)行分析。

MDX 對(duì)復(fù)雜分析場(chǎng)景的控制能力比 SQL 更強(qiáng)，對(duì)于一些復(fù)雜場(chǎng)景如半累加、時(shí)間窗口分析等，MDX 都可以通過簡(jiǎn)單的表達(dá)式來(lái)處理。而同樣的邏輯使用 SQL 就需要使用非常復(fù)雜的查詢才能實(shí)現(xiàn)，有些場(chǎng)景甚至無(wú)法簡(jiǎn)單通過 BI 發(fā)送的 SQL 查詢來(lái)實(shí)現(xiàn)。

SE-DWA建立的OLAP模型在Tableau中同步OLAP模型

3.2、SQL 語(yǔ)義模型

數(shù)據(jù)庫(kù)包含源表和源列，需要分析師 /業(yè)務(wù)用戶手動(dòng)定義表的模型關(guān)聯(lián)關(guān)系，維度的友好名稱，度量的友好名稱及聚合類型，層級(jí)結(jié)構(gòu)的源列順序等。這些完成后才能進(jìn)行正常的業(yè)務(wù)分析，這樣的好處是終端用戶可針對(duì)分析需求靈活的進(jìn)行數(shù)據(jù)建模，但同時(shí)也要求用戶對(duì)底層數(shù)據(jù)結(jié)構(gòu)有一定的理解。

SE-DWA建立的數(shù)倉(cāng)模型在 Tableau 中基于SQL模式的表進(jìn)行建模

3.3、MDX實(shí)現(xiàn)的復(fù)雜分析場(chǎng)景

財(cái)務(wù)分析，是各行各業(yè)中都需要進(jìn)行分析場(chǎng)景。其中，應(yīng)收賬款年初金額是一個(gè)半累加度量，即在時(shí)間維度上不具備累加性，但是在其他維度具備累加性。為了方便大家理解小黎子在下面進(jìn)行模擬應(yīng)收賬款的記錄如下，需要獲取每年所有客戶年初（年的第一月）和年末（年的最后一月）的應(yīng)收款的總額。

我們按照分析需求，得到的結(jié)果應(yīng)該如下：

如果使用 SQL，查詢表達(dá)式如下：

如果使用 MDX，需要先定義計(jì)算度量（基礎(chǔ)度量 [Measuers].[科目余額_本位幣期初金額]]=sum(本位幣期初金額])，基礎(chǔ)度量 [Measuers].[科目余額_本位幣期末金額]]=sum(本位幣期

末金額])），如下：

科目余額_本位幣年初金額=([Measures].[科目余額_本位幣期初金額],[維度_日期].[會(huì)計(jì)期間].[會(huì)計(jì)期間].&[1])

科目余額_本位幣年末金額 = ([Measures].[科目余額_本位幣期末金額],[維度_日期].[會(huì)計(jì)期間].[會(huì)計(jì)期間].&[12])

MDX 查詢表達(dá)式為：

select {[Measures].[科目余額_本位幣年初金額], [Measures].[科目余額_本位幣年末金額 ]} on Columns, ?[維度_日期].[會(huì)計(jì)月份].members on Rows from [SE_財(cái)務(wù)解決方案_多維數(shù)據(jù)集]

由上可見在分析場(chǎng)景中MDX 比 SQL 更容易實(shí)現(xiàn)。這個(gè)場(chǎng)景的復(fù)雜度并不高，有些小伙伴可能還是認(rèn)為SQL更好統(tǒng)計(jì)分析。其實(shí)，類似的場(chǎng)景還有其他的常見分析場(chǎng)景，比如賬戶余額分析，倉(cāng)庫(kù)的庫(kù)存分析等。另外，MDX 還能夠支持多對(duì)多分析場(chǎng)景，這是 SQL 所不支持的。

四、SE-DWA支撐企業(yè)部署的BI 語(yǔ)義層

SE-DWA提供了數(shù)據(jù)倉(cāng)庫(kù)的搭建平臺(tái)可集成市面主流 BI，提供統(tǒng)一的基于大數(shù)據(jù)的業(yè)務(wù)語(yǔ)義層。為企業(yè)實(shí)現(xiàn)企業(yè)級(jí)業(yè)務(wù)語(yǔ)義層提供了技術(shù)可能性，并可滿足更多 SQL 很難滿足的復(fù)雜分析場(chǎng)景。

SE-DWA中定義日期語(yǔ)義表

SE-BI + SE-DWA OLAP模型 分析大數(shù)據(jù)場(chǎng)景下產(chǎn)品收入的同比環(huán)比

五、總結(jié)

MDX 和 SQL 都是在 OLAP 查詢中經(jīng)常使用的語(yǔ)言，主流的 BI 廠商都提供對(duì)兩種接口的支持。兩者的差異在于：

1、MDX 查詢對(duì)應(yīng)的是多維視圖，而 SQL 對(duì)應(yīng)的是關(guān)系視圖，在聚合查詢的語(yǔ)法上 MDX 要簡(jiǎn)單許多。

2、MDX 的語(yǔ)義模型更加豐富和業(yè)務(wù)友好，而 SQL 的語(yǔ)義模型相對(duì)簡(jiǎn)陋，需要后續(xù)再定義。

3，MDX 計(jì)算表達(dá)能力更加豐富，能夠更好的支持復(fù)雜分析場(chǎng)景。

綜上所述，如果業(yè)務(wù)上有復(fù)雜的分析場(chǎng)景需求如半累加，時(shí)間窗口分析等，SE-DWA創(chuàng)建的OLAP方案能夠幫您輕松處理，從而更好的專注與業(yè)務(wù)數(shù)據(jù)的分析。?