Solved

how to decide dense & sparse dimension?

Posted on 2009-07-07
1
2,526 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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

696 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