Solved

how to decide dense & sparse dimension?

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

803 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