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

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

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

Advertising

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

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

Level Key Description Column in the D_Time
Year the year number calendar_year_code
Quarter the year number + the quarter number calendar_quarter_code
Month the year number + the month number calendar_month_code
Day the date value day_code
Advertising

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

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

[email protected]>connect sh/sh
Connected.
[email protected]>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

The day level is normally the storage grain. The lowest level and the chronological key has to be defined at this level.

Advertising

7 - Support

7.1 - The primary key of the table time must be an key in the lowest level

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)

7.2 - The query level must be a static level

Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 22046] 
To use AGO function, the query level ('Month, Week') must be a static level. (HY000)

When you get this kind of error, verify that the level Month and Week are in the same hierarchy of the dimension. You may have a month for a calender and week for an other.

You can also get this error if you use the level-based measures even if you see AGO in the message.

8 - Reference