Business Intelligence – Oracle

Oracle Data Integrator 10.1.3.5 and Hyperion Essbase 11.1.1.0 – Columnar ASO export using MDX

Posted by Venkatakrishnan J on March 24, 2009

I was on a customer call last week wherein the customer wanted to know of ways to export partial data from an ASO cube. The issue with the customer was that they were on version 9.3.1. Since 9.3 does not support clearing regions of an ASO cube, they wanted ways of exporting partial data from the ASO cube and then reload it back to the same cube after clearing the cube. This is a very common scenario and i thought i would blog about it here. As you would know, Oracle Data Integrator 10.1.3.5 now supports export of data from an Essbase cube using Report Scripts as well as MDX. We shall be using the same feature today to understand how MDX exports for an ASO cube work. Also, it would be an interesting activity considering the fact that there can be different ways of framing an MDX query. So, lets first start with the GlobaASO cube that i have been using for the last 3 or 4 blog entries. First lets take a look at the outline

       

Basically this cube has data for the month of April, May and July as shown below.

       

Now, our intention is to take an export for the month of April and May from the ASO cube. There are a couple of options. One is to use a report script as shown below and take the export. But generally MDX is always recommended over Report Scripts for ASO cubes.

<Sym
{ SUPALL  }{ NAMESON  }{ TABDELIMIT  }{ ROWREPEAT  }{ NOINDENTGEN  }{ SUPMISSINGROWS  }
<SUPSHARE
<ROW ("Time","Measures","Channel","Product","Customer")
"Apr-99","May-99"
<DIMBOTTOM "Measures"
<DIMBOTTOM "Channel"
<DIMBOTTOM "Product"
<DIMBOTTOM "Customer"
!

       

The above report script can be re-written into MDX in 2 ways(the main difference between the 2 is the fact that you would get a lot of #missing rows in the first query). Both the MDX scripts are provided below

SELECT
{[Measures].[Units]} on AXIS(0),
NON EMPTY {[Customer].Levels(0).members} on AXIS(1),
NON EMPTY {[Apr-99],[May-99]} ON AXIS(4),
NON EMPTY {[Channel].Levels(0).members} ON AXIS(3),
NON EMPTY {[Product].Levels(0).members} ON AXIS(2)
FROM
[GlobaASO].[GlobaASO]

       

With
set [Time1] as '{[Apr-99],[May-99]}'
set [Channel1] as '[Channel].Levels(0).members'
set [Product1] as '[Product].Levels(0).members'
set [Customer1] as '[Customer].Levels(0).members'
select
{[Measures].[Units] } on columns,
NON EMPTY {crossjoin ({[Time1]},crossjoin({[Channel1]},crossjoin({[Product1]},{[Customer1]})))}   on rows
from [GlobaASO.GlobaASO]

       

Even if we try to save the above MDX outputs as a CSV file we would get the member names wrapped within parantheses (depending on the MDX fired).

       

Now, lets go to Oracle Data Integrator and create an interface with this GlobaASO as the input. Our idea is to test both the above MDX queries. So, first lets start with the first MDX Query and insert it into a dummy table.

       

       

Now, lets try the same thing with the other MDX query.

       

       

It does look like ODI takes into account all types of MDX queries(though in effect every MDX query works on the AXIS specification). The interesting fact is that BI EE chooses the ROWS and COLUMNS (basically AXIS(0) and AXIS(1)) type of queries instead of the first type of the query where every AXIS apart from AXIS(0) comes as a seperate column. One of the main reasons for this is the fact that NON EMPTY clause would not work on individual members. It would work only on the actual existing tupules. So, the 2nd type of query with cross join would actually generate a resultset without #Missing values(lesser number of rows returned). The more you start analyzing the BI EE and Essbase connectivity, the more you get to know how the connectivity has been designed. It would also give you insights on the future product directions. For example, if you notice in EPM 11.1.1.0, a new feature has been added where saved sets can be used during the duration of the session. My guess is that this has been added keeping the BI EE 11g in mind(based on the screenshots in the OOW presentations). Anyway, i am digressing. The above was more of a initial walkthrough of the ODI-Essbase MDX export capabilities. I would be going more into detail of Java API and MDX in the future blog entries.

About these ads

One Response to “Oracle Data Integrator 10.1.3.5 and Hyperion Essbase 11.1.1.0 – Columnar ASO export using MDX”

  1. Hi there. Thank you. I read it regularly to read the most recent info. Extremely helpful article.

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

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: