By: Mark Thompson

A client needed to implement a “current day” report, in which the date automatically changes based on the last loaded date of their data.

To meet that need, we created a secondary time hierarchy that has a single year, quarter, month, and day in it – 4 members total.  We do NOT aggregate cubes over that hierarchy.  The client created a relational view that contains the current day’s calendar information, based on the MAX value of the date found in their fact table.

In Discoverer, we created a report that selects all times at the Day level from the “Current Day” hierarchy.   Each night when the cube is maintained, we also maintain the Time dimension, and thus update that 2nd hierarchy, and thereby change the date on those “Current Day” reports.  By the same token, we could also select the current Month, Quarter, and Year as represented in the fact table, by using that same selection method.