OBIEE 10G/11G - How to set up the time dimension (for time series functions Ago, Todate, ) ?

About

Oracle BI Server ships Time Series Conversion Functions : AGO and TODATE.

They are supported for all relational database and for use with Essbase. They are not support for flat files.

To use them 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) in order to give all informations on the process to generate a good SQL.

The dimension must be checked as a Time Dimension.

11g	10g

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

Articles Related

Grains

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.

Primary Chronological Key Configuration

This primary chronological key is really important for the function AGO and TD of OBIEE because the software use them to order the period 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 real chronological 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 :

Requirements

You can have inconsistent values when the Time/Calender Dimension is not well build. You have to follow this rules:

Key:

• the chronological key of each level must be a real chronological key.
• the chronological key of each level must not contain NULL values.
• the chronological column must have its values stored in a table column and must not be the result of a function
• the foreign key of the time dimension which links to the fact table in the Business Model is not null

Hierarchy:

• each sub-level must be contained in the above level. Each month belongs only to one year. A week can't be on two months.
• each sub-level must be present for the high level (All years must have 12 months)
• all the time columns in your logical sql belongs to the right level of hierarchy. For instance, even if the week number is not the chronological key, the column must be in the week level of the time dimension.

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

Reference

• Documentation
• OBIEE: How To Determine If A Feature Or Functionality Is Supported