The Standard Day Dimension of BI Applications

The Oracle Business Analytics Warehouse provides a standard Date Dimension, which provides all days (much like a calendar) e.g. from 1.1.1980 to 31.12.2030. The Day is the lowest level of granularity for this Standard Dimension. Hence, the naming “Day Dimension”.

Standard Day Dimension

It contains many additional attributes about the Calendar Day e.g. how the Calendar Day correlates to the Financial Calendar or if a Day is the last day of a Quarter, often useful for calculations based on the Date.

This Dimension is used by all the Fact’s of the Standard Data Model. The Dates often (mostly) reside within the Fact and link to the corresponding Day of the Day Dimension. The below figure shows the Standard Marketing Star with these links:Standard Campaign Fact

The Physical Diagram of the Campaign Membership Fact (W_CAMP_HIST_F) shows the Joins from the Fact for all dates to the Day Dimension (W_DAY_D). For each date a alias is created e.g. Created Date, Campaign Start Date, Completed Date.

The End User is presented with the standard subject area which allows to use the Date Dimension (Time Dimension):


But how is this Time Dimension defined?

The Time Dimension available for End Users links the Fact of the selected Subject Area (Marketing – CRM Campaigns) with a “Common” Day Dimension. This Common Day Dimension is the representation of the Time Dimension within the Metadata Repository.

Standard Campaign Time Dimension Source

Each Fact might define the Time Dimension with (random) date of the Star Model. In case of the Marketing – CRM Campaigns Subject Area this is defined as the “Created Date” of the Campaign as shown below:

Standard Campaign Time Dimension

This can also be verified when inspecting the generated SQL by the Oracle BI Server against the Business Warehouse:

Standard Campaign Time Dimension Inspecting SQL

The Created Date of the Fact is joined with the “Common” Day Dimension (available as Time Dimension within the Report Designer UI).

How are the other Dates available?

Other Dates are either available under the sub-folder “Secondary Dates” of the Time Dimension:

Standard Campaign Time Dimension Secondary Dates

Which link to the corresponding Date of the Fact:

Standard Campaign Time Dimension Secondary Dates - sources

Or: the dates are available from the Dimension Folder itself, e.g.:

Standard Campaign Fact - Remaining Dates

To conclude. The Time Dimension often refers to the “created” Date. Other Dates are either provided as Remaining Dates or on the Dimension Folder itself.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.