Database Reference
In-Depth Information
cost in terms of impact on the length of the bitmap and, therefore, database size and/or
performance. Stated as our fifth rule:
r5: Alternate hierarchies, whether Dynamic or Stored or Attribute, are almost
always cheap … give the user what they want.
At most, the cost of adding an alternate hierarchy is the addition of log(# of hierar-
chies) bits. This cost occurs only if any of the secondary hierarchies have a nonshared
level-0 member as noted at
[DBAg 5]
. Stated another way, if the set of secondary level-0
members is a subset of the set of primary level-0 members, then the addition of log(# of
hierarchies) bits is not required.
Note:
The hierarchy with the longest bitmap (whether it is listed first or not) is known
as the primary hierarchy, and the remainder are the secondary hierarchies.
unless you have an alternate hierarchy with a very expensive design, add alternates
without fear. I have had a number of clients who have asked for dictionary-style hierar-
chies that listed all of their members alphabetically by member name or by description,
or even, ordered by a secondary alias table. By doing this with a Dynamic hierarchy with
label-only section headings, there was no cost to the length of the bitmap, only in build
time for the outline. In fact, let us include a rule about “Label only” members:
r6: Label-only members have no cost; use them to enhance your cube's readability.
Because the number of alternate hierarchies in a single dimension typically does not
exceed 8 or, in some cases, 16, then only 3 or 4 bits would be added. Even in the extreme
case where the number of hierarchies reached the maximum of 256 (requiring 8 bits),
we would have to assume that the count of level-0 members would be so high that these
8 bits would not be material. next, note that because the bitmaps of the shorter hierar-
chies overlay the longest and that it is only in the special situation found in
[DBAg 5]
, which
is dependent on hierarchy order, that performance will be little affected by changing the
hierarchy order. Thus, our next rule:
r7: Changes to hierarchy order are cheap or free, so design for user convenience.
Just to make things clear, changes are cheap in terms of cube performance because
the bitmap length is nominally impacted, if at all. There may be a one-time cost in terms
of reorganization if you make the change with data already loaded.
A caveat to r7: In a somewhat cryptic conversation with developers (cryptic due to
the subject's encroachment on “trade secrets”), it was noted that situations wherein
some of the level-0 members were
not
shared (i.e., did not appear in the first, primary
hierarchy), alternate hierarchies can result in difficult (longer) run times when the
outline must be restructured. This situation is similar to that shown in Figure 7.6
