Database Reference
In-Depth Information
tracking and the implementation of recommended views based on the collected usage
data. to provide one example of how to utilize Query tracking, this deployment strategy
might be used to optimize aggregations for a cube that was reloaded weekly:
1. Create and load the cube, using the default out-of-the-box aggregation.
2. turn on Query tracking for Week 1.
3. Prior to updating the cube the next weekend, run the Design Aggregation
Wizard to determine and save recommended aggregate views as a script.
4. Save the resulting script with a unique name.
5. update the cube and aggregate using the saved aggregation script from Week 1.
6. turn on Query tracking for Week 2.
7. repeat Steps 3 and 4.
8. update the cube and aggregate using the new saved aggregation from Week 2.
Continue repeating this process for 4 to 12 weeks; the goal being to track actual usage
for an entire quarter. In most corporations, weekly reports may differ from monthly
reports, which are different yet again from quarterly reporting. remember that one of
the reasons to use Query tracking is because no one could accurately define how the
data would be queried. Discuss expectations with users prior to the go-live kick off. The
users need to understand that the system may be slow to start (and it might be lightning
fast), with a high likelihood that it will grow faster and more efficient at retrieving data
with each additional week of usage and training.
If a strategy, such as the one described, above is employed, it is crucial that docu-
mentation is carefully kept and that statistics are appropriately gathered each week. The
following suggestions may help define a good starting point:
• Save each aggregation script (using dates or week numbers in the name may be
helpful).
• gather statistics on average query times for each completed week; have a target
SLA identified for comparison.
• track aggregate size growth; while the loaded data may not take up much space,
the aggregate views can start growing immensely in size.
• track the total aggregate processing time; there is probably an acceptable limi-
tation on the amount of time that can be spent processing aggregations based
on server resource capacity and server resource availability.
There comes a point in ASo cubes where adding additional aggregates may provide
diminishing returns. more is not always better. At the end of the 12 weeks of Query
tracking, if it can be determined that the best performance resulted after query track-
ing for week 8 was implemented, then use that script permanently in the weekly update
process. It also might be discovered that while the best performance results occurred
after week 12, the total processing time and space utilized exceeded acceptable thresh-
olds. In this instance, go back to a previous week until a script is identified that best
meets the performance SLA as well as resource capacity limits. remember, very little
actual work is occurring during this process. Allowing the system to track and deter-
mine suggestions is just utilizing a feature that is provided. Allowing this process to con-
tinue over an extended amount of time just provides better suggestions based on reality.
This can be shortened or eliminated altogether if gathering data is not within defined
time constraints.
