Databases Reference
In-Depth Information
Take a close look at the functions, most of them are intuitive as to their function and
you will also notice a complete array of functions that are suspiciously similar to the
column functions in relational SQL. Again, this is because an ASO database does not
store the complete set of data like a BSO database does. ASO only physically stores
the data at the lowest levels and is a lot like a relational database where you depend
heavily on functions such as
SUM
,
MAX
,
and
ABS
to massage the data as it is delivered
to you.
MDX function examples
Here, we will show you the same data query function twice. One will be in the
format used by the Essbase Calculation Script language and the other example will
be in the MDX data query language. The reason for this is to illustrate how vastly
different each language is.
The
@ANCEST
function is a very commonly used function. In an Essbase database,
where the data is stored at all levels, you can easily ask for the ancestor member. In
an ASO database however, the data in most cases is dynamically calculated, so you
have to ask for it in a different way so Essbase can interpret your request properly.
•
Essbase Calculation Script
Syntax:
@ANCEST (dimName, genLevNum, [ mbrName])
Example:
@ANCEST(Product,2,Sales)
•
MDX Query Language
Ancestor
Example:
(
Sales,
Ancestor(
Product.CurrentMember,
Product.Generations(2)
)
)
The command above assumes you are keying off of the member currently
selected. What will be returned is the value of
Sales
from the ancestor of
the member in the product dimension and the Generation level 2.
