Database Reference
In-Depth Information
various points (Sales: total expenses) and you will not be able to take advantage of Stored
consolidation. The design alternative being suggested here is to load the data with the
“natural Sign” and let the data “make the decision” of when to add and when to subtract.
of course, your users still do not want to see negative numbers when they look at
expenses. The answer is to flip the sign dynamically after the Stored consolidation is
completed. This is best done by attaching one or more uDAs to those accounts with
negative natural Signs. This information will be available from your accounting system.
As an added benefit, you will be able to take advantage of this information when design-
ing in-cube variance calculations, ensuring that an increase in expenses is reflected as
a negative impact. The dynamic flipping of the sign based on the uDAs is best accom-
plished using mDx in your smallest dimension—the one with the fewest Stored values.
An Analysis dimension, such as [time Span], is ideal for this purpose. The normal
[timeSpan] dimension would be as shown in Figure 7.14 [timeSpan]. The same dimen-
sion with natural Sign Processing is shown in Figure 7.18.
The reason to use the smallest dimension is that the equations and extra members
shown in Figure 7.18 will have to be repeated for each Stored member in the dimen-
sion. For example, if instead of [timeSpan] the [Scenario] dimension was used, it would
require the types of additions for both [Actual] and [Plan].
one issue with this approach is that while the accounting system will be able to sup-
ply you with the Expense/Liability for the uDAs of the level-0 members, it might not
maintain them for the upper-level accounts. In this case, you will have to write SQL to
determine which upper-level accounts require the sign-flipping uDAs. Any upper-level
that is homogeneously made up of accounts with the Expense/Liability tag should
itself get that tag. The problem arises for the nonhomogeneous accounts, such as [net
Income]. one solution is to create an exception table in which your users can indicate
whether these accounts should be flipped. however, to minimize maintenance I suggest
implementing the following rule in your SQL for a default setting for the nonhomoge-
neous: Set the uDA to the value given to the first-born level-0 descendant. This solution
will, based on my experience, generate the correct answer in over 99% of cases. This is
because people generally design hierarchies to show positive values first and offsetting
negative values farther down.
The most common place where this rule will fail is on Cash Flow hierarchies. In that
case, you will have to use an exception table.
Figure 7.18
Natural Sign Processing. (From Oracle Essbase Administration Services. With permission.)
