Converting BSO member formulas to ASO MDX formulas

Introduction

Aggregate storage application doesn’t support member formulas. You will have to face some errors while you convert block storage database into aggregate storage database. In this document we have shown some examples of BSO outline formulas and their equivalent MDX script. MDX supports in both block storage and aggregate storage. So if you want to use those formulas you have to convert all member formula in to MDX script.

In the below diagram we have taken some financial ratio calculation formula. We’ll convert this BSO outline to ASO outline.

Adding member formula in BSO out line:-

Right Click on member :- “Edit member properties”

Select formula tab you will get a scripting wizard, you can write script and verify. You also can use functions in script.

Once you verified the script is correct you can save it.

We are going to convert the below BSO outline formulas. There are some examples of member formulas in cube below outline.

Conversion of Block Storage Database in to Aggregate Storage Database

Now to Convert block storage application in to aggregate storage database

Go to file :- Wizards :- Aggregate Storage Outline Conversion

Select the block storage outline as a source, which you want to convert into aggregate storage.

Select target destination aggregate storage outline.

Click Next

While converting BSO outline to ASO outline, we do not get any error message about the outline formula syntax error. Once the BSO outline converted to ASO outline, we need to check each formula and change its syntax based on MDX script.

When you verify this member formula you will get an error message, shown below.

Now if you want to execute all these formula in aggregate storage, you have to convert all these member formula in to member formula as per aggregate storage.

Convert block storage member formula in to aggregate storage member formula:-

There are some syntax difference in block storage and aggregate storage. Like In aggregate storage codes (“”) are replace by square brackets [ ] no semicolon (;) required.

You have to write complete formula as per aggregate storage syntax.

Formula1:- Block storage member formula

Current Ratio = "Total Current Assets"/"Total Current Liabilities";
You can just replace codes (“”) by square brackets [ ] and remove semicolon.

You can see that formula is now converted in to aggregate storage member formula.

Current Ratio = [Total Current Assets] / [Total Current Liabilities]

Formula2:-

You can see that the validation failed for other block storage member formula now convert this member formula in to aggregate storage formula.

Validation is done successfully done for converted aggregate member formula.

Formula3:-

Converted aggregate member formula for Gross Margin

Gross Margin = ([Pre Tax Income]/ [Sales])*(([Pre Tax Income]-[Tax])/ [Pre Tax Income])

This way you also convert all BSO member formulas in to ASO member formula.

Validation of Formula

Connect your block storage cube with Essbase Client and see the Q1 data. You can see Current Ratio, Quick Ratio and Cash Ratio data value for Q1.

Same data vale has retrieve when aggregate storage database is connected with the sheet.

Converted Aggregate Storage Outline

Below screenshot is Converted Aggregate Storage outline with relevant aggregate member formula.

Formula List

List of converted Block storage member formula in to Aggregate storage member formula