BI 前端实践 7:预定义语义层改善 SQL 做多维分析

实践目标

         偶尔用SQL做多维分析会比较方便,但如果经常做这类事务,每次都可能写重复的SQL,就会存在麻烦,通过使用预定义的语义层改善这些问题。

基于SQL做多维分析的烦恼

l  数据库中的表名、字段名几乎都是英文,甚至是缩略字母,每次都需要用AS转成中文的业务名。

l  时不时会有一些计算字段,用到时每次都要编写计算表达式。

l  事实表中只有代码值,对应的名称值经常是存储在另外的维表中,每次要写JOIN

预定义元数据语义层

打开编辑元数据的设计器({安装目录}/report/bin/dql.exe),然后新建一个元数据,

..

         新建好了这个空白的元数据,下一步要做的是把之前的Mysqlemployees导入进来,创建好数据源并且连接上:

..

         连接上数据库后,选择这个库的表导入元数据:

..

         可以看到选出的多个表,选中某个表,看到它的字段,以及主键信息、字段类型也都自动导入了,如果和实际不符,可以再手动调整。

..

         这样一个最基本的元数据就建好了,保存成文件(employees.lmd)。接下来把它部署到一个独立的服务器(DQL Server)上后,就能基于它直接做多维分析了。

把元数据部署到DQL server

         DQL Server的配置目录是: {安装目录}/report/services/,这个目录下每一个文件夹是一个单独的DQL服务,系统中自带了一个叫datalogic的服务,复制一下它,改成employees服务:

..

编辑这个目录下的server.xml,增加employees服务:

..

         把上面做好的元数据文件employees.lmd拷贝到employees服务的conf目录下:

..

         编辑这个服务的配置文件service.xml,主要改三个地方:指明元数据文件时employees.lmd;指明连接的数据库类型是MYSQL;正确配置employees库的JDBC信息:

..

         这样就成功添加了这个新的服务,运行{安装目录}/report/bin/startDQLSERVER.bat,启动后看到employees服务注册成功:

..

        

         到这一步,就认为用上面这个逻辑服务器(DQL Server),把Mysql数据库服务器给包装起来了。通常Tomcat直接连MysqlSQL获得数据,现在变成了连DQL Server,在多维分析界面做分析的时候,直接选这些表字段获得数据,下面继续看操作流程。

连接DQL Server做多维分析

         以前tomcat应用中集成了润乾报表,在WEB-INF/raqsoftConfig.xml中添加过MysqlemployeesJDBC数据源,在相同的位置再增加上DQL JDBC,数据源起名为lmd4Employees,主要修改url(jdbc:datalogic://127.0.0.1:3366/employees)driver(com.datalogic.jdbc.LogicDriver)type(16)user(root)password(root)这五个属性:

..

         配置了DQL数据源,重启tomcat,分析页面JSP就可以直接用它了:

..

         元数据中所有的表都列到分析备选区域;下图基于元数据直接统计了部门的人数和最迟入职时间:

 ..

这样,基本上实现了业务人员自助地做多维分析,不依赖技术人员手写SQL了。下面继续针对一些细节做改善。

设置元数据里的中文语义

       数据库中英文的表名、字段名对业务人员不友好,把元数据(conf/employees.lmd)里的表、字段设置成中文:

..

         修改后重启DQL server,刷新多维分析页面,看到中文名生效了:

..

元数据里增加计算字段

         选中员工表的伪字段标签页,然后添加伪(计算)字段,把姓、名连起来合成姓名:

..

    修改后重启DQL server,刷新多维分析页面,看到员工表中有姓名字段了:

 

..

维的代码列预关联显示列减少JOIN

    很多维字段都是代码,在界面中分析时,看起来不直观:

..

    可以用字典文件 (*.dct) 给代码列对应上显示列,解决这个不方便,通过元数据直接生成字典:

..

    字典中给需要的维项设置显示列:

..

    保存好的字典,在 JSP 中通过 js 代码引入:

..

    再刷新页面,就能看到分组的部门编号显示成部门名称了:

..

    进一步观察部门编号的显示值设置:

..

    发现根据字典中设置的显示列,自动设置了部门显示值,是这个设置让报表中显示了部门名称:

..

    也可以再设置回“无显示值”,看到报表中又显示成了部门编号:

..

    全部的有代码 / 名称对应关系的维,在字典中都预关联上,需要维名称列做的 JOIN 就可以避免掉了。

给字段预设显示格式

一些日期、数值类型的字段需要调整显示格式,在字典中可以预设:

..

    预设后做分析,查找每个部门第一任经理的上任、离任日期,能看到上任日期按照字典里设置的显示格式显示了,而离任日期未设置,则显示成了默认的显示格式:

..