OBIEE - How to set up the time dimension Ago and Todate
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
Only the date level is required but it's recommended to set the other levels for performance reasons.
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
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 time series functions calculate Period Ago and Period to Date functions based on user supplied calendar tables, not on standard SQL date manipulation functions.
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.
Time series query is valid only if the time series grain is at the query grain or higher
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.
The chronological key has to be defined at this level
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.
You can nest exactly one ToDate and multiple Ago functions if they each 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.
You can nest exactly one ToDate and multiple Ago functions if they each have the same level argument.
Syntax:
TODATE(<measure_expression>, <level>)
About
When the Ago and the Todate function are set up, you can use them to perform period to period comparison.
Articles Related
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 …).