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

实践目标

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

认识维度的层次

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

..

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

..

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

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

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

..

        

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

元数据中定义维度的层次

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

..

        

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

..

         产品表中的分类字段是三级分类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语法体系中,只有明确定义了的维度才能用于分组),而数据库中又没有其相应的维表,就可以使用假表,比如性别字段:

..

         分析时可以当做分组了:

..