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

Bi Server Architecture With Client


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
Obiee11g Time Dimension Property Obiee10g Time Dimension Property

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


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 :

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


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


  • 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


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

Obiee10g Time Dimension Property

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

gerardnico@orcl>connect sh/sh
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

Obiee Time Dim Year Key

  • the quarter

Obiee Time Dim Quarter Key

  • the month

Obiee Time Dim Month Key

  • the day

Obiee Time Dim Day Key

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


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)

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.


Discover More
Bi Server Architecture With Client
OBIEE - Dimension (Logical Table)

A dimension logical table is typically joined to a fact logical table that will contain measures. Every logical dimension table should have a dimensional hierarchy associated with it. This rule holds...
Obiee Essbase Unbalanced Hierarchy
OBIEE - Essbase

Cube scope: Member attributesunbalanced hierarchies“”“”hierarchy columnattribute column Support...
Obiee Ceim
OBIEE - Logical Sql

The logical SQL is the SQL that understand BI Server. It's a full Ansi 92 SQL (including subqueries and derived tables), plus special functions (SQL extensions) like AGO, TODATE, EVALUATE, and others....
Bi Server Architecture
OBIEE - Query Compiler

The query compiler is responsible of the compilation of a logical sql in the query processing process of BI Server. The query compilation is composed of the following five phases: parsing, , navigation,...
Bi Server Architecture With Client
OBIEE - Repository Design

... .. A consideration to take when designing a subject area is to pay attention at the final user. Do you design for a user:OBIEE Analytics/Reporting...
Exp Builder Ago Td
OBIEE - Time Series Conversion Functions : AGO and TODATE

Time series functions in OBIEE. The implementation of the time serie function is based on the time dimension. ( See set up time dimensions) At query time, the Oracle BI Server then generates SQL that...
Obiee Multiple Dimension Hierarchie
OBIEE 10G/11G - Dimension Hierarchy (logical dimensions)

(Dimension hierarchies|Logical Dimensions|Hierarchy) introduce formal hierarchies into a business model. They are created in the Business Model and Mapping layer. Before 11g, end users can't see them...
Obiee Dimension Drill Down Parameters
OBIEE 10G/11G - Level-based Hierarchy

Level-based hierarchy is the first type of hierarchy in OBIEE. Dimension hierarchy levels allow : to perform aggregate navigation, to configure level-based measure calculations, users from Dashboard...
Obiee 11g Aggregate At
OBIEE 10G/11G - Period to period comparison with the AGO Function

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...
Obi Edition

KPIs are measurements that define and track specific business goals and objectives that often roll up into larger organizational strategies that require monitoring, improvement, and evaluation. KPIs have:...

Share this page:
Follow us:
Task Runner