Developing Essbase Applications

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 Advanced MDX 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.1 Querying Metadata 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

information about the Essbase outline. Using simple queries, the administrator can

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 layman’s 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-

tive tasks, like loading data or executing an aggregation. While this is accurate, MaxL

also allows users to execute MDX queries within MaxL scripts. Although MDX is not

a reporting language, there are a few settings that can be altered to manipulate the way

the results look when Essbase returns them via MaxL. Furthermore, MaxL has a little