Types Of Dimensions in Hyperion Essbase

Types Of Dimensions in Hyperion Essbase

Standard Dimensions and Attribute Dimensions

Essbase has two types of dimensions: standard dimensions and attribute dimensions. This chapter primarily considers standard dimensions because Essbase does not allocate storage for attribute dimension members. Instead it dynamically calculates the members when the user requests data associated with them.

An attribute dimension is a special type of dimension that is associated with a standard dimension.

Sparse and Dense Dimensions

Most data sets of multidimensional databases have two characteristics:

● 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.

Essbase maximizes performance by dividing the standard dimensions of an application into two types:

1) Dense dimensions and

2) Sparse dimensions.

This division allows Essbase to cope with data that is not smoothly distributed, without losing the advantages of matrix-style access to the data.

Essbase speeds up data retrieval while minimizing the memory and disk requirements.

Most multidimensional databases are inherently sparse: they lack data values for the majority of member combinations. A sparse dimension is a dimension with a low percentage of available data positions filled.

For example, the Sample Basic database shown in Figure A includes the Year, Product, Market, Measures, and Scenario dimensions. Product represents the product units, Market represents the geographical regions in which the products are sold, and Measures represents the accounts data. Because not every product is sold in every market, Market and Product are chosen as sparse dimensions.

Most multidimensional databases also contain dense dimensions. A dense dimension is a dimension with a high probability that one or more cells is occupied in every combination of dimensions. For example, in the Sample Basic database, accounts data exists for almost all products in all markets, so Measures is chosen as a dense dimension. Year and Scenario are also chosen as dense dimensions. Year represents time in months, and Scenario represents whether the accounts values are budget or actual values.

Note:

In Figure A, Caffeinated, Intro Date, Ounces, Pkg Type and Population are attribute dimensions.

Fig A:

Selection of Dense and Sparse Dimensions

In most data sets, existing data tends to follow predictable patterns of density and sparsity. If you match patterns correctly, you can store the existing data in a reasonable number of fairly dense data blocks, rather than in many highly sparse data blocks.

By default, a new dimension is set sparse. To help you determine whether dimensions should be dense or sparse, Essbase provides an automatic configuration feature.

Essbase can make recommendations for the sparse-dense configuration of dimensions based on the following factors:

● The time and accounts tags on dimensions

● The probable size of the data blocks

● Characteristics that you attribute to the dimensions

You can apply a recommended configuration or you can turn off automatic configuration and manually set the sparse or dense property for each dimension. Attribute dimensions are always sparse dimensions. Keep in mind that you can associate attribute dimensions only with sparse standard dimensions.

Note:

The automatic configuration of dense and sparse dimensions provides only an estimate. It cannot take into account the nature of the data you will load into your database or multiple user considerations.

Dense-Sparse Configuration for Sample Basic

Consider the Sample Basic database that is provided with Essbase. The Sample Basic database represents data for The Beverage Company (TBC).

TBC does not sell every product in every market; therefore, the data set is reasonably sparse.

Data values do not exist for many combinations of members in the Product and Market dimensions. For example, if Caffeine Free Cola is not sold in Florida, then data values do not exist for the combination Caffeine Free Cola (100-30)->Florida.So, Product and Market are sparse dimensions. Therefore, if no data values exist for a specific combination of members in these dimensions, Essbase does not create a data block for the combination.

However, consider combinations of members in the Year, Measures, and Scenario dimensions.

Data values almost always exist for some member combinations on these dimensions. For example, data values exist for the member combination Sales->January->Actual because at least some products are sold in January. Thus, Year and, similarly, Measures and Scenario are dense dimensions.

The sparse-dense configuration of the standard dimensions in the Sample Basic database may be summarized as follows:

● The sparse standard dimensions are Product and Market.

● The dense standard dimensions are Year, Measures, and Scenario.

Essbase creates a data block for each unique combination of members in the Product and Market dimensions (for more information on data blocks, each data block represents data from the dense dimensions. The data blocks are likely to have few empty cells.

For example, consider the sparse member combination Caffeine Free Cola (100-30), New York, illustrated by Figure B:

● If accounts data (represented by the Measures dimension) exists for this combination for January, it probably exists for February and for all members in the Year dimension.

● If a data value exists for one member on the Measures dimension, it is likely that other accounts data values exist for other members in the Measures dimension.

● If Actual accounts data values exist, it is likely that Budget accounts data values exist.

Fig B:

Dense and Sparse Selection Scenarios

The following scenarios show how a database is affected when you select different standard dimensions. Assume that these scenarios are based on typical databases with at least seven dimensions and several hundred members:

Scenario 1: All Sparse Standard Dimensions

If you make all dimensions sparse, Essbase creates data blocks that consist of single data cells that contain single data values. An index entry is created for each data block and, therefore, in this scenario, for each existing data value.

This configuration produces an index that requires a large amount of memory. The more index entries, the longer Essbase searches to find a specific block.

Fig C:

Scenario 2: All Dense Standard Dimensions

If you make all dimensions dense, as shown in Figure D, Essbase creates one index entry and one very large, very sparse block. In most applications, this configuration requires thousands of times more storage than other configurations. Essbase needs to load the entire memory when it searches for a data value, which requires enormous amounts of memory.

Fig D:

Scenario 3: Dense and Sparse Standard Dimensions

Based upon your knowledge of your company’s data, you have identified all your sparse and dense standard dimensions. Ideally, you have approximately equal numbers of sparse and dense standard dimensions. If not, you are probably working with a non-typical data set and you need to do more tuning to define the dimensions.

Essbase creates dense blocks that can fit into memory easily and creates a relatively small index as shown in Figure E. Your database runs efficiently using minimal resources.

Fig E:

Scenario 4: A Typical Multidimensional Problem

Consider a database with four standard dimensions: Time, Accounts, Region, and Product. In the following example, Time and Accounts are dense dimensions, and Region and Product are sparse dimensions.

The two-dimensional data blocks shown in Figure F represent data values from the dense dimensions: Time and Accounts. The members in the Time dimension are J, F, M, and Q1. The members in the Accounts dimension are Rev, Exp, and Net.

Fig F:

Essbase creates data blocks for combinations of members in the sparse standard dimensions (providing at least one data value exists for the member combination). The sparse dimensions are Region and Product. The members of the Region dimension are East, West, South, and Total US. The members in the Product dimension are Product A, Product B, Product C, and Total Product.

Figure G shows 11 data blocks. No data values exist for Product A in the West and South, for Product B in the East and West, and for Product C in the East. Therefore, Essbase has not created data blocks for these member combinations. The data blocks that Essbase has created have very few empty cells.

Fig G:

This example effectively concentrates all sparseness into the index and concentrates all data into fully utilized blocks. This configuration provides efficient data storage and retrieval.

Next consider a reversal of the dense and sparse dimension selections. In the following example, Region and Product are dense dimensions, and Time and Accounts are sparse dimensions. As shown in Figure H, the two-dimensional data blocks represent data values from the dense dimensions: Region and Product.

Fig H:

Essbase creates data blocks for combinations of members in the sparse standard dimensions (providing at least one data value exists for the member combination). The sparse standard dimensions are Time and Accounts.

Figure I show 12 data blocks. Data values exist for all combinations of members in the Time and Accounts dimensions; therefore, Essbase creates data blocks for all the member combinations. Because data values do not exist for all products in all regions, the data blocks have many empty cells. Data blocks with many empty cells store data inefficiently.

Fig I: