Oracle BIEE 时间函数 (ago,todate).docx

Oracle BIEE 中的时间维与时间轴函数累计、同比、环比是 BI 项目中常用的分析方法。本文讲述如何利用 Oracle BIEE 的时间维（Time Dimension）及时间轴函数（Time Series Conversion Function）实现累计、同比、环比计算。本文使用的示例数据可以通过此链接下载：/。 如果你不了解如何创建 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/AYear Level3Year IdYesNoYear NameNoYesMonth Level36Month IdYesNoMonth NameNoYesDay Level1000Day IdYesNoDay 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 ObjectLogical 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 逻辑列对应的物理表的时间粒度被称为存储粒度。 与存储