Database Reference
In-Depth Information
This could be expressed by a slight rewrite of the Query Calculation Type for the second type of query: Essbase
calculates the query at the time of retrieval based on a View that retains the level-0 input data for the base
dimension.
Why does ASo have this limitation anyway? We actually can see why by returning
to the construction of the bitmap. The algorithm selects the hierarchy with the longest
required bitmap for any dimension. remember that within the set number of bits, the
actual encoding on the record on disk and in memory can reflect only
one hierarchy at a
time.
A member, even if it has one or more alternate incarnations as a shared member in
other hierarchies, cannot be simultaneously in two places at once. In ASosamp terms,
at least while looking at the bitmap encoding, [hDtv] cannot be described as a descen-
dant of both [All merchandise] and [high End merchandise] simultaneously. Similarly,
a store cannot be represented in the bitmap as both [Brick & mortar] and [10000] square
feet. The bitmap cannot simultaneously represent both the base member name and the
Attribute name (in a sense, the Attribute name is effectively an alias); the combination
of the two does not exist and, therefore, cannot be Aggregated.
If the bitmap does not show the intersection of [Brick & mortar] and [10000], there is
no way that an Aggregated view could.
The solution, in extreme cases, would be to load the Attribute dimension as a Standard
dimension. With separate bits reserved in the bitmap for the now separate dimensions, a
query can find the fact data rows that are both [Brick & mortar] and [10000]. This solu-
tion does have its costs, however. The Attribute-to-base relationship is no longer supplied
at the time of outline building. Instead, it will be added to every row of fact data to be
loaded. Additionally, it may result in exceeding the ASo limit on the number of views.
ASo cubes are limited to having a maximum of 2**52 views or “Stored dimension
level combinations” (see DBAg v11.1.2.1 Appendix A table 239). This number can be
calculated by taking the product of the levels as seen in Figure 7.5. The product of these
levels 1 * 1 * 4 * 2 * 2 * 2 * 3 * 2 * 6 * 7 * 7 equals 56,448. If the [Square Footage] Attribute
dimension is converted to a standard dimension, its 2 levels are removed from the origi-
nal 7 of the [Stores] dimension and become their own member of the calculated product
of the levels: 1 * 1 * 4 * 2 * 2 * 2 * 3 * 2 * 6 * 5 * 2 * 7 equals 80,640 views.
This issue is an important enough design consideration to warrant a rule:
r8: Designs requiring queries of multiple Attributes of the same base dimension
may suffer performance degradation; evaluate and consider alternatives.
Another alternative, particularly for small Attribute dimensions, would be to build a
compound attribute dimension. There is no logical candidate for this in ASosamp, but if
an auto manufacturer were to have a cube of its Skus (stock-keeping units) as well as have
two attribute dimensions of [make] and [Color], and these were to be queried frequently
together, there could be performance issues. Instead of converting one or both of these to
standard dimensions, it might make sense to combine the [make] and [Color] attributes
into a compound attribute dimension. This technique is shown in Section 7.6.2.5.
It also should be noted that the “varying Attributes” feature appears to effectively
implement this compound Attribute in a more easily maintained manner. There is no
guidance in the documentation for the effect of this feature on performance and as of
this writing, I have not tested it.
