|
|
|
|
|
|
In this chapter, you will learn how Hyperion Essbase OLAP Server improves performance by reducing storage space and speeding up data retrieval for multidimensional databases. This chapter contains the following sections:
Attribute Dimensions and Standard DimensionsHyperion Essbase has two types of dimensions: attribute dimensions and non-attribute dimensions (standard dimensions). An attribute dimension is a special type of dimension that is associated with a standard dimension. For more information about attribute dimensions, see Working with Attributes This chapter primarily considers standard dimensions because Hyperion Essbase does not allocate storage for attribute dimension members. Instead it dynamically calculates the members when the user requests data associated with them.
Sparse and Dense DimensionsMost data sets of multidimensional applications have two characteristics:
Hyperion Essbase maximizes performance by dividing an application's standard dimensions into two types: dense dimensions and sparse dimensions. This division allows Hyperion Essbase to cope with data that is not smoothly distributed, without losing the advantages of matrix-style access to the data. Hyperion 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, as shown in Figure 4-1, includes the Product, Market, Measures, Year, 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 data points 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: | Caffeinated, Intro Date, Ounces, and Pkg Type are attribute dimensions that are associated with the Product dimension. Population is an attribute dimension that is associated with the Market dimension. Members of attribute dimensions describe characteristics of the members of the dimensions with which they are associated. For example, each product has a size in ounces. Attribute dimensions are always sparse dimensions and must be associated with a sparse standard dimension. Hyperion Essbase does not store the data for attribute dimensions, Hyperion Essbase dynamically calculates the data when a user retrieves it. For more information about attribute dimensions, see Working with Attributes |
Figure 4-1: Sample Basic Database Outline
Data Blocks and the Index SystemHyperion Essbase uses two types of internal structures to store and access data: data blocks and the index system.
Hyperion Essbase creates a data block for each unique combination of sparse standard dimension members (providing at least one data value exists for the sparse dimension member combination). The data block represents all the dense dimension members for its combination of sparse dimension members.
Hyperion Essbase creates an index entry for each data block. The index represents the combinations of sparse standard dimension members. It contains an entry for each unique combination of sparse standard dimension members for which at least one data value exists.
For example, in the Sample Basic database Product and Market are sparse dimensions.
Figure 4-2: Product and Market Dimensions from the Sample Basic Database
If data exists for Caffeine Free Cola in New York, then Hyperion Essbase creates a data block and an index entry for the sparse member combination of Caffeine Free Cola (100-30)->New York. If Caffeine Free Cola is not sold in Florida, then Hyperion Essbase does not create a data block or an index entry for the sparse member combination of Caffeine Free Cola (100-30)->Florida.
The data block Caffeine Free Cola (100-30)->New York represents all the Year, Measures, and Scenario dimensions for Caffeine Free Cola (100-30)->New York.
Figure 4-3: Simplified Index and Data Blocks
Each unique data value can be considered to exist in a cell in a data block. When Hyperion Essbase searches for a data value, it uses the index to locate the appropriate data block. Then, within the data block, it locates the cell containing the data value. The index entry provides a pointer to the data block. The index handles sparse data efficiently because it includes only pointers to existing data blocks.
Figure 4-4 shows part of a data block for the Sample Basic database. Each dimension of the block represents a dense dimension in the Sample Basic database: Time, Measures, and Scenario. A data block exists for each unique combination of members of the Product and Market sparse dimensions (providing that at least one data value exists for the combination).
Figure 4-4: Part of a Data Block for the Sample Basic Database
Each data block is a multidimensional array that contains a fixed, ordered location for each possible combination of dense dimension members. Accessing a cell in the block does not involve sequential or index searches. The search is almost instantaneous, resulting in optimal retrieval and calculation speed.
Hyperion Essbase orders the cells in a data block according to the order of the members in the dense dimensions of the database outline.
Figure 4-5 references the following database outline:
A (Dense)
a1
a2
B (Dense)
b1
b11
b12
b2
b21
b22
C (Dense)
c1
c2
c3
D (Sparse)
d1
d2
d21
d22
E (Sparse)
e1
e2
e3
The block in Figure 4-5 represents the three dense dimensions within the combination of the sparse members d22 and e3. In Hyperion Essbase, member combinations are denoted by the cross-dimensional operator. The symbol for the cross-dimensional operator is ->. So d22, e3 is written d22->e3. A, b21, c3 is written A->b21->c3.
Figure 4-5: Data Block Representing Dense Dimensions for d22->e3
Hyperion Essbase creates a data block for every unique combination of the members of the sparse dimensions D and E (providing that at least one data value exists for the combination).
Data blocks, such as the one shown in Figure 4-5, may include cells that do not contain data values. A data block is created if at least one data value exists in the block. Hyperion Essbase compresses data blocks with missing values on disk, expanding each block fully as it brings the block into memory. Data compression is optional, but enabled by default. For more information, see Specifying Data Compression.
By carefully selecting dense and sparse standard dimensions, you can ensure that data blocks do not contain many empty cells. In Hyperion Essbase, empty cells are known as missing or #MISSING data. You can also minimize disk storage requirements and maximize performance.
Selection of Sparse and Dense DimensionsIn 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.
When you create a database outline in Outline Editor, Hyperion Essbase automatically suggests which of the dimensions should be sparse and which dense. Hyperion Essbase, for example, consider the Time and Accounts tags on dimensions and the probable size of the data blocks. For more information on time and accounts tags, see Setting Dimension and Member Properties For more information on using the Outline Editor to create database outlines, see Creating and Changing Database Outlines
You can turn off auto-configuration and manually choose your sparse and dense dimensions. To help you select sparse and dense dimensions, Hyperion Essbase provides data storage information in the Application Manager Data Storage dialog box. To open this dialog box, open the database outline and choose Settings > Data Storage. The information that is provided includes data block size and density. This information helps you choose the optimal configuration for your database. For more information, see Specifying Hyperion Essbase Kernel Settings
Figure 4-6: Data Storage Dialog Box
Consider the Sample Basic database that is shipped with Hyperion 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 on 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 on these dimensions, Hyperion Essbase does not create a data block for the combination.
However, consider combinations of members on 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:
Hyperion Essbase creates a data block for each unique combination of members in the Product and Market dimensions. 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:
Dense and Sparse Selection ScenariosThe following scenarios show how a database is affected when you select different dense and sparse standard dimensions. Assume that these scenarios are based on typical databases with at least seven dimensions and several hundred members.
Scenario 1: A Database Consisting Entirely of Sparse Standard DimensionsIf you make all dimensions sparse, Hyperion Essbase creates data blocks that consist of single data cells that contain single data values. There is an index entry for each data block and, therefore, in this scenario, for each existing data value.
This configuration produces a huge index that requires a large amount of memory. The more index entries, the longer Hyperion Essbase searches to find a block.
Figure 4-8: Database with All Sparse Standard Dimensions
Scenario 2: A Database Consisting Entirely of Dense Standard DimensionsIf you make all dimensions dense, Hyperion 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. Hyperion Essbase needs to load the entire block into memory when it searches for a data value, and thus enormous amounts of memory are required.
Figure 4-9: Database with All Dense Standard Dimensions
Scenario 3: An Ideal Configuration of Dense and Sparse Standard DimensionsBased 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.
Hyperion Essbase creates dense blocks that can fit into memory easily and creates a relatively small index. Your database runs efficiently using minimal resources.
Figure 4-10: An Ideal Configuration
Scenario 4: A Typical Multidimensional ProblemConsider 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 represent data values from the dense dimensions: Time and Accounts. The members on the Time dimension are J, F, M and Q1. The members on the Accounts dimension are Rev, Exp, and Net.
Figure 4-11: Two-dimensional Data Block for Time and Accounts
Hyperion Essbase creates data blocks for combinations of members on 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 on the Product dimension are Product A, Product B, Product C, and Total Product.
Figure 4-12 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, Hyperion Essbase has not created data blocks for these member combinations. The data blocks that Hyperion Essbase has created have very few empty cells.
Figure 4-12: Data Blocks Created for Sparse Members on Region and Product
This example effectively concentrates all the sparseness into the index and concentrates all the data into fully utilized blocks. This configuration provides efficient data storage and retrieval.
Now 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.
The two-dimensional data blocks represent data values from the dense dimensions: Region and Product.
Figure 4-13: Two-Dimensional Data Block for Region and Product
Hyperion Essbase creates data blocks for combinations of members on the sparse standard dimensions (providing at least one data value exists for the member combination). The sparse standard dimensions are Time and Accounts.
Figure 4-14 shows 12 data blocks. Data values exist for all combinations of members on the Time and Accounts dimensions; therefore, Hyperion 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.
Figure 4-14: Data Blocks Created for Sparse Members on Time and Accounts
The Hyperion Essbase SolutionWhen you create an optimized Hyperion Essbase database, you need to consider carefully the following questions:
|
|
|
|
|
 |
Copyright © 1991-2000 Hyperion Solutions Corporation. All rights reserved.
