Practical mDx for Essbase Developers - Developing Essbase Applications - page 204

(Qtr2) 16473783.25 33140973.00

(1st Half) 33140973.00 33140973.00

(Jul) 4879566.25 38020539.25

(Aug) #Missing 38020539.25

(Sep) #Missing 38020539.25

(Qtr3) 4879566.25 38020539.25

(Oct) #Missing 38020539.25

(Nov) #Missing 38020539.25

(Dec) #Missing 38020539.25

(Qtr4) #Missing 38020539.25

(2nd Half) 4879566.25 38020539.25

(MTD) 38020539.25 38020539.25

(Time) 38020539.25 38020539.25

Note: take notice of the use of hierarchize() function with the keyword PoSt. By
default mDx returns results with parents before children. use hierarchize(,PoSt) to
return results in the familiar Excel add-in format.

It is now easy to create some other members like quarter to date (QtD) and simply
tweak the member formula. Add member [view].[QtD] and add the following member
formula.

SUM(PeriodsToDate([Time].Generations(3), [Time].CurrentMember),
[View].[Periodic])

If this is a daily model, you can add a [view].[mtD] for month to date and simply
change the generations layer level to (4) (or whatever is appropriate for your application).
At this point, I am going to say that while the preceding example is an effective way
to implement period-to-date functionality, it is actually not my preference in many situ-
ations. In fact, Because it uses Dynamic calculation, it has the same performance prob-
lems that we found when we first started discussing time-Functionality.
Dan Pressman discusses in Chapter 7 (how ASo Works and how to Design For
Performance) an even more effective way to structure the primary hierarchy in the time
dimension to optimize Stored hierarchies (see Section 7.6.2.1, time Spans (ytD, QtD)
using Stored hierarchies).
If you decide not to use the approach Pressman suggests, the basic rule of thumb
when writing mDx in an ASo database is to leverage nonlevel-0 members of Stored
hierarchies whenever possible . This is because there is a good chance that many of
those members will have been preaggregated in a view during the aggregation process.
So, if you have a choice when setting up [ytD(mar)] to sum [Jan] through [mar] or
point to Qtr1, which is the parent of [Jan], [Feb], and [mar] in the primary Stored hier-
archy, reference Qtr1 if possible rather than forcing a pure dynamic calc of the level-0
members. In fact, you should use this rule of thumb whenever you write mDx not just
when writing it for the time dimension.
returning to Pressman's solution in Chapter 7, the trick is how you make what might
look ugly and unusable to a user more palatable using a view dimension that is clearly
explained in that chapter. one other thing you will need to know for his chapter is how
to do some string concatenation in mDx. Pressman's approach requires a consistent
naming convention and, by doing so, allows us to use mDx text functions to dynami-
cally generate the name of the nonlevel-0 member we want to capture in our formula.