博客 > 技术博客

复杂分析场景，SQL or MDX ？

刘文政

2019年 12月 29日

提起 SQL，相信从事过数据分析相关工作的同学，对此都不陌生。在零售、银行、物流等行业，业务往往会有复杂的分析需求，如半累加，多对多，时间窗口分析等，SQL 在处理这些场景时，就有些捉襟见肘了。那有什么方案能够轻松应对呢 ? 答案就是： MDX

本文将从基本概念、BI 语义模型和分析场景来介绍 MDX 与 SQL 的区别。看完之后，相信您会更加了解为什么 MDX 比 SQL 加适合复杂分析场景。

MDX 和 SQL 基本概念

MDX (Multidimension eXpressions) 是一种 OLAP 多维数据集的查询语言，最初由 Microsoft 于 1997 年作为 OLEDB for OLAP 规范引入，随后集成在 SSAS 中。目前，在 OLAP 数据库中被广泛采用。

MDX 查询语法示例如下：

select < Axis Expr >[ ,< Axis Expr >]  
from [cube]  
where < set >

SQL (Structured Query Language) 是一种用于管理关系型数据库的编程语言，包含 DQL(查询)、DML(增删改)、DDL(定义修改元数据) 和 DCL(权限、事务控制)。为了方便阐述和 MDX 的区别，本文只涉及 SQL 的查询部分。

SQL 查询语法示例如下：

select < column expr >[, < column expr>] 
from [table] 
where < expr>

MDX 和 SQL 查询的主要区别：

a. MDX 选择的主体，即 select 部分，是维度度量或其表达式。SQL 选择的主体是列或列的表达式。

b. MDX 查询的主体，即 from 部分，是多维数据集（Cube），是提前 join 和聚合好的数据，查询时不需要指定 join 关系。SQL 查询的主体是关系表（table），是一条条的明细记录，查询时需要指定表之间的 join 关系。

MDX 与 SQL 的联系：

MDX 在很多情况下是可以等同于 SQL 的，比如需要查询 2019年所有省份的电子产品的销售额。

用 MDX 表示为：

select [Region].[Province].members  
from [Sales]  
where ([Time].[Year].[2019], [Product].[Category].[Electronic Prodcut])

用 SQL 表示为：

select region.province from sales  
join region on sales.region_id = region.id  
join time on sales.time_id = time.id 
join product on sales.product_id = product.id 
where time.year = 2019 and product.category = "Electronic Prodcut"

BI 语义模型

当前，主流的 BI 产品（Tableau, Power BI，Qlik等）都支持通过 SQL 接口（JDBC/ODBC）连接关系数据库，也支持 MDX 接口（XMLA）连接多维数据库。但 BI 通过两种接口获取到的语义模型有较大的差异，下面将具体介绍。

MDX 语义模型包含维度（维度别名），度量（度量别名），层级结构等，无需分析师在 BI 端再对模型进行业务语义的定义，这样的好处是建模师可以在OLAP工具中统一定义业务用户分析时使用的语义模型，而业务在使用 BI 工具分析时无需理解底层表结构，直接使用同步到 BI 工具的维度、度量、层级结构、计算度量等进行分析。

另外 MDX 对复杂分析场景的控制能力比 SQL 更强，对于一些复杂场景如半累加、时间窗口分析、多对多关系等，MDX 都可以通过简单的表达式来处理。而同样的逻辑使用 SQL 就需要使用非常复杂的查询才能实现，有些场景甚至无法简单通过 BI 发送的 SQL 查询来实现。

SQL 语义模型

仅包含源表和源列，需要分析师 /业务用户手动定义表的模型关联关系，维度的友好名称，度量的友好名称及聚合类型，层级结构的源列顺序等。这些完成后才能进行正常的业务分析，这样的好处是终端用户可针对分析需求灵活的进行数据建模，但同时也要求用户对底层数据结构有一定的理解理解。

MDX实现的复杂分析场景

库存分析，是制造、零售和物流行业等经常遇到的分析场景。其中，库存量是一个半累加度量，即在时间维度上不具备累加性，但是在其他维度具备累加性。

假设，库存的记录如下，需要获取每月所有产品期初（月的第一天）和期末（月的最后一天）的库存总量。

我们按照分析需求，得到的结果应该如下：

如果使用 SQL，查询表达式如下：

select `year`, `month`, 
sum(case when `day of month` = 1 then inventory else 0 end) as "Inventory on first day of the month", 
sum(case when day(last_day(`year` || '-' || `month` || '-' || `day of month`) = `day of month` then inventory else 0 end) as "Inventory on last day of the month" 
from inventory group by `year`, `month`

如果使用 MDX，需要先定义计算度量（包含的基础度量 [Measuers].[库存]=sum(inventory)），如下：

[Measures].[期初库存] = ([Time].[Month].currentMember.firstChild, [Measures].[库存])

[Measures].[期末库存] = ([Time].[Month].currentMember.lastChild, [Measures].[库存])

MDX 查询表达式为：

select {[Measures].[期初库存], [Measures].[期末库存]} on Columns,  
[Time].[Month].members on Rows 
from [inventory]

由上可见，在库存分析场景中，MDX 比 SQL 更容易实现。类似的场景还有银行业常见的账户余额分析，证券行业常见的期初期末值分析等。另外，MDX 还能够支持对多分析场景，这是 SQL 所不支持的。

Kyligence MDX: 支撑企业部署统一的 BI 语义层

Kyligence 提供的 AI 增强型大数据平台同时为 BI 用户提供了 SQL 以及 MDX 标准接口，可无缝集成市面主流 BI，提供统一的基于大数据的业务语义层，MDX 的接口。

为企业实现企业级业务语义层提供了技术可能性，并可满足更多 SQL 很难满足的复杂分析场景。

总结

MDX 和 SQL 都是在 OLAP 查询中经常使用的语言，主流的 BI 厂商都提供对两种接口的支持。两者的差异在于：

第一点，MDX 查询对应的是多维视图，而 SQL 对应的是关系视图，在聚合查询的语法上 MDX 要简单许多。

第二点，MDX 接口暴露的语义模型更加丰富和业务友好，而 SQL 接口暴露的语义模型相对简陋，需要后续再定义。

第三点，MDX 计算表达能力更加丰富，能够更好的支持复杂分析场景。

总的来说，如果业务上有复杂的分析场景需求（银行、零售、物流等传统行业经常遇见）如半累加，多对多，时间窗口分析等，有统一的BI语义层需求时，Kyligence MDX 方案能够帮您轻松处理，从而更好的专注与业务数据的分析。

复杂分析场景，SQL or MDX ？

MDX 和 SQL 基本概念

Kyligence MDX: 支撑企业部署统一的 BI 语义层

总结

码上时刻｜通过逻辑视图 Logic View 快速实现批流一体

头部银行 AI 落地实践｜数据应用赋能经营管理闭环

精准铺货、动态调整，指标平台让零售饮料企业掌握线下渠道主动权

把简单留给用户，把复杂交给 AI

头部房企｜数据驱动工程精细化运营，稳中提效

AI 驱动的企业数据文化：塑造未来业务智能与卓越决策

创业7年复盘，中美企业服务市场差异浅析

AI 时代的数据与分析市场变化

想突破转化瓶颈？安排上 AI ，比漏斗图更高效、更实用！

复杂分析场景，SQL or MDX ？

MDX 和 SQL 基本概念

Kyligence MDX: 支撑企业部署统一的 BI 语义层

总结

您可能会感兴趣

阅读下一篇

码上时刻｜通过逻辑视图 Logic View 快速实现批流一体

头部银行 AI 落地实践｜数据应用赋能经营管理闭环

精准铺货、动态调整，指标平台让零售饮料企业掌握线下渠道主动权

把简单留给用户，把复杂交给 AI

头部房企｜数据驱动工程精细化运营，稳中提效

AI 驱动的企业数据文化：塑造未来业务智能与卓越决策

创业7年复盘，中美企业服务市场差异浅析

AI 时代的数据与分析市场变化

想突破转化瓶颈？安排上 AI ，比漏斗图更高效、更实用！