225
7
how aso works
and
how to desIgn For PerFormanCe
Dan Pressman
CONTENTS
7.1 Introduction 226
7.1.1 Who Should Read is Chapter 226
7.1.2 What Will and Will Not Be Covered 227
7.2 Summary: Rules of ASO Designing for Performance 227
7.3 ASO Queries: How ey Work 228
7.3.1 What Does a Cube Look Like? 228
7.3.2 e Essbase ASO Implementation of the Sorting-Needle, the Holes,
and the Notches 230
7.3.3 e Essbase ASO Implementation of the Card Box: e Importance
of Memory Space 232
7.3.4 e Essbase Implementation of the Data Card 233
7.3.5 Is It Really at Simple? 236
7.4 e Bitmap: Its Role and Function 237
7.4.1 Why Should Anyone Care about the Bitmap? 237
7.4.2 How Is the Bitmap Constructed? 237
7.4.3 Primary Lessons from the Bitmap 240
7.5 Understanding Aggregations and Slices 253
7.5.1 Aggregation and the Bitmap 254
7.5.2 What Is the Meaning of ose Numbers in the Recommended View? 255
7.5.3 Aggregate View: What Is “Query Cost 258
7.5.4 So What Is Aggregated and How Is It Used? 259
7.5.5 Design Aggregation CSC File 260
7.6 Design Alternatives and eir Costs 262
7.6.1 Other Types of Cost 262
7.6.2 Alternative Design Options: Designing to Maximize Use of Stored
Hierarchies 263
7.7 Tips to Improve Data Loading and Aggregation Performance 271
226 •  Developing Essbase Applications: Advanced Techniques for Finance and IT Professionals
7.1 INTRODUCTION
Like everyone else, when I rst 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 stu 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 rst 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 rst, simple cubes. is 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. e 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.is 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 benet.
7.1.1 Who Should Read is Chapter
is 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.
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 Aerward and Caveat to the Reader 274

Get Developing Essbase Applications now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.