Business Intelligence – Oracle

Archive for March 5th, 2009

Hyperion Essbase – 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.

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