同比数据模型环比_同比（环比）在分析模型中的实现

本帖最后由 Noah 于 2015-8-20 09:22 编辑

同比(环比)在分析模型中的实现

1) 案例说明

本文整理用友医疗BQ应用开发过程中提出的部分关键问题及其解决方案。

2)实际问题

在数据统计分析项目中&＃xff0c;经常会有指标需要做同比、环比、增长率等相关指标的对比分析。BQ平台中的语义模型又没有提供相应的方法&＃xff0c;多维模型虽有相应的方法但用起来也不是很顺手&＃xff0c;那有没有办法可以解决这个问题呢&＃xff1f;

3) 问题分析

我们在做数据统计分析项目时&＃xff0c;用的工具其实不仅仅只有BQ。做数据库设计的Power Designer、承载数据的Oracle、完成ETL的AE-DI、辅助数据处理的Excel&＃xff0c;这些都是我们做统计分析师用到的工具。

既然有这么多工具可用&＃xff0c;那BQ解决不了的这个问题是否可以通过其他工具解决呢。在这里建议大家在统计分析项目中注重数据库工具的应用&＃xff0c;在这里我们用的是oracle&＃xff0c;在其它的项目上也可能是别的数据库工具。数据库具有非常强大的统计分析功能&＃xff0c;其在特定领域的功能甚至超越专业的统计软件。

那应用Oracle的什么功能来实现这一需求呢&＃xff1f;——自然是SQL及其相关的功能。接下来看下面的分析。

Image 2.png (52.69 KB, 下载次数: 13)

2015-8-20 08:50 上传

图1 数据变化过程图

首先&＃xff0c;我们从原始表如何得到①这样一个结构的结果&＃xff0c;参考下面的语句&＃xff1a;

SELECT 科室 , 金额 , 年份 FROM 数据表 WHERE 年份 IN (2014 , 2015)

其次&＃xff0c;如何在①的基础上增加两个自定义列&＃xff0c;达到②的效果呢&＃xff0c;看下面&＃xff1a;

SELECT 科室 , 金额 , 年份 , (如果 年份&＃61;2014  显示金额 否则 显示 0) AS 自定义(1)

SELECT 科室 , 金额 , 年份, (如果 年份&＃61;2015  显示金额 否则 显示 0) AS 自定义(2)

FROM 数据表 WHERE 年份 IN (2014 , 2015)

然后&＃xff0c;将“自定义(1)、自定义(2)“改为”去年(金额)、本年(金额)“&＃xff0c;同时将这两个字段按照”科室“分组做合计运算&＃xff0c;既可以得到③的结果&＃xff0c;具体脚本参照下面&＃xff1a;

SELECT 科室 ,SUM(如果 年份&＃61;2014  显示金额 否则 显示 0) AS 去年(金额)

SELECT 科室 , SUM(如果 年份&＃61;2015  显示金额 否则 显示 0) AS 本年(金额)

FROM 数据表 WHERE 年份 IN (2014 , 2015)金额

GROUP BY 科室

按照上面的思路&＃xff0c;便可以用SQL获取一个去年同期的对比指标。同理&＃xff0c;上期环比指标也可以用类似的方法实现。当本期、同期、上期三个指标完成配置后&＃xff0c;通过SQL公式来实现增长率就不难了。那这个构思如何与BQ平台衔接&＃xff0c;如何具体在BQ中实现呢&＃xff1f;

4) 具体实现

现在我们来介绍&＃xff0c;如何通过可拖放的语义模型设计器实现上面所描述的内容。在具体操作之前&＃xff0c;我们先看一下实际的数据结构是什么样的。

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数据示例

下面进入”数据处理平台->元数据管理->数据仓库建模工具“节点&＃xff0c;然后通过工具栏中的”批量导入数据表“工具&＃xff0c;将对应数据仓库数据源下的FACT_SF_LEIXING、DIM_DATE、DIM_ORGGROUP三张表导入到BQ系统中。此处导入数据表的目的&＃xff0c;是方便后续在语义模型中引用这三张表&＃xff0c;其好处是方便鼠标操作、数据库中已翻译的表名、列名可在语义模型中自动被引用。

Image 7.png (75.37 KB, 下载次数: 10)

2015-8-20 08:55 上传

图6 元数据管理配置

然后进入”数据处理平台->语义建模->语义建模工具“节点&＃xff0c;新建一个语义模型&＃xff0c;模型编码为”EXAMPLE01“&＃xff0c;模型名称为”同比应用案例“。保存模型定义后&＃xff0c;打开模型设计器&＃xff0c;通过”选择表->元定义“节点将前面导入BQ系统的FACT_SF_LEIXING、DIM_DATE、DIM_ORGGROUP三张表引入模型&＃xff0c;如下图所示。

Image 8.png (24.56 KB, 下载次数: 12)

2015-8-20 08:56 上传

图7 语义模型设计器-选择表

接下来进入”连接条件“&＃xff0c;配置三个表间的链接关系【收费类型FACT_SF_LEIXING.科室PK_ORGGROUP &＃61; 科室档案DIM_ORGGROUP.科室编码DIMCODE】、【收费类型FACT_SF_LEIXING.日期编码PK_DATE &＃61; 日期维表DIM_DATE.日期编码DIMCODE】。

Image 9.png (24.83 KB, 下载次数: 14)

2015-8-20 08:57 上传

图8 语义模型设计器-连接条件

完成后进入”选择字段“&＃xff0c;在界面下方查询参数选项卡中新建参数【iYear年份】&＃xff0c;将”科室档案.科室“字段引入模型&＃xff0c;然后新建【CurrentPeriod本期(金额)】字段&＃xff0c;其表达式为&＃xff1a;

SUM ( CASE  WHEN  DIM_DATE.YYYYCODE  &＃61;  PARAMETER  ( &＃39;iYEAR&＃39; )

THEN  FACT_SF_LEIXING.JINE   ELSE  0  END  )

同理新建【OverPeriod同期(金额)】字段&＃xff0c;其表达式为&＃xff1a;

SUM ( CASE  WHEN  DIM_DATE.YYYYCODE  &＃61; PARAMETER ( &＃39;iYEAR&＃39; )  -  1

THEN  FACT_SF_LEIXING.JINE   ELSE  0  END  )

Image 10.png (44.88 KB, 下载次数: 13)

2015-8-20 08:57 上传

图9 语义模型设计器-选择字段

最后在”描述器“选项卡配置查询条件&＃xff0c;查询条件脚本参照为&＃xff1a;&＃xff1a;

DIM_DATE.YYYYCODE &＃61; PARAMETER ( &＃39;iYEAR&＃39; )

OR

DIM_DATE.YYYYCODE &＃61; PARAMETER ( &＃39;iYEAR&＃39; )  -  1

Image 11.png (30.8 KB, 下载次数: 15)

2015-8-20 08:58 上传

图10 语义模型设计器-描述器

模型完成后数据预览结果如下图所示&＃xff1a;

Image 12.png (57.36 KB, 下载次数: 13)

2015-8-20 08:59 上传

图11 指标配置同比效果图