Archive for March, 2015

Quick OBIEE Trick

Posted in Dahsboard, OBIEE, Performance, Tips and Tricks with tags , , , on March 10, 2015 by RZGiampaoli

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

Essmsh/OdiOSCommand/Echo Trick

Posted in EPM, Essbase, Essmsh, MaxL, ODI, Tips and Tricks with tags , , , , , on March 9, 2015 by radk00

Hi all, today’s post is a very simple but useful trick using ODI and Essmsh command. As you know, the MaxL Shell (essmsh) is a pre-parser mechanism for entering MaxL statements. In order to use it, first you need to log into a server:

1_essmsh

After you login, you may start issuing MaxL commands against that specific host. But this is an interactive login, where you need to manually specify the user/password/host. Fortunately essmsh contains some options that allow us to pass the login (-l) and the server (-s) as parameters. It looks like this:

essmsh -l ServiceUser password -s ANY.SERVER.COM

This is great, but imagine that you want to login and execute some essmsh commands to automate a specific process. Imagine that after the login you want to run the following commands:

  • set message level error;
  • display partition on database CONWF_M.WrkForce advanced;

Here is where the “echo” trick comes in. essmsh have another option (-i) that starts a MaxL session which reads from <STDIN>, piped in from another program. So basically you can do the following:

echo set message level error; display partition on database CONWF_M.WrkForce advanced;| essmsh -l ServiceUser password -s ANY.SERVER.COM -i

Now this single OS command will connect to a server, login and execute the set message and display commands. Very simple, but very powerful!

It gets even better if you add ODI to the game. ODI is extremely flexible to develop this kind of automation processes. Here is what it would look like. Create an ODI procedure and go to “Command on Source” Tab. Select the “Schema” where you want to execute your essmsh commands:

2_essmsh

No code is needed here, since we will just use it to retrieve the Essbase connection information, like user/pass/server/app/database. On the “Command on Target” tab, you may add something like this:

3_essmsh

Here we are calling a login on essmsh and we are “echoing” some MaxL commands against an Essbase application/database using the –i parameter. The output of this command will be redirected to the -OUT_FILE where you may check it or use it in another process. Very handy and easy for a lot of Essbase automation with ODI.

Hope you liked this quick tip! See ya!