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
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.
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 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
