Data Copy in an ASO Application

For those of you familiar with Essbase, when creating an Essbase application there are two types available; BSO (Block storage) and ASO (Aggregate storage). Typically, BSO applications are used when the requirements involve complicated calculations, whereas ASO applications are used if the model contains large dimensions and does not require any complex calculations.

One of the main problems with ASO databases in the past was finding a way to perform calculations such as a standard data copy from one member to another – a task that is extremely simple in a BSO application. You see, unlike in BSO applications, ASO applications don't give you the ability to write and execute calculation scripts. However, there is a way round this which uses MaxL along with an external calculation script and a little bit of MDX.

Below illustrates a copy from scenario Actual to Forecast for months April to August. The MaxL Script is as follows:

execute calculation on database MyApp.MyDB with local script_file "D:\ActToFcst.csc"

POV "Crossjoin(Descendants([Base Currency], [Currency].Levels(0)),

Crossjoin({[Monthly]},

Crossjoin({[Apr],[May],[Jun],[Jul],[Aug]},

Crossjoin(Descendants([Company],[Company].Levels(0)),

Crossjoin(Descendants([Data Type],[Data type].Levels(0)),

Crossjoin(Descendants([Source], [Source].Levels(0)),

Crossjoin(Descendants([Book], [Book].Levels(0)),

Crossjoin({[Working]},

Crossjoin({[&CurrYear]},

Crossjoin(Filter(Descendants([Measures], [Measures].Levels(0)), NOT Measures.CurrentMember.Shared_Flag),

Crossjoin(Descendants([All CC],[Cost Centres].Levels(0)),

Descendants([All Projects],[Projects].Levels(0)) ))))))))))) "

SourceRegion "{[Actual],[Forecast]}";

 Where the calculation script ActToFcst.csc contains the function:

 [Forecast] := [Actual] ;

 

The POV works like a fix in a BSO calculation script, narrowing down the area that the calculation will act on. Here we must use multiple MDX Crossjoin functions to specify the cross-section of data which we require to be included in the data copy. This is probably the most complicated part of creating an ASO data copy – MDX Crossjoins can be a bit of a pain to handle!

Crossjoin Tip: If you come across an error due to your POV containing shared members, use the Filter() function to remove any shared members from your selection (as used above for the Measures dimension)

The SourceRegion is a set of members which are present in the calculation script formula. So in this case, because we are doing a direct copy from forecast to actual, the source region is only "{[Actual],[Forecast]}";

To kick off the data copy script, simply run the MaxL script from within EAS, using a batch script or through a business rule in Planning.