Putting ERP to Work in Higher Ed: Dynamic Metric Selection from Prompt

Examining the ongoing challenges of delivering high-quality, value-added ERP services in Higher Education.

Monday, August 13, 2012

Dynamic Metric Selection from Prompt

There is a saying: "never use two words when one can suffice." Sometimes it seems that with Oracle BI (OBIEE) they have a different view: why provide one way to do something when six can suffice. At the minimum you have to deal with the semi-redundant functionality of the BI Publisher and Legacy Siebel feature sets. But even within those tools there is often more than one way to implement a given feature.

Imagine that you have this requirement: provide the end user an option for which metric to include in a table or graph. A common use case would be to toggle between order volume (# of transactions) and revenue ($).

The most obvious way to do this within Oracle BI is to use the "Column Selector" view. This is easy and fairly obvious.

1. Create your analysis with one metric.

2. From the Results Tab, insert a new view -- Column Selector.

3. Click the checkbox next to the metric you already chose, then double click other metrics from the subject area navigator to add them to the list.

4. View the report and note the drop-down for the user to choose the metric he prefers. (Below is a screenshot with two views on one dashboard).

But that does not seem to be Oracle's preferred method... Presuming that your plan is to deploy Analyses mostly via Dashboards, the recommendation seems to be to configure Dashboard Prompts and utilize Presentation Variables. This is pretty slick, especially in combination with the "go-less prompt" style of removing the Apply button. It isn't straight-forward and it does have some drawbacks, but it is a useful method to know!

1. Configure your analysis with the dimension columns and a single measure.

2. Edit the formula for the measure, entering syntax that presumes a presentation variable "{Met01}" but defaults to something (here: revenue)

Resulting column shown below...

3. Create a Dashboard Prompt.

4. Add a prompt for "Metric" -- the trick here is to replace the member formula with a fake text value -- '1' or something like that (see below)

5. The next trick is to bypass the normal way of adding members to the prompt -- since we are not using a real prompt, we need to trick the system; click the pencil icon in the upper right of the screen.

6. (Above) Key in member names from the fact table(s) of the subject area for this report. This has to be perfect -- I find it easiest to have two tabs of the same OBIEE instance open in Firefox and toggle between them.

7. Establish a default (although your query will default if you followed the above example) and define the presentation variable to be set as a result of the user selecting a value; name it "Met01" since that's what you assumed earlier!

8. Create a new Dashboard and drag the prompt and report into the body.

9. Run the report; select each radio button to see the view(s) refresh.

(And below, after toggling the prompt value...)

So that's pretty slick, and definitely the way to go if you will embed multiple analyses on a single Dashboard. My top concern -- although I would not be surprised to learn there is a fix for this -- is that the column selector has the added advantage of also toggling the column heading, whereas in the prompt/pres-var solution the column heading is something generic such as "selected metric." Nevertheless, I think I'll be teaching this trick to my team next week!

Labels: Business Intelligence, OBIEE, Oracle, Oracle BI, Tips and Tricks

2 Comments:

At December 12, 2012 at 4:54 AM, kiran said...: Can we apply for two different subject areas ,say we have for sales and inventory subject areas at time to pass the common variable...
At January 28, 2013 at 11:45 AM, ray said...: Is there a way to replace the Metric Selector Text from "Base Facts"."Revenue" to just Revenue making it easier for users to read?