Oracle BIEE 中的时间维与时间轴函数

累计、同比、环比是 BI 项目中常用的分析方法。本文讲述如何利用 Oracle BIEE 的时间维(Time Dimension)及时间轴函数(Time Series Conversion Function)实现累计、同比、环比计算。本文使用的示例数据可以通过此链接下载:http://www.zw1840.com/。 如果你不了解如何创建 Oracle BIEE 资料库,请参考之前的文章 Hello BIEE
 

目录

时间轴函数概述

    累计、同比、环比是 BI 项目中常用的分析方法。如果采用关系型数据库,直接通过 SQL 语句进行这样的计算是比较麻烦的。Oracle BIEE 提供的解决方法是时间轴函数。时间轴函数是位于逻辑模型层内的函数,使用时间轴函数能够定义出“去年同期值”或“本年累计值”之类的与时间相关的计算字段。Oracle BIEE 现在支持两个时间轴函数:
  • Ago:从当前时间起回溯用户指定的 n 个时间段,返回当时的度量值。
  • ToDate:从用户指定时间段的起点开始,到当前时间为止,计算度量值的累计值。
    使用时间轴函数的前提条件是在逻辑模型内定义了时间维。上面两个函数说明中的“时间段”实际上指的是时间维的层级(Level)。例如使用 Ago 函数定义“去年同期值”,指定的时间段为“年”这一层级,指定的回溯周期为 1,表示得到去年的值。再比如使用 ToDate 函数定义“本年累计值”,指定的时间段也是“年”这一层级,表示从年的起点(年初)开始到当前时间为止,求度量值的累计值。
 

创建时间维的准备工作

    时间维的创建过程与创建普通维度基本相同,只是维度对象的某些设置不同。因此创建物理模型、逻辑模型、及展现模型的过程在此不细说了,如果你不了解请参考之前的文章 Hello BIEE
 
    在物理模型层,导入时间维表 V_COMMON_DATE;将 V_COMMON_DATE.DAY_ID 定义为 V_COMMON_DATE 的主键;将 V_COMMON_DATE.DAY_ID 定义为 V_FINANCE_EXPENSE.CONSUME_DATE 的外键。
 


 


 


 


 

    在逻辑模型层,将物理表“V_COMMON_DATE”拖放到逻辑模型“Finance”内;使用“Rename Wizard”重命名;将逻辑字段“Day Id”定义为逻辑表“Date Dim”的逻辑主键;在逻辑表“Date Dim”和“Expense Fact”之间创建逻辑连接。
 


 


 


 

    在展现模型层,将逻辑表“Date Dim”拖放到展现模型“消费分析”内;只保留年、月、日的名称字段,并重命名。
 


 

    需要注意的是,Oracle BIEE 对与时间维相关的物理表及逻辑表有以下要求[6]
  • 如果某个物理表的字段被映射到了时间逻辑表上,那么这个物理表中的字段不能再映射到其他逻辑表中。
  • 作为时间逻辑表的数据源的物理表,只允许与物理事实表关联。且关联只能基于外键(foreign key),而不能基于复杂连接(complex join)。

 

创建时间维

    与创建普通维度对象一样,以逻辑表“Date Dim”为基础创建维度对象“Date”;依次定义层级:“Total Level”、“Year Level”、“Month Level”、“Day Level”,并设定层级的元素数;将逻辑表“Date Dim”的各列拖放当相应的层级;定义各层级的层键,并设定主层键,以及用于下钻的层键。
 


 

层级
Level
元素数
Number of elements
层键
Key
主层键
Primary
用于下钻
Use for drilldown)
Total Level1N/AN/AN/A
Year Level3Year IdYesNo
Year NameNoYes
Month Level36Month IdYesNo
Month NameNoYes
Day Level1000Day IdYesNo
Day NameNoYes
    时间维与普通维度有两处不同。首先,双击维度“Date”节点,在“Dimension”对话框中需要选中“Time Dimension”选项,以表明此维度对象为时间维度。
 


 

    其次,时间维度中需要定义时间序列键(Chronological Key)。时间序列键的作用是表明时间维中具有一组单调增加的时间序列值[1]。定义时间序列 时存在以下原则:
  • 时间维中,至少一个层级上应定义有时间序列键[2]
  • 物理存储的最细时间粒度上必须定义时间序列键[3]。在例子中,物理表“V_FINANCE_EXPENSEE”的日期字段“CONSUME_DATE”为日期,即 物理存储的最细时间粒度为日期,因此时间维“Date”的“Day Level”上应该定义时间序列键。
  • 除了必要的时间序列键,时间维的各个层级上均可建立额外的时间序列键,这主要是为提高查询性能服务[4]
  • 时间维的同一层级上可以建立多个时间序列键,但只有第一个时间序列键有效。[5]
    在这个简单的例子中,我只把层级“Day Level”的逻辑层键“Day Id”定义为时间序列键。
 


 

使用时间轴函数定义度量值

    定义好时间维后,就可以在逻辑事实表中定义使用时间轴函数的逻辑列了。在逻辑表“Expense Fact”上单击右键,选择“New Object\Logical Column”创建逻辑列。在“Logical Column”对话框中选择“Use existing logical columns as source”再按右侧的按钮,可以打开“Expression Builder”编辑逻辑列的公式。最后不要忘记将逻辑列添加到展现模型中。
 
    例如,使用 Ago 函数定义“去年同期值”。
 
 AGO(Finance."Expense Fact".Amount, Finance."Date"."Year Level", 1) 

    第一个参数是逻辑字段 Finance."Expense Fact".Amount,是用于时间轴运算的度量值;第二个参数是逻辑模型时间维的层级 Finance."Date"."Year Level",表明回溯的时间单位为年;第三个参数表示回溯的时间周期,1 则为回溯一年,即去年。
 


 

    再例如,使用 ToDate 函数定义“本年累计值”。
 
 TODATE(Finance."Expense Fact".Amount, Finance."Date"."Year Level") 

    第一个参数是逻辑字段 Finance."Expense Fact".Amount,是用于时间轴运算的度量值;第二个参数是逻辑模型时间维的层级 Finance."Date"."Year Level",表明累计计算开始自年时间的起点,终止到当前时间。
 


 

    在进行时间查询时应注意几个重要的粒度[7],以查询 select "Date Dim"."Month Name", "Expense Fact"."Amount Year Ago" 为例:
  • "Date Dim"."Month Name" 为查询粒度
  • "Expense Fact"."Amount Year Ago" 逻辑列对应的时间维层级为时间序列粒度
  • 在包含时间序列函数的查询中,时间序列粒度必须大于等于查询粒度。
  • "Expense Fact"."Amount Year Ago" 逻辑列对应的物理表的时间粒度被称为存储粒度
  • 与存储粒度对应的时间维层级上必须定义时间序列键。
    Ago 与 ToDate 函数可以嵌套调用,但需要遵守一定规则[8]
 
    在使用时间序列函数时也有一定限制,具体请参考 BIEE 文档[9]
 

查询

    定义如下查询,其中“消费金额(同期)”与“消费金额(年累计)”分别为使用 Ago 和 ToDate 函数创建的逻辑列:
 


 

    结果如下图所示:
 

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值