OBIEE 10G/11G - Period to period comparison with the AGO Function

Obiee Ceim

About

Ago is a time function that calculates the aggregated value from the current time back to a specified time period.

This function is useful for comparisons, such as Dollars compared to Dollars a Quarter Ago. For example, Ago can produce sales for every month of the current quarter and the corresponding quarter-ago sales. Multiple Ago functions can be nested if all the Ago functions have the same level argument.

You can nest exactly one ToDate and multiple Ago functions if they each have the same level argument.

Prerequisites

Syntax

AGO(<measure_expression>, <level>, <number of period>)

In that example, :

  • <measure_expression> is an expression that contains at least one measure,
  • level. Year, Month, … See below for a description
  • and <number of period> is an integer literal.

Level

11G

The level in 11G is given by a presentation hierarchy column. See below a level with the aggregate at function

Obiee 11g Aggregate At

10G

In 10g, the level is described through a path in the business model by

  • <model_id> is a model identifier,
  • <dimension_id> is a dimension identifier,
  • <level_id> is a level identifier,

The following is an example of this syntax:

AGO(model.sales.revenue + 5, model.time.month, 3)

Example

10G

You can therefore create a logical column in a logical fact table (it's not possible to use them in an answer in 10g) with for instance this expression :

AGO(SH.Salesfacts."Amount Sold", SH.TimesDim."Month", 1)

Obiee Logical Column Ago

that you can then use in a report :

Obiee Period To Period Comparison Ago Function

Log / Performance

The time to perform this simple report (without cache) is really long: 1 m 36. Because the database source definition is set on Oracle 10GR1. In this case, OBIEE performs a lot of stitch join and the performance is then really bad.

Todo: Comparison with no stitch join

If you compare it with the lag analytical function solution, it takes 10 times longer.

Obiee Ago Log

+++Administrator:310000:310012:----2009/04/15 21:08:04

-------------------- Sending query to database named orcl SH (id: <<97171>>):

select T268.CALENDAR_MONTH_DESC as c2,
     T268.CALENDAR_YEAR as c3,
     T268.TIME_ID as c5
from 
     SH.TIMES T268
order by c2


+++Administrator:310000:310012:----2009/04/15 21:08:04

-------------------- Sending query to database named orcl SH (id: <<97203>>):

select T245.AMOUNT_SOLD as c1,
     T245.TIME_ID as c2
from 
     SH.SALES T245
order by c2


+++Administrator:310000:310012:----2009/04/15 21:08:04

-------------------- Sending query to database named orcl SH (id: <<97246>>):

select T268.TIME_ID as c2,
     T268.CALENDAR_MONTH_DESC as c3
from 
     SH.TIMES T268
order by c3


+++Administrator:310000:310012:----2009/04/15 21:08:04

-------------------- Sending query to database named orcl SH (id: <<97277>>):

select T268.CALENDAR_MONTH_DESC as c1,
     sum(T245.AMOUNT_SOLD) as c2
from 
     SH.TIMES T268,
     SH.SALES T245
where  ( T245.TIME_ID = T268.TIME_ID and T268.CALENDAR_YEAR = 2000 ) 
group by T268.CALENDAR_MONTH_DESC
order by c1

Support

Values inconsistent

You have to go back in the set up of the time function the set up of the time series function AGO and TODATE and verify the Time Dimension design.

Analytics function and OBIEE times function

You can not mix an database analytic function for instance lag with an ago or a todate function otherwise you will receive this error.

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. 
[nQSError: 42015] Cannot function ship the following expression: Evaluate( LAG(%1,1) over (order by 
%2),D903.c3, case when D903.c1 is not null then D903.c1 when D903.c2 is not null then D903.c2 end ) .
 (HY000)

The query level ('Fiscal Year, Operational Year') must be a static level

[nQSError: 10058] A general error has occurred. [nQSError: 22046] To use AGO function, the query level 
('Fiscal Year, Operational Year') must be a static level. (HY000)

When you use two different hierarchy for the time dimension,

  • you can't mix two levels which come from two different hierarchies.
  • or you must create only one level

ORA-12801: error signaled in parallel query server

When the level key is a function column, you may have this error:

[nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 12801, message: 
ORA-12801: error signaled in parallel query server P000 ORA-01722: invalid number at OCI call OCIStmtExecute ....

The chronological column must have its values stored in a table column.





Discover More
Obiee Period To Period Comparison Lag
Blog - Obiee 10G – Period to period comparison with the analytical function Lag/Lead

During this thread on the Obiee forum, Goran point out a solution for a period to period comparison...
Saw Object
OBIEE - BI Presentation Service (SAW/OBIPS)

AnswersInteractive DashboardsDelivers The Oracle BI Presentation Service Server: is a pure Web Environment visualize data from the Oracle BI Server through the creation of Interactive Dashboards...
Obiee Period To Period Comparison Lag
OBIEE - Period to period comparison (YAGO, MAGO) with the analytical function Lag/Lead

lag and lead are analytical functions that can be used to get the value of a column in a previous/next row. More ... densewith sparse datadense use the Obiee Ago Function or densify your data ...
Exp Builder Ago Td
OBIEE - Time Series Conversion Functions : AGO and TODATE

Time series functions in OBIEE. The implementation of the time serie function is based on the time dimension. ( See set up time dimensions) At query time, the Oracle BI Server then generates SQL that...



Share this page:
Follow us:
Task Runner