Quick OBIEE Trick


Hi guys, hope everyone is well. Today I want to talk a little bit about an OBIEE trick (doesn’t looks like but I work with that too :)).

Other day I was in a client that was complaining about a performance issue in a Condition validation in a Dashboard.

Basically they have a Dashboard with a prompt and a condition to select the Analysis that they want to show in the dashboard.

To build that they used the normal approach:

Create a Variable prompt:

Prompt Creation

Then they created 6 analysis and a Dummy column in each analysis to be used as prompt filter:

Edit Formula

In this dummy column they created a “CASE” formula getting the return of the presentation variable of the prompt and transforming it in a 0 or 1 (false or true).

After create the dummy column they used it in the filter like this:

Edit Filter

Finally, they put all together in the dashboard and used the condition to show only the analysis that has more than 0 rows:

Select Condition

Select Condition2

The trick here is simple, when the “Analysis 1” is selected it will return “Analysis 1” in the “varId” presentation variable. The Case in the analysis will transform this value in 0 or 1 and it will make one Analysis return rows and the other returns none.

The “Condition” in the dashboard will count the amount of rows of each report and will show the report that have more than ZERO rows.

And here’s where my trick is handy. What is the problem with this approach? Obiee must count all the rows that return in all analysis before show it in the screen.

In my client case, they have 6 huge Analysis in this prompt and the time to count the rows in any of the six analysis was bigger than show in the screen the report itself (Remember, Obiee default limit is 25 rows but when you count something it’ll count all the rows that returns not only the 25). (Also I do not know about other clients but all my clients loves to have huge list reports that the ERP does not provides in OBIEE, and it’s not only Brazilian clients, US clients too).

Anyway, this process took a lot longer than I like it, then I created a simple and powerful work around to this.

The idea here is almost the same but with some little differences. First of all we don’t need that dummy column and the filter anymore in the Analysis then let’s remove then:

Analysis1

Analysis2

Now we will create a new analysis. This analysis will be create using a “Direct database Request”.

Direct Database Request

The analysis is simple. We will do a query against the dual table using “CONNECT BY“, “LEVEL” and the return of the presentation variable from the prompt:

Well, what the connect by and the level does. Basically it returns the amount of lines that we want to, for example:

Connect by

In this example, I put 4 in the “LEVEL” and it returns 4 rows, if I put 6 there it’ll return 6 rows and so one. This is an awesome way to generate data without a PL.

Ok now we need to replace the 4 by the “varId” from our Prompt and we’ll have something like this:

Direct Database sql

Now, with this, if the prompt returns “Analysis 1” this query will return 1 row, if it returns “Analysis 2” the query will return 2 rows as we can see here:

Direct Database Request test

Direct Database Request test2

The final touch. We need only to change our condition and make use of our sub query. Because OBIEE condition only counts the amount of rows from an analysis I had to create this “CONNECT BY LEVEL” clause. It was the only one to control the amount of rows depending of the prompt.

With this OBIEE will, instead of count an entire analysis, it will count only a very small and limited amount of rows, and we need only to say that the “Analysis 1” will appears if the amount of rows is 1 and the “Analysis 2” will appears if the amount of rows is 2 and so one:

New Select Condition

New Select Condition2

And the result is:

Results 1

Results 2

This is a very simple but powerful, reliable and faster way to implement an Analysis prompt in OBIEE, and the best part is that if you do not have it yet you can create it without the need of change your current analysis.

Hope you guys enjoy this. See you soon.

Advertisements

2 Responses to “Quick OBIEE Trick”

  1. Charles E Says:

    Nice post, Rodrigo! Did you also try the ROWID function count in the Analysis itself as opposed to a physical expression/query?

    • Hi Charles how are you? You could use a ROWID to do the same trick but you would use a dimension or fact table that would return more or the same amount of rows that you need and filter ir by the ROWID.
      I used the dual because I did not want to use any subject area. I wanted it to run in the fastest way possible, then I used the Dual.

      But the important thing here is to use a sub analysis to reduce the amount of rows that OBIEE will count 🙂 I’m sure that there’s a lot of other possibilities.

      Thank you for the idea 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: