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:
Then they created 6 analysis and a Dummy column in each analysis to be used as prompt filter:
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:
Finally, they put all together in the dashboard and used the condition to show only the analysis that has more than 0 rows:
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:
Now we will create a new analysis. This analysis will be create using a “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:
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:
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:
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:
And the result is:
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.
DEV EPM 