How to “Fake” a calendar dimension

Often, developers are asked to use less than ideal data structures to create reports and dashboards in OBIEE. These reports usually involve recreating some kind of list report found in MS Excel or another reporting tool. Recently, I was asked to do just this…

The requirements were that the request needed to filter on a number of specific dates, only use a single dashboard prompt, all without the luxury of a calendar dimension.

My solution was to use a calendar dimension from another subject area, then pass the date value selected to the request filter using a presentation variable. Below is a recap of steps I took to execute the solution:

1. Create a prompt using an “alien” calendar dimension. “Alien” meaning that this calendar dimension can exists in any other subject area, since any table relationships will have no bearing on this interaction.

2. In this case we’re using a Month End Date, and we’ve set the presentation variable name to be “Date_Variable”.

3. Within the request, we’ve set each date field we want to filter to recognize the presentation variable set in the prompt.

4. Add the Prompt and the Request to a dashboard page.

Once the Prompt and the Request have both been placed on a dashboard page, they should interact together, and the end user will be none the wiser. Notice how the value selected in the Prompt is reflected in the Filter View.