Hyperion Essbase 18.104.22.168 – Transparent Partition – Using BSO as Partition Targets – Write-Back Partitions on ASO – Part 2
Posted by Venkatakrishnan J on March 23, 2009
In the last blog entry, i had given a brief on how Transparent partition works on ASO targets. One of the major drawbacks of ASO targets is that, they cannot be used to store local data. Today we shall see the advantages of using BSO cubes as transparent partition targets. The major advantage of using BSO as a transparent partition target is that, it provides the capability to do write-backs on ASO (more of a workaround). Lets take at a simple use case today. We shall be using the same GlobaASO cube as our ASO source. Lets look at this ASO outline first.
As you see, we have 4 analysis dimensions and one accounts dimension. This ASO cube stores only actual units sold. Lets assume that we have end users who do active planning/forecasting on the number of the units sold. And they need to have the capability to store the forecasted data directly into the cubes. Typically they do the forecasts along the product dimension. Lets also assume that forecasting is typically done for the last 6 months after looking at the 6 months of Actual units sold. So, in order to facilitate this, lets create a BSO cube as shown below.
As you see, this BSO outline has only 3 dimensions. So, any user who would be involved in forecasting would be looking at the actual units sold for the first 6 months and then would be doing the forecasts accordingly for the remaining 6 months. Also this outline has one extra account called as Forecast Units which would hold the Forecast Data. Now lets create a transparent partition with the ASO cube as the source and the BSO cube as the target.
The important point to note while creating a partition is the fact that we do not have the same set of dimensions in the source as well as the target. So for all the dimensions that do not exist in the target, just include the topmost member (dimension member or the member that you feel has the relevant data stored for forecast). The source partition area would look like the one shown below
As you see, i have chosen the topmost member(in the source) for the non-existent dimensions in the target. The target partition would look like the one shown below
Now, if you validate the partition you would get an error stating that the number of dimensions in the source and the target do not match.
To bypass this, go to the mappings tab and map your non-existent dimensions in the target from the source as shown below
This will validate your partition. Now go to excel-add in and query on the new BSO cube.
Now as an end user, i would typically look at Q1 and Q2 actual numbers and based on that i would be forecasting for the remaining 2 quarters across multiple products. Of course, there would be a lot of other factors coming into play while forecasting, for simplicity purposes i would just multiply the Q2 actual numbers by 1.3 for Q3 and Q4 forecast. You should be able to do this directly from excel-add in. Though you might be getting warnings on the write-back going against ASO(as long you are sure that the particular cell that you have locked is not part of the transparent partition you should be good), you should still be able to writeback. Essbase performs a partition area check based on the members chosen in Excel and not on the cells locked. Hence you might get warnings even if you have locked the correct cell.