Business Intelligence – Oracle

Hyperion Essbase 9.3.1 – OBE Series – Using Outline Migration Wizard to Convert BSO to ASO outlines – Understanding ASO Cubes – Part 5

Posted by Venkatakrishnan J on December 18, 2008

In the last 4 articles, we saw how to go about designing a BSO cube. In this article we shall see how to go about converting a BSO cube to an ASO cube using the outline migration wizard. Before we do that, let us try to understand the basic differences between ASO and BSO cubes. ASO cubes have certain restrictions. But they are an excellent fit if you have huge dimensions. Typically an ASO cube aggregates very fast and this is primarily due to the fundamental differences in the architecture of ASO and BSO. On a high level following are the ASO properties that one would have to know

1. ASO cannot be used to load data at non-level0 members. ASO will accept data only at the lowest level.
2. ASO does not support calculations in stored members for Non Account dimensions.
3. Each non-Account dimension hierarchies in an ASO cube can be of 3 types. They are Stored, Dynamic and Multiple Hierarchies.
4. A Stored Hierarchy dimension is like a normal hierarchy in BSO. But the major difference is that the same member cannot be shared more than once within the hierarchy. Also, non-level0 members cannot be shared within a stored hierarchy. This hierarchy does not support stored members within calculations.
5. A dynamic hierarchy on a non-accounts dimension has all the properties of a dimension in a BSO cube. But the major difference is that the upper level member values are dynamically obtained (during data retrieval). Also, calculated members are supported in this hierarchy.
6. A multiple hierarchy dimension can have both stored and dynamic hierarchies. But this dimension should have atleast one stored hierarchy.
7. ASO data loads are typically more flexible than BSO data loads. ASO supports the concept of load buffers which can do data addition,subtraction etc of data coming in from multiple data sources in memory
8. There is no need for identifying sparse and dense dimensions.

There are other differences as well(like attribute dimension is not supported on all the dimensions etc). But the ones listed above are the most important ones atleast from an outline and dataload standpoint. Our goal in this article, as stated above, is to create the same Global BSO cube in ASO as well. Let us first start with creating a new ASO application and an ASO database called GlobASO.

       

Now, let us migrate the BSO outline that we created before using the Aggregate Outline Migration Wizard.

       

In the source, choose the Global Database’s outline.

       

As you see below, as soon as we click on next, we would see a lot of errors/warnings which inherently show the differences between ASO and BSO. Do not do the conversion automatically. Instead use Interactive Conversion.

       

Due to these inherent differences, the wizard would not migrate everything correctly. So, we shall correct all the errors manually now.

       

The first step in the correction is to make one of the customer hierarchies as a dynamic hierarchy. This is because by default chosen as a stored hierarchy by the wizard. The reason for doing this is that ASO does not support a shared member to occur twice in a stored hierarchy. To make this change, make the customer dimension to be multiple hierarchy enabled and make the Total Market as a dynamic hierarchy.

       

Once this done the verification would go through without any problem. But from a data load standpoint we still have one open issue. If you recollect, we had UNIT_PRICE and UNIT_COST measures having a grain of only 2 dimensions. Since BSO supported the option of loading values directly to the parent members, we had loaded the values against CHANNELS and CUSTOMER dimensions (non grain dimensions). But ASO does not allow data to be loaded at non-level0 members. So, we need to create 2 dummy members(or use existing lev0 members) under Channels and Customer dimensions. These members are shown below

       

We also have to change their hierarchy types in order to ensure that the dummy members do not rollup. Instead of proceeding further in the wizard just copy all the members(dimension by dimension) to the GlobASO outline and save the members

       

Now, that we have created the outline the next step is to load the data. The rule file that we created for Units measure would still work in the ASO cube. But the SQL for the other 2 data load rule files would change since we would be loading the data against a dummy level0 member. The SQL’s are given below. Just copy the rule files to new cube and make the below SQL changes. Then use them to load the data.

SELECT
MONTH_DSC,
'Unit Price',
'Channel No Grain',
ITEM_DSC,
'Customer No Grain',
SUM(UNIT_PRICE) DATA
FROM
PRICE_AND_COST_FACT UF,
TIME_DIM TD,
PRODUCT_DIM PD
WHERE
UF.MONTH_ID = TD.MONTH_ID AND
UF.ITEM_ID = PD.ITEM_ID
GROUP BY
MONTH_DSC,
ITEM_DSC

 

SELECT
MONTH_DSC,
'Unit Cost',
'Channel No Grain',
ITEM_DSC,
'Customer No Grain',
SUM(UNIT_COST) DATA
FROM
PRICE_AND_COST_FACT UF,
TIME_DIM TD,
PRODUCT_DIM PD
WHERE
UF.MONTH_ID = TD.MONTH_ID AND
UF.ITEM_ID = PD.ITEM_ID
GROUP BY
MONTH_DSC,
ITEM_DSC

Once the data load is done, let us run the aggregation on the ASO cube. Typically an ASO cube can be aggregated based on different parameters like based on Query statistics, based on the target cube growth size etc. For now let us run the default aggregation recommended.

       

       

Now, let us check the data in this cube,

       

       

As you see the data matches exactly with what we saw in the BSO cube.

Advertisements

One Response to “Hyperion Essbase 9.3.1 – OBE Series – Using Outline Migration Wizard to Convert BSO to ASO outlines – Understanding ASO Cubes – Part 5”

  1. Sonal said

    I am migrating essabse application from 9.2 to 11.1.1.0.3. I am able to migrate calscripts, rule files but somehow .otl file is not getting migrated. The source essbase is on Unix box and target essbase is on windows. please help

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

 
%d bloggers like this: