Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# how to decide dense & sparse dimension?

Posted on 2009-07-07
Medium Priority
2,670 Views
how to easily identifed dense & sparse dimension
0
[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
1 Comment

LVL 5

Accepted Solution

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.

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

Question has a verified solution.

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

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
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 tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
###### Suggested Courses
Course of the Month9 days, 21 hours left to enroll