Database Reference
In-Depth Information
When using query tracking, the estimated cost is the average for all tracked queries.
The cost number for a specific aggregate view can be different in different selection
lists; for example, aggregate view 0, 0, 1/0, 2/0, 0 can show a different query cost
in the default selection list than it would show in a selection that includes tracked
queries in the analysis.
to compute the percentage improvement, divide the query cost value for the
aggregate view into the query cost value shown for storing only level 0 input cells.
But, wait, if the column measures cost, then why does it select those views with the
highest
cost first? Some have suggested that this column reflects the cost of
building
the
view. Based on the DBAg definition, this is clearly not the case. In fact, this column
appears to be poorly named. What is actually measured here is the utility of the view as
a function of the view size. All views are a fraction of the size of the level-0 view. With
two views of equal size, the one that could be used to answer more of the possible query
“questions” will be more useful and have the higher “cost” as shown in the wizard. The
use of query tracking takes this concept one step farther and assigns a higher utility or
“cost” to those views that, based on past experience, will be queried more often.
7.5.4 So What Is Aggregated and How Is It Used?
Which brings us to repeat a comment made earlier: Aggregations are like “stepping
stones,” different ones can be used to cross the river without getting your feet wet (i.e.,
querying the input-level data). now, if you supply a query “hint” in your outline so that
[1st half] and [2nd half] are not aggregated, does that mean any query at the half level
will not be able to take advantage of aggregations? no. It could use a view that was
Aggregated at the Quarters. In fact, the situation is even better than that. A query can be
run against any view whose aggregation level on each dimension is less than or equal to
the aggregation level of the query (for the same hierarchy). you do not have to Aggregate
all dimensions. I summarize this in a rule:
r12: A query will be run against the smallest view whose aggregation level on each
dimension is less than or equal to the aggregation level of the query (for the same
hierarchy), so you do not have to create Aggregated views on all dimensions.
The process of building an Aggregated view is itself a query. We can evaluate the
queries needed to build the views in Figure 7.10 against the queries above it. take a look
at view 18 (on the 19
th
row). using the rule above, of course, it can be calculated from
view 0, the level-0 view, but it also can be calculated from view 4. Looking further, we
find it also can be calculated from views 6, 8, 10, and, 15. The column
Database Size
in Mb
is cumulative. The size of any single view can be calculated by subtracting the
Database Size of the row immediately above. When we do, we see sizes for views 0, 4,
6, 8, 10, and 15 of 6.53125, .041871, 1.524792, .428817, .373747, and 6.25567 mb, respec-
tively. With these six views to choose from, we might as well run our query against the
smallest, which would be view 4.
returning to the eight views for [1st half] and [2nd half], we see by looking in the
third column that these are views 4, 5, 6, 7, 13, 18, 20, and 23. notice that view 18 is based
on one of its fellow [1st half] and [2nd half] views, view 4. Suppose we eliminated view
