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.