185
6
PraCtICal mdX For essBase develoPers
Gary Crisci
6.1 INTRODUCTION
Multidimensional Expressions (MDX) is a query language written for retrieving data
from multidimensional databases. Similar in some ways to Structured Query Language
(SQL) for relational databases, it is a exible language that allows the user to not only
retrieve data, but also to manipulate the data and perform numerous other func-
tions. MDX should not be considered a robust reporting language, due to its limited
CONTENTS
6.1 Introduction 185
6.2 Objectives 186
6.3 Housekeeping 186
6.4 MDX Basics 187
6.4.1 Terms and Syntax 187
6.5 Properties 190
6.6 Functions 190
6.6.1 Crossjoin 191
6.7 Query Structure 192
6.7.1 Axis 192
6.7.2 Where Slicer 193
6.7.3 Solve Order 196
6.7.4 ASO Member Formulas 197
6.8.1 Conditional Logic 197
6.9 Time Functionality 201
6.9.1 Period-to-Date 201
6.9.2 Analytic Dimensions 202
6.9.3 Time Balancing 205
6.10 Tips and Tricks 207
6.10.2 Calculating Optimal Sparse Dimension Order 222
6.11 Closing 223
186 •  Developing Essbase Applications: Advanced Techniques for Finance and IT Professionals
formatting capabilities, but instead is best used behind the scenes of reporting tools
that will retrieve data from a database and then apply formatting within the reporting
application. Although presentation quality reporting is not its strength, MDX can be
used for simple reporting requirements where formatting is not particularly important.
While MDX’s primary purpose is to retrieve data from multidimensional databases
using structured queries, it also serves another purpose for Essbase developers: MDX
is the mechanism used to dene member formulas for aggregate storage option (ASO)
databases. Leveraging the functionality of a subportion of an MDX query known as the
WITH MEMBER specication, MDX is used within an Essbase ASO outline to dene
calculations that range from simple equations to very complex formulas.
Essbase developers should note the dierence from block storage option (BSO) appli-
cations in that BSO cubes use the proprietary Essbase calculation script language to
perform functions in calculation scripts and outline member formulas. Beyond member
formulas, MDX also can be a useful tool for Essbase administrators to understand and
manage their applications. Administrators can leverage the MDX language to query
derive information that is useful for outline maintenance as well as tuning and opti-
mization. Keep in mind that while MDX can only be used in member formulas in an
aggregate storage database, the use of MDX to run a query applies to both aggregate
storage and block storage applications.
MDX’s query operations, ASO member formula language use, and metadata man-
agement functionality make MDX a very powerful tool that every Essbase developer
and administrator should experience and utilize.
6.2 OBJECTIVES
e goal of this chapter is to convey useful information, and my personal experience,
to other developers. is work is not meant to read like a computer science textbook,
and, as such, more technical denitions may be found in other literature. My objective
is to explain concepts in laymans terms using examples that should be familiar to both
novice and seasoned professionals. e chapter will be broken into various subsections
to allow the reader to use the chapter as a reference guide in the future. It is not neces-
sary to have previous experience with MDX; however, the reader should have a general
understanding of multidimensional database technology. Experience with other query
languages, such as SQL, would be helpful.
6.3 HOUSEKEEPING
For starters, the rst thing needed to run an MDX query against an ASO database is
an MDX client. Essbase Administration Services (EAS) and MaxL are the default MDX
clients for Essbase. ese tools are basically the same since EAS leverages many MaxL
commands behind the scenes. Note that there are also third-party MDX clients that
can be utilized to run queries. My personal preference when running MDX queries is
to use MaxL. Typically MaxL is thought of as the language for automating administra-