File Library

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

Finally, time will be spent discussing an approach I have regularly used and successfully taught to two 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 couple of 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.