Most data sets of multidimensional databases have two characteristics:
1. Dense Dimensions
2. Sparse Dimensions
Sparse Dimensions :
Data is normally stored in sparse form. If no value exists for a given combination of dimension values, no row exists in the fact table. For example, if not every product is sold in every market. In this case, Market and Product are sparse dimensions.
It's why in the reporting tool Obiee for instance, by default, data are considered sparse.
Dense Dimensions:
Most multidimensional databases may also contain dense dimensions. A fact table is considered to have dense data if it has (of a high probability to have) one row for every combination of its associated dimension levels.
If you are a relational database developer or a spreadsheet user, you tend to view data in 2 dimensions. An X and Y axis is equivalent to the rows and columns in your spreadsheet or database table. Essbase is a little different in that it stores data in 3 dimensions, like a Rubik’s Cube, so it has a Z axis. Essbase databases refer to these “Rubik’s Cubes” as blocks. An Essbase database isn’t one giant Rubik’s Cube; it could be millions of them. The size and number of possible blocks a database has is determined by the sparse/dense configuration of the database.
An Essbase outline has a number of dimensions. The number of dimensions can range in quantity and size, but each dimension is identified as a dense or sparse dimension. The dense dimensions define how large each block will be in size (the number of rows, columns and the depth of the Z axis). The sparse dimensions define the number of possible blocks the database may hold. Assume the following scenario: a database exists with 3 dense dimensions and 2 sparse dimensions. The dense dimensions are as follows:
Net Income
Income
Expenses
Qtr 1
Jan
Feb
Mar
Version
~ Actual
~ Budget
~ Forecast
Remember, the dense dimensions define the size of blocks. These dimensions would produce a block that looks like the image below. Every block in the database would be the same.
For those more knowledgeable with Essbase design, this example assumes that no member is dynamically calculated or is tagged as a label to reduce complexity.
The sparse dimensions are below.
Total Product
Shirts
Pants
Total Region
North
South
East
West
The unique combinations of each sparse dimension has its own block. There will be a block for Pants – North, one for Shirts – North, and so on. Since there are 3 members in the Total Products dimension and 5 members in the Total Region dimension, there will be a total of 15 (3 x 5) blocks. If a database has 5 sparse dimensions, all with 10 members, it would have a total possible number of blocks equal to 100,000 (10 x 10 x 10 x 10 x 10). Below is a representation of the possible blocks for Shirts.
Reference Blog : http://www.in2hyperion.com/2009/08/05/sparse-dense-and-blocks-for-dummies-2/
- Data is not smoothly and uniformly distributed.
- Data does not exist for the majority of member combinations. For example, all products may not be sold in all areas of the country.
1. Dense Dimensions
2. Sparse Dimensions
Sparse Dimensions :
Data is normally stored in sparse form. If no value exists for a given combination of dimension values, no row exists in the fact table. For example, if not every product is sold in every market. In this case, Market and Product are sparse dimensions.
It's why in the reporting tool Obiee for instance, by default, data are considered sparse.
Dense Dimensions:
Most multidimensional databases may also contain dense dimensions. A fact table is considered to have dense data if it has (of a high probability to have) one row for every combination of its associated dimension levels.
If you are a relational database developer or a spreadsheet user, you tend to view data in 2 dimensions. An X and Y axis is equivalent to the rows and columns in your spreadsheet or database table. Essbase is a little different in that it stores data in 3 dimensions, like a Rubik’s Cube, so it has a Z axis. Essbase databases refer to these “Rubik’s Cubes” as blocks. An Essbase database isn’t one giant Rubik’s Cube; it could be millions of them. The size and number of possible blocks a database has is determined by the sparse/dense configuration of the database.
An Essbase outline has a number of dimensions. The number of dimensions can range in quantity and size, but each dimension is identified as a dense or sparse dimension. The dense dimensions define how large each block will be in size (the number of rows, columns and the depth of the Z axis). The sparse dimensions define the number of possible blocks the database may hold. Assume the following scenario: a database exists with 3 dense dimensions and 2 sparse dimensions. The dense dimensions are as follows:
Net Income
Income
Expenses
Qtr 1
Jan
Feb
Mar
Version
~ Actual
~ Budget
~ Forecast
Remember, the dense dimensions define the size of blocks. These dimensions would produce a block that looks like the image below. Every block in the database would be the same.
For those more knowledgeable with Essbase design, this example assumes that no member is dynamically calculated or is tagged as a label to reduce complexity.
The sparse dimensions are below.
Total Product
Shirts
Pants
Total Region
North
South
East
West
The unique combinations of each sparse dimension has its own block. There will be a block for Pants – North, one for Shirts – North, and so on. Since there are 3 members in the Total Products dimension and 5 members in the Total Region dimension, there will be a total of 15 (3 x 5) blocks. If a database has 5 sparse dimensions, all with 10 members, it would have a total possible number of blocks equal to 100,000 (10 x 10 x 10 x 10 x 10). Below is a representation of the possible blocks for Shirts.
Reference Blog : http://www.in2hyperion.com/2009/08/05/sparse-dense-and-blocks-for-dummies-2/
