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