Database Reference
In-Depth Information
Fully qualified member names become even more important when your database
has duplicate member names enabled. When duplicate member names are enabled,
it becomes necessary to distinguish between two members with the same name.
For example, you may have a geography dimension with ambiguous city names, such
as Portland: [geography].[uSA].[maine].[Portland] and [geography].[uSA].[oregon].
[Portland]. Again, you do not have to use all levels when qualifying, just enough to
clarify any ambiguities, such as [maine].[Portland] and [oregon].[Portland], while
[new york City] is probably unique and does not require [new york].[new york City].
As a general rule, I do not advocate enabling duplicate member names. While there
are times where it can be useful, I stay away from it for various reasons, such as diffi-
culty with hierarchy maintenance, potential for confusion with end users, and potential
misinterpretation of information in reports. If you are using duplicate member names, I
suggest reading the member Specification section of the Essbase technical reference for
a detailed explanation of the best utilization of fully qualified member names.
6.5 properties
It is hard to get into details about mDx properties without rewriting the section in the
technical reference. Because the technical reference does a very good job at explaining
this, I am going to limit this section to briefly explaining what properties are. This will
demonstrate why you should research them further and, more importantly, why you
might use them and how they may not be as useful as hoped.
There are two types of properties that are exposed in mDx: intrinsic and custom. The
custom properties include mEmBEr_nAmE, mEmBEr_ALIAS, LEvEL_numBEr,
and gEn_numBEr. how the results are returned and what you can do with them is a
different story. generally, this information when returned via a query is not in a format
that is very useful. Instead the information returned is likely of more use when pushing
through to another reporting type of system, perhaps through the application program-
ming interfaces (APIs) where that reporting system can better interpret the results.
of more use to most mDx developers are custom properties. Custom properties
in Essbase are defined by Attributes, user Defined Attributes (uDAs), and alias table
names. Later in Section 6.10 (tips and tricks), I will explore ways to use custom prop-
erties in your queries. Additionally, you can review some of the sample queries to see
examples where custom properties like DImEnSIon ProPErtIES are used.
6.6 FunCtions
Functions are used to make writing mDx queries easier and expand the robustness of
the analytics. Functions can return metadata (i.e., member sets, member properties,
etc.) or they can return numerical results. Functions eliminate the need to explicitly
define what you are looking for in a query. For instance, if you wanted all Cola prod-
uct codes in your query from Sample.Basic, you could explicitly state the set {[100-10],
[100-20], [100-30]}. using a function can simplify what needs to be specified. Instead
of spelling out each member, you could use a member set function like Children(). For
example, note that the style conventions Children([Colas]) or [Colas].children are inter-
changeable, although you may find one is more readable depending on your preference.
Be careful when using a function that you understand the nature of dynamically gener-
ated results. In the example I have provided, it is easier to use Children([Colas]) than it
