Database Reference
In-Depth Information
7.8
A Final Word about rule r1, ASo Cache, and memory
272
7.8.1 Aggregation
272
7.8.2
ASo Cache
272
7.8.3 memory
273
7.9 Summary
273
7.10 Aterward and Caveat to the reader
274
7.1 introDuCtion
Like everyone else, when I first saw ASo (aggregate storage option) I was amazed
how fast it was; it could return results without the long “cooking” time required
by BSo (block storage option) cubes. All of that Dense/Sparse stuff was no longer
important, and I no longer had to worry when blocks were created or about database
size explosion, etc. Initially, I learned to write the simple mDx (multidimensional
expressions) needed to convert BSo member formulae for use in ASo and then I
gradually learned to write mDx and used it to add some of the features not available
when ASo first came out, such as time balance and nonpositive consolidation tags in
the Accounts dimension. While this added needed functionality, soon I noticed my
cube load time was slowing down and the queries were getting pokey.
With much reading and experimenting, I learned to design cubes that would load,
aggregate, and retrieve with all the speed of my first, simple cubes. This chapter lists a
number of design rules I have developed and now use to optimize performance, along
with the reasons behind those rules. It is my hope that you the reader will be able to
not only use these rules, but also gain an understanding of the reasons in order to
make informed decisions. The implications of not following the rules can be major,
but if you decide to relax them, then you will be able to understand and accept the
consequences.
Let me state clearly that the purpose of this chapter is to design a cube that per-
forms maximally,
minimizing the need for extensive aggregations by maximizing the
opportunities for powerful, highly leveraged aggregations that work over the widest
number of queries.
This will reduce the trap that I fell into in the beginning, an over-
dependence on aggregations. I will show that aggregations have a cost, not only in the
time to compute them, but in the all-important footprint your cube occupies in memory.
Aggregated views are an incredibly powerful tool, albeit, potentially costly; by optimiz-
ing your design, you will maximize their benefit.
7.1.1 Who Should Read This Chapter
This chapter is recommended for those who have built at least a few ASo cubes and
have already fought with the wizard as it converts your nice old BSo cube to an ASo
cube that really should have been faster. you probably have already built an alternate
hierarchy or two, and fought your way through at least a couple of conversions from
BSo member formulae to mDx (you certainly do not need to be anywhere near an
expert).
most important, your situation might be one of these:
• your cube is slow to query.
• your cube requires extensive aggregation to make it usable.
• you are getting ready to build a really big cube.
