Database Reference
In-Depth Information
([Jun], (Period]) as opposed to [Jun], [ytD]). All data is stored in [Period] (which is the
only stored, nonformula member in this dimension). Then mDx formulas for [ytD]
and [QtD] redirect queries made against ([Jun], [ytD] ) to ([ytDd(Jun)], [Period])
using concatenation functions. (
Note:
only the new [timeSpan] dimension has been
shown in Figure 7.14.)
There is one issue with these formulas as written. There is no mDx that translates
queries for ([ytD], [Qtr3]), and the other upper-level members of the [Full year] hierar-
chy. What should [ytD] be for [Qtr3] be? It is clear, if [Qtr3] is in the past, but suppose
we are part way through it, say [Jul]? you might want it to be equivalent to ([ytD], [Jul])
not only for the current year, but also for prior years for variance analysis (otherwise
you would be comparing a one-month value to a three-month value). I have found that
different organizations have different conventions. Assuming you have a substitution
variable that indicates the most recent closed month and the current year, it would be
simple to add special code for upper-level members of the [time] dimension code to
redirect, for example, ([Qtr3], [ytD]) to ([ytDd(Sep)], [Period]) assuming that [Sep]
for the queried year was closed. If [Sep] was not yet closed, the code could redirect to
[ytDd(Jul)] or [ytDd(Aug)] or, if none were closed, it could simply provide a value of
#missing. This also would be a good place to code a special case providing a value of
#missing for February 29th on nonleap years.
Finally, this technique becomes enormously successful when used in a cube with daily
time periods, as in Figure 7.15. (
Note:
The daily cubes shown below have [timeSpan]
members similar to those shown in the figures above; the mDx is not shown here due
to space constraints.)
There is one problem with the cube in Figure 7.15. The cube still works, but when
we look at the Statistics tab in Database Properties (see left side of Figure 7.16), we see
that the number of bits for the [time] dimension that increased from 4 (Figure 7.5)
to 11 (Figure 7.13) in the monthly cube, now go to 37 in the daily cube (see left side of
Figure 7.16). That does not seem like a bad deal. A little over 3 times as many bits in
exchange for a cube with room for 30 times as much detail.
Figure 7.15
ASOsamp with Daily time periods. (From Oracle Essbase Administration Services. With permission.)
