Database Reference
In-Depth Information
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 define member formulas for aggregate storage option (ASo)
databases. Leveraging the functionality of a subportion of an mDx query known as the
WIth mEmBEr specification, mDx is used within an Essbase ASo outline to define
calculations that range from simple equations to very complex formulas.
Essbase developers should note the difference 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
The goal of this chapter is to convey useful information, and my personal experience,
to other developers. This work is not meant to read like a computer science textbook,
and, as such, more technical definitions 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. The 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 first 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. These 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
