Database Reference
In-Depth Information
4.3.1
Need for Speed
Without exception, the most important ingredient of every successful Essbase applica-
tion is speed. If you scan otn, a substantial portion of threads are performance-related.
The typical scenario is a company that has an application that they like, but is just too
slow and they are looking for that magic setting that will resolve the performance issues.
Sadly, as in almost everything else in life, there are no quick fixes. Without a solid design,
your database is doomed. Author Dan neil said it best, “The problem with making a silk
purse out of a sow's ear is that your purse will still smell faintly of bacon.” The moral
here is to design the database correctly from the onset with performance in mind. here
is no hidden switch that will make a poorly designed database run faster.
4.4 what is “gooD” perFormanCe?
Performance with regards to databases can mean different things depending on the
application. For applications that are primarily used for reporting and analysis, calcula-
tion performance is probably not a big issue. on the other hand, for a budgeting appli-
cation, where end users enter data, execute calculations, and then wait for a response,
calculation performance is paramount. There might not be a single design that is best in
both situations. When designing an application, the first step is to decide what defines
acceptable performance for this particular application. Will it be based on calculations,
reporting speed, data load times, or something else? how many users will be simultane-
ously hitting the database?
4.4.1
Know the Basics for Good Performance
The Essbase Database Administrator's guide (DBAg) has at least 100 pages devoted
to application tuning. There are also numerous white papers and presentations on the
Internet covering the same subject. The fact is that many of the suggestions offered by
these documents are beneficial while others have little impact on overall performance.
The sheer number of factors makes it impossible to test every scenario. Even when we
have the will to perform comprehensive performance testing, we just do not have the
resources to test everything. to simplify matters, I am going to discuss the top three
things that impact performance the most: dimensionality design, system caches, and
configuration file settings and calculations.
4.4.2
Dimensionality
The process of defining the dimensionality of the database is beyond the scope of this
chapter, but the organization and content of the dimensions have a major impact on
performance. one of the best and also worst things about Essbase is that there is a mul-
titude of ways that a database can be designed. Some designs will work and there are
others that just will not work. Actually, they will work, but they will make your life
miserable. here are a few that come to mind:
• Databases having more than 10 real (nonattribute) dimensions.
• too many dynamic or dynamic calc and store sparse dimensions.
• Dense blocks without any dynamic calculations.
• two or more large flat sparse dimensions.
• Databases with many attribute dimensions based on large sparse dimensions.
• Databases having more than six or seven levels in a very large sparse dimension.
