Fast and reliable access to your information is one of the key success factors for any business intelligence or data warehousing application. Within the technological architecture of SAP BW the multidimensional model of InfoCubes has a tremendous impact on performance. A good dimension design optimizes performance in three ways:

• Data load times
• Query runtime
• Database size

Introducing the Problem: Degenerative Dimensions

One of the most critical guidelines from SAP about multidimensional modeling in BW is: “The dimension/master (SID) tables should be relatively small with respect to the number of rows in comparison to the fact table (factor 1:10/20).” When one dimension grows very large in relation to the fact table, the database optimizer finds it difficult to choose an efficient path to the data. This condition of a dimension having an exceptionally large record count relative to the fact table is known as a “degenerative dimension.”

Finding an optimal multidimensional model is a delicate balancing exercise between the size and number of dimension tables. When executing queries this will translate into the size and number of joins that need to be performed in order to get the results.

Figure 1: Optimal Multidimensional Model

Traditional Modeling Technique: Trial and Error

In order to come up with a good multidimensional model, traditional modeling papers say “know your data.” Although this is a very true statement, large data volumes make real-world experimentation impractical and time-consuming.

Figure 2: Traditional Data Modeling

The traditional repetitive modeling process contains the following steps as shown in Figure 2:

1. Analyze the data
2. Design the multidimensional model of an InfoCube
3. Build the InfoCube
4. Transport the InfoCube to an environment with a representative dataset (volume & diversity)
5. Load data into the InfoCube
6. Check the resulting multidimensional model (size of dimension and fact tables)
7. If the result is not optimal, return to step one

This trial-and-error-based process is very time- and resource-consuming and truly artisanal. Could there be a more scientific approach?

The Scientific Approach to Multidimensional Modeling

VRS Consulting has discovered a method to transform this art of multidimensional modeling into a science. We developed a SAP BSP application (portal based, fully integrated within BW) that eliminates 50% of the modeling (or remodeling of existing underperforming InfoCubes) processes and more than 50% of the modeling effort. The tool analyzes data in an existing data store object (DSO) or InfoCube and exposes all relationships between characteristics – good and bad – and how the characteristic relates to the facts. This scientific approach immediately exposes whether or not two characteristics should be in the same dimension and signals potential issues with dimensional degeneration.

The tool also simulates three steps of the traditional InfoCube modeling process. Within the tool one can define the multidimensional model of the InfoCube to be built and calculate the exact expected sizes of dimension and fact tables thus eliminating the need to actually build “test” InfoCubes and load data. Results are displayed in an easy to analyze format with tables and graphs.

Once satisfied with the results, there is even a button that automatically builds the InfoCube with the chosen multidimensional model.

Figure 3: Scientific Approach to Multidimensional Modeling

Results of this Scientific Approach

1. Reduction of more than 50% of the modeling effort Better data loading times (typically 20-50%)
2. Increased user satisfaction through improved query performance (typically 20-50%)
3. Smaller database size and less system load

SOURCE: Multidimensional Modeling in SAP BW – Art or Science?

Recent articles by Thierry Helderweirt

• Multidimensional Modeling in SAP BW, Part 2 - Science!