OBIEE – Date Dimension

There is plenty of information available on creating a time dimension in OBIEE.  However, I think the majority of resources fall short of the full picture.  Through a number of small blogs I hope to colour in some of that blank space.  How should we populate the table in the database?  How do we deal with date and time granularity?  What about working days? Or multiple time zones?  Or how do we deal with working days in multiple time zones, including national calendars?  If any of these questions are of interest to you then hopefully the next few pages will help you.
 This first blog will address creating your first date dimension; you can use the SQL below to populate your Date table during your ETL process – why do people not cover this?

Date Dimension Table SQL

If required this SQL can easily be built upon, and/or manipulated to suit your requirements.  Other than that the only things that may need to change are the start date, which you can see referenced a number of times throughout the statement – it must be changed in all occurrences – and the number of records to create, referenced at the bottom of the page.  This SQL will create 4,383 records, which corresponds to the number of days between 01/01/2008 and 31/12/1919 inclusive.
Note that I have created a blog for Creating a Date Dimension in MSSQL.
For the purposes of completeness I have created a fact table to show how it should join to this new date dimension.

Sample Fact Table

The fact table has a single key to the data dimension and a measure, Amount.  Your Fact is likely to be considerably more complex.

Creating the Physical Layer

In the physical layer I have imported both my dimension and fact tablse and created a between the two tables, as described in the screenshot below.

Physical Model

Notice that I have used aliases for both fact and dimension; I would always recommend this approach; no matter how simple your star schema at the moment it could become much more complex and this would avoid later problems.

Creating the Logical Layer

Before the new logical date table can be recognised as a dimension it must join to a fact table; thus the reason fro bringing the fact table into this blog.

Logical Model

Once our date table is recognised as a dimension (ie it turns white) we can create a hierarchical dimension, as shown in the diagram; notice that a unique key exists as each level.

Date Dimension Hierarchy

The time dimension is a special dimension in that it allows us to perform certain functions using its hierarchy that other dimensions do not.  To mark it as such you must tick the checkbox in the properties box for the Dimension Hierarchy, Time Dimension; this is shown in the diagram below.

Date Dimension Properties

And at each level within the registered time dimension a unique key must be registered as a chronological key.

Date Dimension Level Key Properties

Your time dimension is now created; all that remains is to drag and drop the columns into the Presentation Layer and verify your work in the UI.