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”.
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:
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.
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:
This can also be verified when inspecting the generated SQL by the Oracle BI Server against the Business Warehouse:
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:
Which link to the corresponding Date of the Fact:
Or: the dates are available from the Dimension Folder itself, e.g.:
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.