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.

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.