复杂分析场景，SQL or MDX ？

提起 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 方案能够帮您轻松处理，从而更好的专注与业务数据的分析。 

关于作者：

刘文政，毕业于南京大学，OLAP 数据分析从业者，Kyligence 高级开发工程师