Database Reference
In-Depth Information
time dimension with a primary Stored hierarchy and then creating secondary Dynamic
hierarchies with members like [ytD(Aug)] or [QtD(mar)]. Because these members are
part of a Dynamic hierarchy, the developer is able to add mDx member formulas to
these members to calculate the needed results. For example [ytD(Aug)] has the mDx
member formula [1st half]+[Jul]+[Aug]. This is somewhat better than what some devel-
opers may do, which is to just hard code [Jan]+[Feb]+[mar]+[Apr]+[may]+[Jun]+[Jul]+
[Aug] or to use the Sum() function as Sum([Jan]:[Aug]). While these are all simple, they
are more dynamic in nature. At least the ASosamp version tries to make use of the [1
st
half] member from the Stored hierarchy.
In reality, a developer does not even need to use mDx to do simple period-to-date
functionality like this. Instead of using mDx, the developer could just use shared
members and create alternate rollups where [ytD(Feb)] has two children [Jan] and
[Feb], which are shared from the primary hierarchy. to expand upon this approach,
the developer could move the [ytD(Feb)] member to generation-2 of the time dimen-
sion and switch it to a Stored hierarchy instead of a Dynamic hierarchy. Then, during
aggregations, the developer can enable alternate rollups to be considered for aggre-
gate views. This will yield very good performance and works well on smaller ASo
applications.
There are two factors with the above approach that make it less than desirable. The
first is that it requires the developer to create many new members in the alternate
rollups. Whether using mDx or shared members, the developer has to create 12 new
ytD members, potentially 12 QtD members, and perhaps a few others depending on
requirements. While that may be acceptable in a monthly model, what happens when
building a daily model or a fiscal year crossover model where years and months are in
the same dimension? The developer could be in a situation to create hundreds of PtD
members.
The second problem with this approach is that it decouples the relationship between
similar time members. [Jun] and [Jun_ytD] are now two different members in the
database. If a user has a report with 12 period members in the header column and they
want to see ytD values, they must replace all 12 members with different ytD mem-
bers. This is inconvenient and does not exemplify ease of use. [Jun] and [Jun_ytD] are
not unique content; the difference between them is a matter of context. to alleviate this
discontinuity and reduce hierarchy complexity, a developer needs to consider a view
dimension, or what I like to call an Analytic dimension.
6.9.2 Analytic Dimensions
The premise of an analytic dimension, in general terms, is a dimension added to a data
model with one default stored member. All other members in the outline are calcu-
lated members, or label only members that are only used to help group-related cal-
culated members. generally, the analytic dimension is not related to the subject data
in the model. to this end, you will find there are some general categories for analytic
dimensions and you will be able to reuse the same code in multiple models. Analytic
dimensions work by leveraging the power of tuples. The default member of the ana-
lytic dimension joins with the members of other dimensions to create multidimensional
members against which you can execute functions.
Assume you are working with the ASosamp database and you add a new dimen-
sion [view] and set it to be a Dynamic hierarchy. Beneath the dimension root member
[view], add a member [Periodic]. update any Load rules you may have to add [Periodic]
