Developing Essbase Applications

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