Database Reference
In-Depth Information
log(Count( hierarchies)) was added when this situation was detected. nevertheless, this
view could not be used to report a roll-up of [high End merchandise] directly because
there is no bit pattern that uniquely identifies the three members of that hierarchy
([hDtv], [Flat Panel] and [Stored member]).
ASo must do one of two things if it is to use this view (or the level-0 view for that
matter) to roll-up [high End merchandise]. It either must translate the query (by look-
ing at the outline) to a bitmap mask that matches [Stored member] and [hDtv] and
[Flat Panel] in one query or alternatively must translate the bitmap found on the view for
this dimension (0/0) to one that has the hierarchy information queried (0/1). my guess
(based on watching file I/o in resource monitor) is that it does the latter, using data that
is stored in the file ess001.dat in the metadata subdirectory of appname/dbname (as
opposed to the data file ess001.dat in the default subdirectory). In any case, this would
not be a costly operation once the row had been brought into memory.
In contrast, let us look at [Stores]. As we know, [Stores] has two attribute dimensions
that function as alternate hierarchies [Store manager] and [Square Footage]. An inspec-
tion of the detail in the outline reveals that every [Store] has a [Store manager], so [Store
manager] is an implied share of [Store] and even though the view we are looking at has
no information in the bitmap
about store managers individually, a query of the top-level
member [Store manager] could be answered directly without either of the two types of
translations mentioned above; it is really a query of its implied share “parent” [Stores].
Suppose a query for a specific store manager was made? There is no possible translation of
the bitmap in view 4 that could answer that question. The association of store manager to
store was done at level-0 in the [Store] hierarchy. This view only has data about the com-
posites found in [Brick & mortar] and [online]. The association is lost within this view.
An even more curious observation can be made about the attribute [Square Footage].
Inspection of the detail in the outline reveals that [online] stores never have a [Square
Footage] attribute, only level-0 descendants of [Brick & mortar] stores have a [Square
Footage] association. nevertheless, we cannot query any [Square Footage] information
from this view 4
even for those stores that are descendants of [Brick & Mortar].
The coinci-
dental fact that only descendants of [Brick & mortar] have [Square Footage] is just that, a
coincidence. Even though we know all [Brick & mortar] descendant stores have a [Square
Footage] association in view 4, we have no idea of which stores have what association.
7.5.3 Aggregate View: What Is “Query Cost”
Let us return for a moment to the eight views for the half level mentioned above for
view 4. Admittedly, when you look at the sizes of these eight views, they are relatively
small, but if they are rarely used, why build them and why are they so high on the list of
views to be built? The answer is because they have a high Query Cost.
no definition of how Query Cost is calculated can be found in the documentation.
Some indication of the calculation in the Query Cost column (as seen in the wizard
dialog in Figure 7.10) can be found in the DBAg. The following is reprinted with per-
mission from oracle Corporation,
Oracle Essbase Administrators Guide Release 11.1.2.1
(oracle Press, 1996, 2011) Chap. 64, p. 993):
The Relative “Query Cost” performance improvement
The Query Cost number that is displayed by each aggregate view in the list projects
an average retrieval time for retrieving values from the associated aggregate view.
The default view selection estimates the cost as the average of all possible queries.
