IBM

Converting BSO member formulas to MDX

Overview:

MDX (Multi-Dimensional query Expression Language) is the data manipulation language for Essbase which is used to convert the BSO outline Calculation scripts to ASO outline calculation. Aggregate outline performs calculations dynamically and  can be compared with the BSO dynamic formulas. MDX language is supported by ASO and BSO but the Calc language is specific to BSO.

Differences between ASO and BSO

  • Storage options “Dynamic Calc and Store” and “Dynamic Calc” are not available in ASO.
  • BSO calculation depends upon the order they are placed in the Outline hierarchy. ASO formulas are executed based on the order of their dependencies in ASO but the order also can be defined using the “Member Solve Order” property.
  • BSO separates the dense and sparse dimension for certain calculations but there is no such method in ASO.
  • ASO Attribute dimension calculations support only “Sum”.
  • ASO outline allows formulas only in account dimensions or dynamic hierarchies.

When to use MDX??

  • Any formulas which are attached to a BSO member belong to “Account”. dimensions.
  • Expense reporting is not available in ASO. Use of VAR (Arg1, Arg2) function can be replaced as Arg1-Arg2.
  • Any Essbase calculation functions referring to dynamic time series members or shared members.

Common Scenarios

IBM / Red Hat - Unlock Potential App Modernization
Unlock Your Potential with Application Modernization

Application modernization is a growing area of focus for enterprises. If you’re considering this path to cloud adoption, this guide explores considerations for the best approach – cloud native or legacy migration – and more.

Get the Guide

Scenario 1: Converting Q-T-D BSO Functions

Solution: Create a new member “QTD” under dimension which is tagged as “Time”. Add sibling members as shown below

Scenario-1_MDX

Scenario 2: Converting Time Balance Functionality

Solution: Time Dimension member hierarchy should be defined as “Stored” to use the time balance functionality. If the member hierarchy is not “Stored” then create a formula member as shown below

WITH MEMBER [Measures].[Starting Inventory] AS
‘IIF(IsLeaf([Year].CurrentMember),
[Measures].[Opening Inventory],
([Measures].[Opening Inventory], OpeningPeriod ([Year].Levels(0),[Year].CurrentMember)))’

OpeningPeriod

The purpose of the function is to return the first sibling among the descendants of a member at a specified level. All function parameters are optional. If no member is specified, the default is [Time].CURRENTMEMBER. If no level is specified, it is the level below that of member that will be assumed.

To know more about opening period refer
http://docs.oracle.com/cd/E26232_01/doc.11122/esb_tech_ref/frameset.htm?mdx_openingperiod.html

About the Author

More from this Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to the Weekly Blog Digest:

Sign Up