Developing Essbase Applications

113

4

Bso wIll never dIe and here Is why

Dave Farnsworth

4.1 WHO SHOULD READ THIS CHAPTER?

Writing a chapter about the block storage option (BSO) within Essbase comes with

asubstantial amount of risk. No matter how carefully the topic is researched, there will

CONTENTS

4.1 Who Should Read is Chapter? 113

4.2 Prerequisites 114

4.3 Why BSO, Indeed 114

4.3.1

Need for Speed 115

4.4 What Is “Good” Performance? 115

4.4.1

Know the Basics for Good Performance 115

4.4.2

Dimensionality 115

4.4.3

Block Size Mysteries 117

4.4.4 Dimension Order 118

4.5 Getting the Most from Multiple Processors 121

4.6 Cache Is King; Know the Basics 121

4.6.1

Index Cache 122

4.6.2

Data Cache 123

4.6.3

Data File Cache 124

4.6.4

Calculator Cache 125

4.6.5

Dynamic Calc Cache 125

4.7 Check Your Work 127

4.7.1

Block Count as a Speedometer 128

4.7.2

Whoa! My Script Is Slow 130

4.7.3

Script Variables: Are ey Useful? 132

4.7.4

Reduce Processing and Simplify Code 133

4.7.5

Aggregate Only What Is Needed 135

4.7.6

Create Blocks Explained 137

4.7.7

What Do ey Mean, Why Do I Care? 141

4.8 BSO Matters and en Some 142

114  •  Developing Essbase Applications: Advanced Techniques for Finance and IT Professionals

always be exceptions to database behavior. Use the ideas presented here as guidelines

and not absolutes. is chapter is aimed squarely at the BSO developer or administra-

tor who knows the basics, but does not have a solid understanding of the underlying

technology; this is not a chapter for the novice BSO developer or administrator. BSO

is almost 20 years old and there are already numerous books, whitepapers, and online

resources that are terric when it comes to the basics. e chapter will appeal to:

•  ose who want to understand the elements that impact database performance.

•  Anyone who is confused with the plethora of BSO cache settings.

•  ose who want to take advantage of the log messages while tuning calculations

scripts.

•  Anyone needing to eciently create data blocks in a calculation script.

•  Administrators or developers seeking the “choke point” of a calculation script.

is chapter is full of ideas and examples that will encourage you to create ecient and

robust Essbase databases and inspire you to improve your existing calculation scripts.

4.2 PREREQUISITES

It is absolutely critical that the reader have at least moderate administration experi-

ence with Essbase and, in particular, with BSO. In addition, it will be helpful to be very

familiar with Essbase Administration Services (EAS) and how to view application logs

and database statistics.

4.3 WHY BSO, INDEED

With apologies to Mark Twain, reports of the death of Essbase block storage are greatly

exaggerated. We have heard all of the reasoning for the demise of block storage: BSO

has limitations on the number of practical dimensions, sparse/dense conguration is

confusing, dense block has size limitations, and, nally, the calculations are too slow.

Oh, and let us not forget data explosion. Okay, I understand the issues, but tell me this:

Name another tool that oers in-place write back along with scores of functions for

manipulating data. ese are the features that separate Essbase block storage from every

other tool and reasons why BSO will be around for some time.

What about the performance-related concerns, you ask? Admittedly, there can be

issues. One only needs to look on the Oracle Technology Network (OTN) messageboards

to view performance diculties that others have experienced. Like any technology, there

is a time and place when BSO is the correct choice. No good discussion of BSO can begin

without identifying where BSO is not a good t. Before the advent of aggregate storage

options (ASO), block storage Essbase was used for every type of application imagin-

able. Today, BSO is best used for read/write applications and databases that use complex

allocations and calculations. I am not saying that BSO cannot be used for large report-

ing applications, it certainly can. I am asserting that because of scaling constraints that

is not its best application. Regardless of use, I am going to show you how to use the

power of Essbase block storage to build databases that perform to the best of their ability.

Certainly you have all heard that Essbase design is more art than science. Truer words

have never been spoken. What works with one database might not work as well with

another. I will help you understand the variables that aect performance. Aer that, it is

your responsibility to try various options and then test, test, and test some more.