Solved

how to decide dense & sparse dimension?

Posted on 2009-07-07
1
2,278 Views
Last Modified: 2012-06-22
how to easily identifed dense & sparse dimension
0
Comment
Question by:AJITPADHY
1 Comment
 
LVL 5

Accepted Solution

by:
garycris earned 500 total points
Comment Utility
The idea of Dense and Sparse dimensions is quite lengthy and unfortunately there is no easy answer to the question.  As a general rule, you would start off making your accounts and time dimensions dense.  This usually makes sense because it is likely that a large number of your time periods will have data across all your other dimensions, as will your accounts (measures).  From there you have to look at your block size, which is made up of the combinations of your dense dimensions.

So for example if you have 12 stored members in your periods dim and 200 stored measures in your accounts dim then your block size would approx 19K (12 x 200 x 8 bytes)

Optimal block size for 32 bit OS is between 8k and 100k with 64 bit OS, you can go larger, but you have to test and tune.

One good way to test which are your "densest" dimensions is to make a single dimension dense, load your data and run a default calc.  Then go into database properties and look under statistics and check block density.  Write it down, then clear the database, make a different dim dense with all others sparse and repeat load, calc, and check stats, again write it down and repeat for all dimensions.  This will give you an indication of which are the sparsest and which are the densest dimensions.

What you have to realize is that even after you are armed with that information, you still have to take a number of other factors into consideration.  Just because a dimension is the densest, doesn't always mean it will be a good dense dimension.  I know it is confusing, but tuning Essbase cubes is more art than science.  There are some basic rules to start out and then you have to test, test, and test.  It also makes a difference what your goals are.  I might make certain dims dense and others sparse to speed up calc time, but I might make other dims dense and sparse to get better retreival time.  So it depends on what the requirements are and it is usually a balancing act of what works best.

As I stated in the beginning, try to start off with Time and Accounts dense and go from there.

If you want to learn more about Essbase, I would suggest some readings and possibly a training class.

Check out the  database administrators guide http://download.oracle.com/docs/cd/E12825_01/epm.111/esb_dbag.pdf

I would also recommend a great book called "Look Smarter Than You Are with Essbase 11"
http://www.lulu.com/content/paperback-book/look-smarter-than-you-are-with-essbase-11/6402010
(full disclosure: while I am not the author of the book, I did contribute content to the publication)

Hope this helps.

0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now