BI 前端实践 11：多维分析使用有层次的维度

原创于 2021-08-26 12:11:38 发布 · 475 阅读

本内容遵循CC 4.0 BY-SA版权协议

标签

#bi #商业智能 #多维分析 #维度

64 篇文章

订阅专栏

本文介绍了在BI实践中如何处理具有层次关系的维度，如产品分类的多级类别和日期中的年、月、日。通过在元数据中定义维度层次，可以将隐含的维度明晰化，便于多维分析。文章详细阐述了如何在元数据中定义维度层次，使用SQL和假表来处理无直接关联的维度，以及如何在多维分析界面中应用这些定义。

有些维度有层次关系，比如产品的多级类别；年、月、日；国、省、市，这几个有层次关系的维度有可能存储在同一个字段中，比如005006008中包含了三级类别的编码，日期字段中则隐含了年维、月维。通过在语义层中预定义这些层次关系，把隐含的维度明晰定义出来，以方便界面中做多维分析。

修改测试库，给产品表增加分类ID、引入日期两列。一个产品可以归类为一级类别、二级类别、三级类别三种，分类ID的编码特点是每3位代表一个级别。

各级分类的名称在产品分类表(product_category)中定义：

产品表中的分类字段，虽然存储的是三级类别的编码，但它能直接直接计算出一级、二级类别，相当于这一个维度字段实际上还隐含了一、二级类别这两个维度。日期字段，也有这个特点，实际上隐含着年份、季度、月份这些维度(再细分的话，还包含星期几、时、分、秒等相对不常用的维度)。

这些隐含的、可以被计算出来的新维度也就是维度的层次，简称维层。因为这些隐含的、有层次维在多维分析中也常用，所以有必要在元数据中预先定义出来。

最后再说明一下，这种用一个字段存储多个有层次的维度，并非唯一的库表设计方式，之所以存在，往往是为了简化设计。采用常规的独立维表设计时，三级类别分别存储在category1, category2, category3三个表中，通过外键关联：

年、季度、月份这些日期时间相关的维度，虽然也能采用独立维表设计，但实际项目中没人这么做，都是惯例的存储一个整体的日期时间值，需要哪个维度再用它计算出来。

在介绍具体操作之前，先明确下DQL元数据的中对维的定义，维必须起源于某个表的主键。但数据库中表不完全遵循这个规则，如上面的各级产品类别、年、月、日这些维表在数据库中就不存在。这时，元数据中通过SQL表、假表这些逻辑表来实现对维的明确定义。

先看产品分类，多个级别混合的存储在了产品分类表中，可以用带条件的SQL拆分出各个级别 (ID长度为3、6、9的记录分别属于一、二、三级分类)：

产品表中的分类字段是三级分类ID，所以用它外键到三级分类这个SQL表上。

注意，这时一级分类、二级分类的SQL表虽然创建了，但它们还是孤立存在的，还没和其它表关联到一起，而且也确实找不到合适的外键字段关联它们。

追根溯源，它们是由三级分类这个维计算出来的，那还是要在这个维上定义层函数关联它们，层函数中用“?”指代三级分类这个原始维：

元数据中这些明确的维度、层函数的定义，都会对应到DQL语法上，看个例句：

SELECT

count(含批次号产品名) 产品数

ON 二级分类

FROM 产品表 BY 分类#二级分类

这实际上已经是一个分组查询了，按照二级分类维度(ON 二级分类)，汇总产品数量，而维度值的实际数据来源于维度字段#层函数(BY 分类#二级分类)。

层函数中的表达式，会出现在翻译好的SQL中：

SELECT

SUBSTR(T_1.category_id,1,6) `二级分类`

,count(CONCAT(T_1.product_name,'(',T_1.batch_id,')')) `产品数`

FROM product T_1

GROUP BY SUBSTR(T_1.category_id,1,6)

保存元数据，观察多维分析界面上的变化，展开分类字段时，除了三级分类自身，还多了它所计算出来的一、二级维层表，可以同时选出这些维做分析：

引入日期字段只存储了一个日期，数据库中没有日期维表 (产品分类的多级维，至少还有个混合在一起的维表)。这时用假表定义年、年月、日期三个维就可以：

之后的步骤就一样了，产品表的引入日期外键到日期假表：

日期维通过层函数计算出形如“2021”、“202103”的年维、年月维。

在多维分析界面用这些维做分析：

着重说明一下假表的用法，不要因为上面的例子，把假表局限在有层次的维上。随便一个普通字段，如果想定义成维度，在分析时当做分组(DQL语法体系中，只有明确定义了的维度才能用于分组)，而数据库中又没有其相应的维表，就可以使用假表，比如性别字段：

分析时可以当做分组了：