?
Solved

how to decide dense & sparse dimension?

Posted on 2009-07-07
1
Medium Priority
?
2,617 Views
Last Modified: 2012-06-22
how to easily identifed dense & sparse dimension
0
Comment
Question by:AJITPADHY
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 5

Accepted Solution

by:
garycris earned 1500 total points
ID: 24793956
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
How to increase the row limit in Jasper Server.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

764 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