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.

BI Publisher and OBIEE: Integration with Dashboards & Prompts

In this post I will demonstrate another way to "integrate" OBIEE with BI Publisher ("BIP"): adding a pre-built BIP report to a Dashboard and configuring it to interact with a Dashboard Prompt.

For this example I've already logged into BIP as "Administrator" and created a report named "BI Publisher Subject Area" including some simple dimensions and facts from the "Paint Exec" demo Subject Area. I've uploaded a simple table template aptly named "SubjectAreaTable" using Word to this report. The resulting report viewed in HTML looks like this:

Step 1: Add a BIP Report to Dashboard

From a blank Dashboard page, drag & drop "BI Publisher Report" object into the blank Column. Click "Properties" then "Browse" to the location of the existing BIP report "BI Publisher Request." (Note what happens when you "hover" over the report name - that's the value of the report's Description field.)



Click on the report name and then OK. Click OK again in the Properties window (accept the default parameters for now). Save your changes then view the new Dashboard. Note that the standard BIP controls are available - you can select a Template & format and even choose to deliver the output using the standard BIP options (which are a bit different from OBIEE's options).

Step 2: Configure the BIP Report to respond to a Parameter for "Region"

Now switch to BI Publisher (More Products > BI Publisher). Navigate to "My Folders," then click the "Edit" link under the "BI Publisher Request" report.

Click the "Parameters" option in the left-hand nav, then click "New" to create a new parameters.



Fill in the values as follows:

Identifier: "REGION_NAME"

This value is fairly arbitrary, but as you will see below we will be referencing it in Answers so it doesn't hurt to consider some sort of naming convention. I am also deliberately making the identifier different than the name of the Presentation Column in Answers to clarify the distinction between the two.

Data type: "String"

I've had spotty results using anything but "String" -- Date in particular gave me trouble.

Parameter type: "Text"

There are some interesting uses for other types, but we'll save that discussion for later.

Display Label: "Region Name"

This value can also be arbitrary

Text Field Size: [leave blank]
Options: [leave blank]

Both of these are also out of scope for this discussion.

Now we click into the Data Set (within the Data Model "folder") and add the line "WHERE Markets.Region = :REGION_NAME" to the SQL Query input box. Note we are using the "Identifier" of the Parameter we just created.



Save your changes (click the disk icon in the upper LH corner) and view the results - Note the results by default will be blank because we haven't entered a value. Enter "CENTRAL REGION" in the parameter box and click "View."



Step 3: Add a Prompt to your dashboard

Go back to Answers and create a new Dashboard Prompt based on the "Region" Presentation Column. Here's the important part: Configure the prompt to set a Presentation Variable named - you guessed it - "REGION_NAME" and save your new prompt.




OK last step: Still in Answers, go back to modify your Dashboard and add the new prompt. Might as well put it on top of the existing BI Publisher report.


Save your changes and view the Dashboard. Again, because the default value for Region is blank, you won't get any results unless you pick one from your new prompt.



But, uh oh, how can I select ALL regions? The "All Choices" selection doesn't seem to work the same way with BI Publisher as it does with native OBIEE reports...

Hmm. How about I give a banana to whomever can chime in with the solution to THAT problem?


Remember that reports in BI Publisher can be created against other data sources besides OBIEE... but the Dashboard integration is essentially the same. I'll save that topic for another post.

TTFN