MDX 是什么
- MDX (Multidimension eXpressions) 是一种 OLAP 多维数据集的查询语言,最初由 Microsoft 于 1997 年作为 OLEDB for OLAP 规范引入,随后集成在 SSAS 中。目前,在 OLAP 数据库中被广泛采用。
MDX 查询语法示例如下:
select <Axis Expr>[ ,<Axis Expr>] from [cube] where <set>
SQL 是什么?
SQL (Structured Query Language) 是一种用于管理关系型数据库的编程语言,包含 DQL(查询)、DML(增删改)、DDL(定义修改元数据) 和 DCL(权限、事务控制)。为了方便阐述和 MDX 的区别,本文只涉及 SQL 的查询部分。
SQL 查询语法示例如下:
select <column expr>[, <column expr>] from [table] where <expr>
MDX 查询 vs SQL 查询
区别
MDX 选择的主体,即 select 部分,是维度度量或其表达式。SQL 选择的主体是列或列的表达式。
MDX 查询的主体,即 from 部分,是多维数据集(Cube),是提前 join 和聚合好的数据,查询时不需要指定 join 关系。SQL 查询的主体是关系表(table),是一条条的明细记录,查询时需要指定表之间的 join 关系。
联系
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 所不支持的。
总结
- MDX 和 SQL 都是在 OLAP 查询中经常使用的语言,主流的 BI 厂商都提供对两种接口的支持。两者的差异在于:
- 第一点,MDX 查询对应的是多维视图,而 SQL 对应的是关系视图,在聚合查询的语法上 MDX 要简单许多。
- 第二点,MDX 接口暴露的语义模型更加丰富和业务友好,而 SQL 接口暴露的语义模型相对简陋,需要后续再定义。
- 第三点,MDX 计算表达能力更加丰富,能够更好的支持复杂分析场景。