Business Intelligence – Oracle

Hyperion Essbase 11.1.1.0 – Clearing regions in ASO cubes – Using MDX and MaxL – Part 1

Posted by Venkatakrishnan J on March 5, 2009

Till the release 9.3.1, there was no automatic way of clearing portions of an ASO cube. This is probably one of the most common requirements as there is always a possibility of the data being volatile. In 11.1 release of Hyperion Essbase, a new feature has been added that allows end users to clear portions of an ASO cube using a combination of MDX and MaxL. Lets look at various approaches for clearing an ASO cube, both in 9.3.1 and 11.1 versions. We shall start with 11.1 release first.

11.1 release basically allows for 2 kinds of data clearance from an ASO cube. They are

1. Logical Delete – In this case, Essbase automatically creates another slice of data with an exact negative of the actual data in the region. So, when end users query from the region, they would in effect be getting zeroes for all intersecting cells in the region. This is the recommended delete option as it is much faster than Physical Delete.
2. Physical Delete – In this case, Essbase deletes the physical cells from the region defined. It is generally slower than logical delete as it has to do I/O to remove the cells physically.

For example, lets take the ASOSamp->Sample ASO cube. A typical scenario is wherein the data is loaded each and every month. There are always a possibility of prior months loaded data being volatile. So, in order to load the data for a prior month again, it is necessary to remove that month’s data alone (for all intersections) and then reload it again. In our example, lets first take a look at an Excel Add-in report for month of Jan.

       

As you see above, we have 3 months of data loaded into the ASO Cube. In the month of Mar, due to some error somewhere, we see the necessity to reload the data for Jan. In BSO, this is straight forward as Jan data can be cleared using a simple calculation script. In ASO, so far till 9.3.1 it was not possible directly to remove the Jan data alone using a simple approach (we shall look at some approaches for 9.3.1 in the next blog entry). Now with the advent of the logical and physical deletes this is straightforward even in ASO now. Lets first do a logical delete first and see what happens.

The syntax for the command is

alter database <appname>.<dbname> clear data in region '{MDX Set Expression}' [physical]

The logical delete for Jan is done as shown below

alter database ASOSamp.Sample clear data in region '{[Jan]}';

       

Now, let us take a look at the Jan data in Excel-addin.

       

As you see, this delete has basically not physicall deleted the data. The actual logical delete is explained in the pic shown below

       

Let us do a physical delete now.

alter database ASOSamp.Sample clear data in region '{[Jan]}' physical;

       

       

As you see above, the physical delete has actually removed the data from the cells physically. Thats the reason why we are seeing #Missing against Jan. The actual physical delete is explained as shown below

       

The above is easy. The example below shows a much more complex MDX based delete.

alter database 'ASOsamp'.'Sample' clear data in region '{CrossJoin(CrossJoin([Jan].children,[Age].children),[Stores].children),[Geography]}';

In the next blog entry, we shall see how we can achieve the same functionality in the 9.3 version.

About these ads

3 Responses to “Hyperion Essbase 11.1.1.0 – Clearing regions in ASO cubes – Using MDX and MaxL – Part 1”

  1. […] ParametersContact MeOracle BI EE 10.1.3.3/2 – Customizing look and feel – Styles and Skins – Phase 2Hyperion Essbase 11.1.1.0 – Clearing regions in ASO cubes – Using MDX and MaxL – Part 1Customizing OBI EE – SOAP APIOracle BI EE 10.1.3.3/2 – Custom Drill Path […]

  2. […] March 11, 2009 We are in the final entry on the ASO data clearance blog series. In the blog entry here, i had covered the new feature that was introduced in the 11.1.1.0 release. And in the blog entry […]

  3. sridhar said

    Hi,
    I have a question here. I want to delete a particular portion of ASO data and generate my 1st Report and include the deleted part for my 2nd Report.

    So, can implement above example in my cube??? After delete, How to make it available for my 2nd report???

    please advise me and it will be very helpful for my Job here.

    Regards,

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: