本文共 2903 字,大约阅读时间需要 9 分钟。
本帖最后由 Noah 于 2015-8-20 09:22 编辑
同比(环比)在分析模型中的实现
1) 案例说明
本文整理用友医疗BQ应用开发过程中提出的部分关键问题及其解决方案。
2)实际问题
在数据统计分析项目中,经常会有指标需要做同比、环比、增长率等相关指标的对比分析。BQ平台中的语义模型又没有提供相应的方法,多维模型虽有相应的方法但用起来也不是很顺手,那有没有办法可以解决这个问题呢?
3) 问题分析
我们在做数据统计分析项目时,用的工具其实不仅仅只有BQ。做数据库设计的Power Designer、承载数据的Oracle、完成ETL的AE-DI、辅助数据处理的Excel,这些都是我们做统计分析师用到的工具。
既然有这么多工具可用,那BQ解决不了的这个问题是否可以通过其他工具解决呢。在这里建议大家在统计分析项目中注重数据库工具的应用,在这里我们用的是oracle,在其它的项目上也可能是别的数据库工具。数据库具有非常强大的统计分析功能,其在特定领域的功能甚至超越专业的统计软件。
那应用Oracle的什么功能来实现这一需求呢?——自然是SQL及其相关的功能。接下来看下面的分析。
Image 2.png (52.69 KB, 下载次数: 13)
2015-8-20 08:50 上传
图1 数据变化过程图
首先,我们从原始表如何得到①这样一个结构的结果,参考下面的语句:
SELECT 科室 , 金额 , 年份 FROM 数据表 WHERE 年份 IN (2014 , 2015)
其次,如何在①的基础上增加两个自定义列,达到②的效果呢,看下面:
SELECT 科室 , 金额 , 年份 , (如果 年份=2014 显示金额 否则 显示 0) AS 自定义(1)
SELECT 科室 , 金额 , 年份, (如果 年份=2015 显示金额 否则 显示 0) AS 自定义(2)
FROM 数据表 WHERE 年份 IN (2014 , 2015)
然后,将“自定义(1)、自定义(2)“改为”去年(金额)、本年(金额)“,同时将这两个字段按照”科室“分组做合计运算,既可以得到③的结果,具体脚本参照下面:
SELECT 科室 ,SUM(如果 年份=2014 显示金额 否则 显示 0) AS 去年(金额)
SELECT 科室 , SUM(如果 年份=2015 显示金额 否则 显示 0) AS 本年(金额)
FROM 数据表 WHERE 年份 IN (2014 , 2015)金额
GROUP BY 科室
按照上面的思路,便可以用SQL获取一个去年同期的对比指标。同理,上期环比指标也可以用类似的方法实现。当本期、同期、上期三个指标完成配置后,通过SQL公式来实现增长率就不难了。那这个构思如何与BQ平台衔接,如何具体在BQ中实现呢?
4) 具体实现
现在我们来介绍,如何通过可拖放的语义模型设计器实现上面所描述的内容。在具体操作之前,我们先看一下实际的数据结构是什么样的。
Image 3.png (65.86 KB, 下载次数: 13)
2015-8-20 08:53 上传
图2 事实表FACT_SF_LEIXING数据结构
Image 4.png (78.21 KB, 下载次数: 10)
2015-8-20 08:53 上传
图3 事实表FACT_SF_LEIXING数据示例
Image 5.png (99.81 KB, 下载次数: 20)
2015-8-20 08:54 上传
图4 日期维表DIM_DATE数据示例
Image 6.png (62.5 KB, 下载次数: 14)
2015-8-20 08:55 上传
图5 科室维表DIM_ORGGROUP数据示例
下面进入”数据处理平台->元数据管理->数据仓库建模工具“节点,然后通过工具栏中的”批量导入数据表“工具,将对应数据仓库数据源下的FACT_SF_LEIXING、DIM_DATE、DIM_ORGGROUP三张表导入到BQ系统中。此处导入数据表的目的,是方便后续在语义模型中引用这三张表,其好处是方便鼠标操作、数据库中已翻译的表名、列名可在语义模型中自动被引用。
Image 7.png (75.37 KB, 下载次数: 10)
2015-8-20 08:55 上传
图6 元数据管理配置
然后进入”数据处理平台->语义建模->语义建模工具“节点,新建一个语义模型,模型编码为”EXAMPLE01“,模型名称为”同比应用案例“。保存模型定义后,打开模型设计器,通过”选择表->元定义“节点将前面导入BQ系统的FACT_SF_LEIXING、DIM_DATE、DIM_ORGGROUP三张表引入模型,如下图所示。
Image 8.png (24.56 KB, 下载次数: 12)
2015-8-20 08:56 上传
图7 语义模型设计器-选择表
接下来进入”连接条件“,配置三个表间的链接关系【收费类型FACT_SF_LEIXING.科室PK_ORGGROUP = 科室档案DIM_ORGGROUP.科室编码DIMCODE】、【收费类型FACT_SF_LEIXING.日期编码PK_DATE = 日期维表DIM_DATE.日期编码DIMCODE】。
Image 9.png (24.83 KB, 下载次数: 14)
2015-8-20 08:57 上传
图8 语义模型设计器-连接条件
完成后进入”选择字段“,在界面下方查询参数选项卡中新建参数【iYear年份】,将”科室档案.科室“字段引入模型,然后新建【CurrentPeriod本期(金额)】字段,其表达式为:
SUM ( CASE WHEN DIM_DATE.YYYYCODE = PARAMETER ( 'iYEAR' )
THEN FACT_SF_LEIXING.JINE ELSE 0 END )
同理新建【OverPeriod同期(金额)】字段,其表达式为:
SUM ( CASE WHEN DIM_DATE.YYYYCODE = PARAMETER ( 'iYEAR' ) - 1
THEN FACT_SF_LEIXING.JINE ELSE 0 END )
Image 10.png (44.88 KB, 下载次数: 13)
2015-8-20 08:57 上传
图9 语义模型设计器-选择字段
最后在”描述器“选项卡配置查询条件,查询条件脚本参照为::
DIM_DATE.YYYYCODE = PARAMETER ( 'iYEAR' )
OR
DIM_DATE.YYYYCODE = PARAMETER ( 'iYEAR' ) - 1
Image 11.png (30.8 KB, 下载次数: 15)
2015-8-20 08:58 上传
图10 语义模型设计器-描述器
模型完成后数据预览结果如下图所示:
Image 12.png (57.36 KB, 下载次数: 13)
2015-8-20 08:59 上传
图11 指标配置同比效果图
转载地址:http://qktnx.baihongyu.com/