Search This Blog

Saturday 17 March 2012

Creating Time Dimension Hierarchy

We can create the hierarchy for the dimension table we are having in the BMM Layer. We can continue to the creation of hierarchy with same step for Time dimension table also. It will work fine. But we are having a specialized hierarchy called 'Time Dimension' Hierarchy which will effectively used to create the calculated columns using Time Series Function.
In this post, I am giving how I have created a sample Time dimension hierarchy using 'SH' schema of oracle database.
In 'SH' schema we are having different columns for Fiscal year and calendar year. In this example I have imported the columns only related to Calendar year. 
Before this I have created all the joins between all dimensions and fact table using physical and logical diagram.
I have dragged my SH schema into the BMM Layer of my Administration tool.

Right click on the BMM Layer schema folder ->Select New Object -> Select Dimension
From below window give time dimension hierarchy name and select the check option 'Time Dimension' and click 'OK'
Time dimension hierarchy is created without levels and keys. Right click on the time dimension -> Select New object -> Select Logical Level.
By passing the above step we are getting below screen. There give the name 'Grand Total' and select the option 'Grand Total level'

Now continue the process on right clicking grand total level which we just created and select New Object -> Select Child Level
Give the level name 'Year' and click 'OK' 

follow the same above process to create the levels of Quarter, Month, Week and Day. Now The time dimension hierarchy is ready without any level Keys.
Now drag the corresponding columns from time dimension table to time dimension hierarchy. 
For e.g. Drag CALENDAR_YEAR_ID, CALENDAR_YEAR Columns to Year Level.
After we dragged all the columns to the corresponding level we will get the time dimension hierarchy like below screen:
One of the import step in time dimension hierarchy creation is creating 'Chronological Key' for each level.
Double click on the year level -> Go to 'Key' Tab -> Select 'New' -> from new window select the column which you want make as a key and Click 'OK'. Now We will be coming to properties window of the level.
From this window we have check option to make a column as a chronological Key.
From the above screen you can understand the concept why I have dragged Calendar_Year_ID and Calendar_Year columns. ID column is used as chronological key and Year column used in drill down when you are creating the reports.We have to create the Chronological Key for all the level otherwise we will get the error. 
Now login into Presentation services and try to create a report with time dimension. Here is the sample output screenshot:
Points to Remember:
1. we have check the option 'Time Dimension' when creating this hierarchy
2. we have to set 'Chronological Key' for each level of hierarchy
3. If you want use the same Key column in drill down there is no need of any 
   other extra to be dragged into the levels of the hierarchy
4. Last but not least, the last level key should be primary key of the 
   dimension table. (The granularity level of any hierarchy should be Primary 

1 comment:

  1. Articles delve into green chemistry principles and sustainable practices in chemical manufacturing. Watch Itv India Blogs and articles feature eco-friendly flooring options and sustainable interior design ideas for green homes.


What is Oracle Database Link and How to create database link (DB Link)

Recent Posts