Database Reference
In-Depth Information
For his method, you will need to understand and leverage an mDx formula like the
following:
StrToMbr(ConCat([Time].CurrentMember.MEMBER_NAME, "_YTD"))
This function takes the member name of the current member and concatenates it
with the text string “_ytD.” The Strtombr() function converts the newly concatenated
string that is being returned to an Essbase member name. The result of this formula
will point to a nonlevel-0 member of the primary Stored hierarchy in Pressman's exam-
ples. Finally, you do have to make it a tuple with the [Periodic] member of the view
dimension:
(StrToMbr(ConCat([Time].CurrentMember.MEMBER_NAME, "_YTD")),
[Periodic]).
In my opinion, Pressman's method is the most efficient way of calculating period to
date results for ASo cubes with large time dimensions; if your time dimension is not
very large, then some of the other approaches discussed above may be easier to imple-
ment and maintain.
6.9.3 Time Balancing
time balancing is another feature BSo developers enjoyed that was missing from ASo
when it was first released. time Balancing has since been added to ASo as an option;
however, it is often not practical to utilize ASo's native time balancing functionality
because it limits the developer in a number of other areas.
First, it is important to understand what time Balancing is and why it is necessary.
In many applications, there are certain measures that do not aggregate over time, such
as balance sheet accounts. many applications load the monthly closing balance to the
balance sheet account and not the monthly activity. In this case, it is not accurate to
aggregate balances period over period. Instead, the proper result for upper level, subto-
tal members, is to reflect the last balance. Sometimes it may be necessary to capture a
starting balance. This issue also can be applied to other measures, such as head counts
and inventory measures.
If the application design is simple, it is possible to use the native time balancing func-
tionality. however, very often this is not optimal. For starters, in order to use time
Balancing in ASo, you must tag a dimension as Account. While an Account dimension
was central to BSo applications because of its unique functionality, the term carried over
to ASo without any of the benefits. In fact, setting a dimension as the account dimen-
sion can often be very bad for performance because tagging a dimension as an Account
dimension sets a dimension storage type to a Dynamic hierarchy. If the account dimen-
sion is large, this can be very bad for performance. Secondly, in order to leverage time
Balancing in ASo you can only have Stored hierarchies in your time dimension, which
may be an issue for you depending on your requirements.
Fortunately, through the use of mDx, you can achieve time Balancing results. one
example may be that you only have one or two members that need to be time Balanced.
If so, depending on if you already have a view dimension or not, you may just opt to
create a member in the measures dimension to deal with time Balancing.
As an example, determining headcount may clarify this technique. Create a mem-
ber called Load_headcount and, in the data Load rules, load headcount data to this
