Date dimensions are in high demand. Nearly every warehouse and data mart has at least one date dimension. They are extremely useful in Cognos reporting – end users frequently want to analyze data by year, quarter, month, etc.

However, there are some things that are difficult to do with a “standard” date dimension. For example, calculating period-to-date in a relational data source, or setting a report to always default to the current quarter both take more effort than they should to implement in Cognos (and both require Report Studio to accomplish).

Problems such as these can be solved by creating set of fields in the date dimension that are updated nightly. The resulting dynamic date dimension allows report writers to accomplish many things (such as those described above) simply.

There are many properties of a date which will change (or potentially change) every day. Adding a date dimension job to your ETL process allows these properties to be recalculated nightly.

Examples

Here are a few typical examples of dynamic date attributes. Each BI solution has different goals, but these are a good starting point.

Relative Day Number

This field stores an integer value representing the offset from the current date. A value of zero indicates the current date. Future days count upward: tomorrow is 1, two days from today is 2, etc. Similarly, previous days count backward: one week ago is -7, two weeks ago is -14, etc.

This property can be used to create prompts that always default to the current date. In Cognos 10, set the “Use Value” property of a prompt to the Relative Day Number field, and create a default value of zero. The prompt will now default to the current day each time the report is run.

Similarly, creating a filter on a report where Relative Day Number is zero will always filter on the current day.

The same concept can be applied to other periods (month, quarter, year, etc.).

Note: When including these fields in a Framework Manager model, make sure the Usage property is set to Attribute. (Because these fields are numeric, Framework Manager may set their Usage property to Fact by default.)

Month to Date Indicator

This field is a yes/no value which indicates whether the date falls into a “month to date” calculation. For every month, if the day of the month is less than or equal to the current day, then this field would have a “yes” value. For example, if the current date is July 22, the 1^st through the 22^nd of every month would be set to “yes”, and the 23^rd through the end of the month would be set to “no”.

While this calculation is already available in dimensional sources, it is not available for relational sources. When combined with a relative period field (such as Relative Month), it becomes easy to create a report query such as “show month to date values for the past 6 months”.

As with Relative Day Number, this technique can be used for other periods (quarter, year, etc.). Using a bit value (0 or 1) for the flag will save database space, but using a single character value (Y or N) may be easier on report writers.

Current Quarter Indicator

This field is another yes/no value which indicates whether the date falls into the current quarter. It will only be set to “yes” for one quarter’s worth of dates at a time. This is similar to a Relative Quarter Number field – a Relative Quarter Number of zero is the same as a Current Quarter Indicator field of “yes” – but this field can make report design (and understanding a report’s design later on) much easier.

As with any indicator field, this can be created as a bit to save space, or a single character field for clarity. Similar indicator fields can be created for other periods.

External Data

Any date-related information which is stored in a source system and which is subject to change can be loaded into the date dimension dynamically. For example, company or national holidays (which can have a significant impact on daily sales or manufacturing volume) can be loaded as a flag.

Including these fields in datamart ETL loads is not difficult. A SQL UPDATE statement can handle these calculations, as can more advanced ETL tools such as SSIS, Informatica, or expressor. (The SQL statement can be called directly or embedded in a stored procedure.)

Once you have committed to updating the date dimension with ETL, many opportunities to make reporting writing simpler will present themselves. Look for them and make use of them!