Business Intelligence – Oracle

Archive for July 18th, 2008

EPM 11.1.1 – Essbase Studio, XOLAP – First Impressions

Posted by Venkatakrishnan J on July 18, 2008

I just did a complete install of the latest EPM release 11.1 on my laptop. One of the first things that i noticed was the naming of this release. This is a fusion release. So, you would notice a uniform look and feel across all the products. Also, the installation procedure itself has changed quite a bit. There is no more setup.exe that we need to click for each of the products. The entire EPM package can be installed at one shot. There is a seperate installer download. This installer would provide the UI for installing all the components. The screenshot below gives you a sample of the installer.


It would be interesting to see whether all the other oracle softwares like BI EE etc would follow the same installation procedure. There is also one more change that i noticed. There is no need for having seperate schemas for storing EAS and Shared Services related metadata. All of them can use the same database schema which simplifies the installation. Be warned if you are doing this installation on an XP machine. Shared Services would not start from the services console(i believe this would be a problem in all XP machines since i have noticed this on 3 different machines with XP as OS. Let me know if that is not the case). One would have to manually start it. The first thing that i did after the installation was to check out the Essbase Studio and the newly introduced XOLAP.


I was expecting some glitches here and there since this is its first release. But so far, all the connections, metadata creation etc have been pretty seamless. Also, there are some very helpful custom tool tips that pop up which provide you with proper direction whilst you are navigating within the studio. It looks like a lot of thinking has gone into this Studio version and so far it is looking really good. There is a very good lineage editor which shows the lineage of the metadata within Essbase Studio.


The other very good feature is that it now recognizes BI EE metadata, Oracle, Essbase as data sources.


Unfortunately, it does not understand BI EE hierarchy yet. It does not use the ODBC driver directly. Instead it directly connects to the 9703 port of the BI Server to extract the metadata. This i believe, is to bypass issues in using the ODBC driver. Now, lets see how we can create a simple XOLAP cube and see what XOLAP actually means. As a first step, lets import the tables in the default SH Schema.


We shall then create a new folder in the Metadata Navigator which would hold all the Multi Dimensional elements.



Once this is done, let us drag and drop relevant/needed columns from the source into the folder that we created above.


Now, create simple hierarchies on Channel, Time & Customer containing one column each (the hierarchies would be flat containing the ids to keep it very simple. This is not the best of examples).


Also, create a measure hierarchy to include AMOUNT_SOLD and QUANTITY_SOLD.


Now, lets create a new Cube Schema that would contain all cube related objects like Hierarchies and measure hierarchies. This is similar to the metaoutline creation step that one does in Essbase Integration Services.


After the creation of a cube schema, lets create a deployment model for that cube schema. So, basically the idea is to create all the possible hierarchies first in the root folder and then determine the type of hierarchies to load in different cube schemas.


Now, in the properties of the Model, choose the deployment option as XOLAP.


Now, right click on the model and click on the deployment wizard. As you see this is similar to the screen that we have in EIS for cube deployment. Lets choose a new application and database name to do the deployment.


As you see, since this is a XOLAP model, it will only build the outline. And the data would be retrieved at run time using SQL. So, this is very similar to ROLAP in OWB. But the difference here is that the outline resides outside of the database and SQL Queries would be fired on the fly while retrieving the data. Once deployed, lets look at the new cube from the Essbase Administration Console.


The XOLAP application also has a set of rule files and also the corresponding outline with the ids. Now, lets go into Visual Explorer and see whether the cube actually fires queries to get the data back for us.


Well it does work. But somehow it errors out whenever i try to do a pivot. On the whole, the Essbase Studio has a pretty intuitive user interface and looks very neat. Next, we shall see the new features of HFR and workspace.

Posted in All Posts, EPM, Hyperion Essbase | 6 Comments »