Database Reference
In-Depth Information
at the dimension level “Information” tab “hierarchy Information”->“hierarchy”->
“hierarchies Enabled”), a
dimension
can be both “Stored” and “Dynamic.” The indi-
vidual hierarchies within it are then each either Stored or Dynamic. Please consider the
use of the term “Stored dimension” to be interchangeable with “Stored hierarchy” for
a “multiple hierarchies Enabled” dimension. This is also true for the terms “Dynamic
dimension” and “Dynamic hierarchy.”
7.3.4.2 Dynamic Hierarchies
you may have noticed when modifying an outline that
you can add lower-level members to be consolidated or write an mDx formula for a
level-0 member of a Dynamic hierarchy. This is true even if the “Data Storage” member
property is set to “Store Data.”
What then of a Dynamic dimension level-0 member with the “Data Storage” mem-
ber property set to “Store Data” that does
not
have a member formula? It behaves
exactly like a member of a Stored dimension in queries. In fact, we will see that a flat
Dynamic dimension with “Label only” or “no Consolidation” at the dimension level
behaves exactly like a Stored dimension. This is really an exception to my emphatic
use of
only
when I said above “… the above description/analogy applies
only
to Stored
dimensions …” and leads to a slight technical modification of that rule:
r2: Wherever possible, data should be calculated from Stored nonformula members.
This rule originally said Stored dimensions. “Stored members” includes level-0 mem-
bers of Dynamic dimensions that do not have mDx formulas.
remember, of course, that a Stored upper-level member of a Dynamic hierarchy cannot
take advantage of aggregation ;in other words, it cannot be materialized in an aggregation.
7.3.4.3 Dynamic Query Performance and the Critical Importance of Solve-Order
he
details of how mDx should be written are discussed in Chapter 6 (Practical mDx for
Essbase Developers) by gary Crisci.
In this chapter, however, I deal only with the performance impact of dynamic opera-
tions, be they mDx formulae or dynamic outline consolidation . It is critical to realize
that all queries are eventually resolved by the ASo query engine into a combination of
queries against Stored hierarchies or stored members. In Chapter 6, Crisci notes that
member formulae are seen in the mDx version of queries as “WIth” clauses. It is as if
the base part of the mDx (the Select statement) is executed against the Stored members,
and then the WIth clause is executed on the result.
Bad design can result in a dynamic query being split into multiple stored queries.
Query-based aggregation will then speed up these generated stored queries, but the
results will still have to be combined in the ASo cache by the mDx processor. And that
adds to query response time.
understanding that all queries are eventually resolved to a dynamic combina-
tion of queries against stored members is important. Also, it is important to remem-
ber that aggregations are stored query results with some or all dimensions being at
upper-levels. In fact, one could call the input-level data the level-0 aggregation (and
because aggregations also are known as views, this chapter and the literature will
often refer to the input-level data as the level-0 view). knowing this and thinking
