File Library


MDX Simplified: A Methodical Approach to Writing Complex Functions
Topic: Essbase
Owner: Jennifer Hanks
Date: 2014-06-24
Subtopic: Tips and Techniques

With ASO becoming the future of Essbase cubes MDX is the language necessary to write member formulas. For someone new to MDX this may seem a daunting task, as it is different than many other languages a user may have used before. MDX is not just for the technical side of the business; it can easily be learned and written by the functional side.

This session will start with a quick foundation of ASO and the role of MDX in an ASO cube. Before writing an MDX formula it is helpful to understand the different types of functions and when they can and should be used, so a description of the types of functions available will be covered. As the types of functions are discussed, examples of common functions in each category will be given, along with relating them to their BSO equivalent where applicable or useful. There will also be a short section on solve order as it plays a key role in producing accurate formulas.

A good portion of time will be spent discussing an approach I have regularly used and successfully taught to several MDX newbies. It is a pretty simple methodology wherein you build formulas in a methodical process after conceptually deciding what the formula should accomplish and potentially building the formula in Excel first as visualization. Starting with the simplest version of the formula, slowly adding in the arguments. By approaching the process methodically, validation can be done at multiple stages of the formula, ensuring you are not having to comb through lines of code to figure out what is wrong if you don’t get the expected results. Multiple saves, as stepping through building the formula allows validation that the right data set is being impacted and that the results for each piece are correct. By using a methodical approach, it allows a more complex formula to be built. You can slowly layer in various different functions or perform multiple different calculations to get to your intended results. You can start with a Case statement to limit the data set impacted and end up performing a Sum of a Tail of a Filter on a CrossJoin of a MemberRange, and it can be efficient too. It can also be useful when troubleshooting a formula, as breaking it down is sometimes the key. When teaching MDX to a few novices, the approach has helped in brining an understanding of what each step is really doing. In the beginning, the process may take longer than just writing a formula outright, but if the formula is complex enough, the process may actually take less time, as you are validating at each step of the way and building on a solid framework.

Finally, some troubleshooting tips and lessons learned will be shared. When a formula breaks or the business changes how a calculation should be performed, utilizing the same framework, a complex formula can be evaluated to determine where corrections or changes are necessary to produce the intended result. A summary of lessons learned or best practices that I have found in my years of working with MDX will conclude the session.

This will be an expanded version of a KSCOPE13 session and will include more examples with a wider variety of formulas used. Examples will also provide further illustration that there isn’t necessarily one right way of doing things and that different functions can be used to achieve the same end result. After receiving questions from several attendees after the session concluded and being able to assist them with looking at their real world formulas, I hope the expansion of topic content and formula usage will help the real world application be even more apparent to those in attendance.


Download File   JHanks.MDXA.Presentation_06-24-2014.pdf

Become a member of ODTUG to gain access to more than 12,500 files in our technical database.

  • Not a member? Click through to the topic of interest to browse a list of available presentations.
  • Already a member? Log-in here to access the full database


Click here to see the Full Techincal Resource Database