Oracle BI EE 10.1.3.3.2 – Connectivity to Essbase – First Impressions

I had some time today to quickly install Essbase and BI EE 10.1.3.3.2 to test out the newly added feature of Essbase connectivity. Following are the list of bare minimum components that you would need to get this up and running.

1.   BI EE 10.1.3.3.2 – If you are doing an upgrade from an earlier release ensure that you have added ESSBASE = nqsdbgatewayessbasecapi; to your NQSConfig.ini
2.   Hyperion Essbase Server
3.   Hyperion Essbase Client (This is very important for the connectivity to work. If you already have an Essbase server somewhere then you would just need 1 and 3).
4.   Hyperion Administration Services (This is for starting up various Essbase applications).

You can download the Essbase components from http://edelivery.oracle.com. Just an observation from my end. Ensure that you are restarting your machine once all the components have been installed (After installing Essbase, it might not ask you to restart. But BI EE would not be able to identify Essbase immediately since the Essbase client APIs need some Environment variables that get populated only after a restart). Once this is done, go to the admin tool and click on Import from Multi-Dimensional.

      

      

So far so good.

      

As you see, you can import databases from multiple applications simultaneously. The next step is to create a Business Model and Presentation Layer. For this we do not have to design BM and PL. All we need to do is drag and drop the folders directly into BM and PL. For example, when you drag the Sample database, you can see that all the hierarchies and joins would automatically be imported from Essbase.

      

      

Even the final reports seem to be very fast. Of course, the sample applications do not have a large dataset. Lets see how the connectivity works on a larger data set in a future blog entry.

      

P.S: Came to know about the existence of a new Oracle OLAP blog via Mark’s blog here. Any users who want to know about Oracle OLAP, this blog is where you should go since this is maintained by 3 well known OLAP gurus, if i may call them that. Having grown to reading Kevin Lancaster’s long educative replies in our internal mailing lists, i have been waiting for him to be part of a blog so that others can also benefit. Looks like the wait is over :-). Also, the other 2 authors, Keith and Jameson are well known within Oracle OLAP circle. Both Keith and Jameson are former Product Managers. If you do not know where to start you should start with their Workshop series.

Oracle BI EE 10.1.3.3/2 – Dynamically varying Colums in Sub-Prompts – Passing Presentation Variables to Dashboard Prompts

We just had another interesting question today in one of our internal forums. The question was how do we dynamically vary the columns in a Dashboard Prompt based on a selection in another dashboard prompt. Let me explain a bit further. For example, lets assume that we have 2 dashboard prompts. One has values like Region Sales and Brand Sales (these are hard coded values, same as what we saw in the previous blog entry). Now the requirement was, whenever Region Sales is chosen in one prompt the values in the other prompt should change as to show the values of only category names (CATEGORY_NAME). Whenever Brand Sales is chosen in the 1st prompt the 2nd prompt should show only the product names (PRODUCT_NAME). I can envision a similar requirement in many scenarios where one might want to have a single page wise dashboard prompt that would filter all the other prompts within the dashboard whenever a selection is made. In order to achieve this, lets start with the first dashboard prompt where we shall print dummy values ‘Region Sales’ and ‘Brand Sales’. Make this dashboard prompt to set a presentation variable say Report. For more details on how to achieve this refer my blog entry here.

      

Now lets start creating the sub-prompt whose reference column would change based on what is chosen in the first prompt. In order to do this, lets include a dummy column (whatever we choose in the dashboard prompt will not affect any columns. In order to use this prompt we can only use presentation variables) by entering the below case statement in the formula tab.

CASE WHEN 1=0 THEN PRODUCTS.CATEGORY_NAME ELSE ‘Dummy unused Column’ END

      

Now in the dashboard prompt, convert the results to show only SQL Results and enter the below SQL.

SELECT CASE WHEN ‘@{Report}’=’Region Sales’ THEN PRODUCTS.CATEGORY_NAME
WHEN ‘@{Report}’=’Brand Sales’ THEN PRODUCTS.PRODUCT_NAME ELSE ” END FROM SH2

      

So basically, we are passing the presentation variable that we used in the first prompt to the second prompt. Lets now look at the final output after including both the prompts in a dashboard.

      
Lets first choose Region Sales and see what happens to the second dashboard prompt.

      

Similarly, lets choose Brand Sales and see what happens.

      

The only disadvantage of this approach is that the users would have to hit the GO button to make the dashboard prompts to change. But again this can be very helpful in situations where you want a single dashboard prompt to control the output of multiple dashboard prompts.