Database Reference
In-Depth Information
Figure 7.16
Database Properties-Statistics for cubes in Figure 7.13 (left) and Figure 7.15 (right). (From Oracle Essbase
Administration Services. With permission.)
however, the number of levels for the [time] dimension have risen from 4 (Figure 7.5)
to 18 (Figure 7.13) in the monthly cube, and now balloons up to 371 in the daily cube (see
left side of Figure 7.16). There was a hard limit of 2**52 views (as described in Section
7.4.3.2) for a cube, and that calculation was based on the number of levels per dimension.
We are in no danger on this cube, but it points out something else: Even though we did
not build a Stacked dimension 366 members high going from Jan 1 to Dec 31st, it calcu-
lated as if it were. Because each stacked month is in a separate alternate Stored hierarchy,
there has to be an Aggregate view for each of the 12 ytD months to make this cube
really perform for any of the 366 days of the year. one might think that the aggregation
engine could build all 12 views in a single pass of the input-level, but this does not seem
to be the case. While Aggregated views can be built from earlier Aggregated views, my
observations of the resource monitor seem to indicate that each view is built separately.
Therefore, this design, while better than an all-mDx design, might not be optimal.
7.6.2.2 Upper-Level Data Loads
The key thing to understand is that even though ASo
is great at aggregation, sometimes it might pay to do some of the work for it in advance.
try loading the data for the prior month-end ytD total as a precalculated value. This
can be done with a special month-end process or accomplished by always loading each
day to both the daily and the full-month members. This process can be accomplished
easily in one pass with a simple Load rule and, given the capabilities of the load buf-
fer and slices, would have negligible additional cost. Finally, with version 11.1.2.1 of
Essbase, we have one additional option: running a calculation script to Aggregate the
prior month-end members and the daily members for the most recent month into a full-
month member. Both the use of a calculation script and the “double loading” method
have the additional advantage of ensuring that the monthly total and the daily detail
tie. A separate full-month process could have a bug generating different results than the
sum of the daily loads.
But wait, I thought you could not load to upper-levels in an ASo cube? yes, that is
true, but nothing prevents you from having an upper-level-in-name-only member, one
that is actually at level-0. you can see how this could be done in Figure 7.17, where the
bottom of each monthly stack has been changed to have a separate level-0 End-of-the-
Prior-month-ytD member named [01 ytD], [02 ytD], etc. There still will be the [Boy
ytD] member for the prior-year-ending ytD, which will be at the bottom of the [Jan]
stack. By doing this and not linking the monthly stacks, the number of levels decreases
to a much more palatable 37 (see the right side of Figure 7.16).
