Post date: 15-Jan-2010 12:27:07

Ref: http://gerardnico.com/wiki/dat/obiee/presentation_service/obiee_period_to_period_ago_todate

http://gerardnico.com/wiki/dat/obiee/bi_server/design/start

To use the OBIEE - Time Series Conversion Functions : AGO and TODATE on a particular dimension, you have to designate the dimension as a Time Dimension and set one or more keys at one or more levels as Chronological keys. This identifies the dimension as having a monotonically increasing value in time (corresponds to chronological order).

The dimension must be check as a Time Dimension.

When you do that the column “Chronological Key” appear in each key tab of each level...

Chronological Key Configuration

This chronological key is really important for the function AGO and TD of OBIEE because the software use them to order the month and to know what is by example the third last period. If you want to have a good consistency, you must so check this box with a realchronological key.

It is required that you define a chronological key at the lowest level (in most case for the day) that can be used to answer your time series query. It is recommended that you define additional chronological keys at other relevant levels for performance reasons.

Example :

Example of implementation on the SH repository

First, you have to check the Time Dimension check box.

Then you have to choose the chronological keys column as show the above table.

gerardnico@orcl>connect sh/sh Connected. sh@orcl>desc times  Name                              Null?    Type            Example of Value  --------------------------------- -------- --------------- ----------------  TIME_ID                           NOT NULL DATE              CALENDAR_QUARTER_DESC             NOT NULL CHAR(7)         2000-04, 2000-03  CALENDAR_YEAR                     NOT NULL NUMBER(4)       2000, 2001  CALENDAR_MONTH_DESC               NOT NULL VARCHAR2(8)     2002-01, 2002-08, 2002-09, 1995-02 

Then you can for each level set the chonrological key :

• • the year

• • the quarter

• • the month

• • the day

Support

Remarks : the primary key of the table time must be an key in the lowest level, otherwise you can have this error.

  [nQSError: 22040] To use AGO of TB function, the storage level of the query  ('[Time.TIME_DIMENSION_KEY]') must be a static level. 

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.  [nQSError: 22042] AGO function may not be used on 'Sold' because its source is horizontally partitioned. (HY000) 

Reference

OBIEE - Time Series Conversion Functions : AGO and TODATE

AGO and TODATE are the types of time series conversion functions.

Articles Related

• • OBIEE - Dimension

  • OBIEE - How to set up the time dimension Ago and Todate

  • OBIEE - Period to period comparison with the AGO Function

Use and grain

Limitation, use :

• • You may only enter AGO and TODATE functions in the Expression Builder in the Administration Tool.

  • You cannot use them in coded SQL.

  • Functions are for relational data sources only

The Ago and ToDate functions allow you use Expression Builder to call a logical function to perform time series calculations instead of aliasing physical tables and modeling logically.

The following list describes the important grains in navigating a time query, using the following query example:

Select quarter, YearAgoSales

• • Query grain. The grain of the request. In the query example, the query grain is Quarter.

  • Time Series grain. The grain at which the aggregation is requested. In the query example, the Time Series grain is Year.

• • Storage grain. The query in the example can be computed from daily sales or from monthly sales, or from quarterly sales. The grain of the aggregate source is called aggregation grain.

Functions

Ago

Calculates the aggregated value from the current time back to a specified time period.

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.

Syntax:

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

In that example, :

• • <measure_expression> is an expression that contains at least one measure,

  • level describe by

    • • <model_id> is a model identifier,

      • <dimension_id> is a dimension identifier,

      • <level_id> is a level identifier,

  • and <number of period> is an integer literal.

The following is an example of this syntax:

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

ToDate

ToDate aggregates a measure attribute from the beginning of a specified time period to the currently displayed time. For example, this function can calculate Year to Date sales.

If unsupported metrics are requested, NULL values will be returned and a warning entry will be written to the NQQuery.log file when the logging level equals three or above.

A ToDate function may not be nested within another ToDate function.

Syntax:

  TODATE(<measure_expression>, <level>) 

About

Articles Related

• • OBIEE - Period to period comparison with the analytical function Lag/Lead

  • OBIEE - BI Presentation Server (Dashboard / Answer)

Implementation

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

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

To see the complete syntax, check this article : OBIEE - Time Series Conversion Functions : AGO and TODATE

that you can then use in a report :

Log / Performance

The time to perform this simple report (without cache) is really long : 1 m 36

Worse than the lag analytical function which perform the same statement in less than 10 sec.

Why ? Because Obiee perform a lot of query and is obligated to perform internal join (I suppose) against one for the analytical function

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

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) 

Creating Logical columns within BI EE for manipulating the Date /Time functions are fairly straight forward using the rich set of Date/time conversation functions available within the Expression Builder UI.

For Example, to extract a Year from the date column you could use the syntax :

“Year(transaction_date)” within the expression builder.

Its also possible to reuse the logical column for additional manipulation. For Example, if the Logical column created above of named “YYYY”, you can use that to define a Quarter as follows :

‘Q’ || cast(Quarter_Of_Year(transaction_date ) as char(1) ) || ‘-’ || cast(YYYY as char(4) )

The above method would not necessitate the need for creating additional views over the dimension table and fact tables as your example suggests.

A note of caution, for any real life deployment it is highly advisable to extend the Time dimension table in the DB to include these columns and avoid having to deal with complicated SQL’s later during optimization and debugging.

Concept of “Chronological Keys” within the BI EE, the premise for chronological key itself is fairly straight forward.

As an Administrator, you need to let the BI server know which columns can be sorted in a Chronological order. These columns need not restricted to date columns, but can be numbers or even varchars. Examples would be Year (like 2006, 2007, 2008…) or Year-Qtr (like 2006-Q1, 2006-Q2 …).