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

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs) > OBIEE - Logical Sql

1 - 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.

Advertising

3 - Prerequisites

4 - 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.

4.1 - Level

4.1.1 - 11G

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

4.1.2 - 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)
Advertising

5 - Example

5.1 - 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)

that you can then use in a report :

6 - 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 perform a lot of stitch join and the performance are then really bad.

Comparision with no stitch join

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

+++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

7 - Support

7.1 - 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.

7.2 - 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)
Advertising

7.3 - 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

7.4 - 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.