What is chronological key?
Chronological: Arranged in order of time of occurrence.
To identify the dimension as having a monotonically increasing value in time, we define chronological key. E.g. Time Dimension.
Pre-requisites to Chronological key:
- Sequential (The members have a natural order).
- Cardinal (All members are spaced the same distance apart at a given level, such as day or month).
- Complete (There should be no missing numbers).
- Chronological Order: 2010, 2011, 2012..., Jan, Feb, Mar..., Jan 2010, Feb 2010, Mar 2010...etc.
- Sorting Order: 2010, 2011, 2012..., Apr, Aug, Dec, Feb..., Apr 2010, Apr 2011 etc.
- At-least one level of time dimension should have chronological key.
- In any number of levels you can define one or more number of chronological keys.
- Defining at lowest level is the best practice. But defining at all relevant levels is recommended for all performance issues.
- If the key is structured as YYYYMMDD, you can use it as a chronological key. But if the key is YYYYDDMM, you can't use it as chronological key. Because, the chronological key has to increase sequentially. So, YYYYDDMM won't work as chronological key.
- Date specific functions like AGO, TODATE, PERIODROLLING etc. We can use the chronological key to calculate the results.
- Theoretically, time series functions operate correctly if only the bottom level key in the Logical Dimension is chronological. In practice, however, this causes performance problems because it forces the physical query to use the lowest grain, causing joins of orders of magnitude more rows (for example, 365 times more rows for a "year ago" joining at the "day" grain). It also means higher-level aggregate tables are never selected by the query planner when using the time series functions, which again significantly slows the query.
1. All other dimensions don’t care about the order of the values like region, product, customer etc.
- Example, region_dim the values are north, south, west and east. Here nobody wants to see whether north comes first or south comes first. i.e., no role for order here.
2. In the case of time dimension there needs to be a particular order for all values present in it.
- Example, 2010 is earliest and 2004 is older. Dec-10 is earliest and Jan-10 is older. i.e., the values in the time dimension needs to follow a particular sorting order. So the chronological key is the key which tells the OBIEE that the data will increment based on the chronological column.
3. Here you may get another Question. i.e., you are having columns like year, half_year, quarter, month, week and day. Here which one should become the chronological key?
- Analyze it yourself. If you keep year as chronological key then OBIEE will be confused whether Jan-10 is earliest or Feb-10 is earliest. Because it knows only that 2010 is earliest and 2009 is older.
Finally, always it should be the lowest level of the dimension which needs to be the chronological key. In the above case it should be date.
You can select either date or date_id (this could be a sequence generator values).
