Home | Topics | SAP

Multidimensional Modeling in SAP BW – Art or Science?

Printer-friendly versionPDF version

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


Thierry Helderweirt
Thierry Helderweirt is an experienced SAP BW consultant and project manager with 12 years of experience in data warehousing and business intelligence. Thierry started his career as a data warehouse and business intelligence consultant at leading consulting organizations such as Origin International and Deloitte Consulting. He has worked with different technologies such as SAS and Seagate Info (Crystal Reports)and has been focusing exclusively on SAP BW since 2000. In 2001, Thierry founded VRS Consulting, a niche consulting company completely dedicated to SAP BI solutions. One of his main focus areas is performance tuning within SAP BW environments.

Source: http://www.beyenetwork.be/view/9968

Short URL
Asymptotix on Twitter

Are the key legislative pillars such as Basel II & III, UCITS IV and Solvency II forcing you to re-examine how you identify, measure and manage risk and capital?

Asymptotix work closely with our partners to help clients develop a more proactive, systematic and integrated approach to governance and risk management to deliver proper value.

Asymptotix can offer the support you need to deliver on time. Read more...

Is the goal of your website to sell services or products, educate, or collect data?

A positive customer experience is vital to conversion, no matter what your conversion goals may be. Our designers and developers will create a positive experience to maximize your conversions and deliver the optimal return on your investment. We strive to find the perfect balance between the web site’s design and functionality.

Asymptotix implements interactive solutions for European companies. From corporate websites to social communities, our clients will tell you an investment in building a scalable online experience will deliver long-term tangible benefits.

Based in Luxembourg we can help you all over Europe. Our multi-lingual team can work with projects and speak your language! Read more...