Database Reference
In-Depth Information
Figure 7.17
ASOsamp with Daily time periods, with Upper-level data loaded. (From Oracle Essbase Administration
Services. With permission.)
note that we have included a not strictly required [12 ytD] member. By adding this
last member, we can build in some additional performance improvements. With a bit of
extra code added to the ytD and QtD formulas, we can redirect queries to last-day-of-
the-month dates, such as 06-30 and 08-31 to the ytD members. Why would we want to
do this? Because the last days of the month are usually the most frequently queried and,
frankly, the other days of the month are rarely queried past month end. Thus, if we write
a little bit of code, we are already into dynamic mDx for the concatenation anyway. We
can now afford to include a hint in the outline instructing ASo not to consider inter-
mediate levels of aggregation on the ytD stacks, knowing that it will only have to go to
the level-0 view (in the [time] dimension) if mid-month data is needed.
We could even modify the whole procedure to use a generic daily stack and a separate
monthly dimension. This would not decrease the number of levels, but it might make
some developers happier because they do not have to set up the 12 monthly stacks. But,
that is a one-time cost. In any case, I would advise you to use either the solution in
Figure 7.17 or this last solution with a separate [year], [month], and [time] dimensions
to avoid having 371 levels.
7.6.2.3 Loading Data with the “Natural Sign”
Loading Data with the “natural Sign” is
one of the simplest and most powerful changes you can make to your cube design. As
you know, Stored Consolidation can only be additive. Accounting applications often
have very large “Accounts” dimensions, and users usually want to see positive balances
even for those accounts, such as revenues and Liabilities, that are maintained in most
accounting systems with negative values. This type of data storage (with revenues and
Liabilities normally having negative values) is known as “natural Sign” recordkeeping.
If the data for these negative values is instead converted to the positive values pre-
ferred by the users for reporting, then the outline will require negative consolidation at
