|

Understanding DP and BW InfoCube Dimensions and Fact Table

ByShaun Snapp

Executive Summary

  • SAP DP and BW use the star schema.
  • We cover dimensions and how they use InfoCubes in SAP DP and BW.

DIm

Introduction to the Star Schema

The star schema is critical to most analytics applications. You will learn how the star schema works and how it is set up in SAP DP and BW.

Our References for This Article

If you want to see our references for this article and other related Brightwork articles, see this link.

Notice of Lack of Financial Bias: We have no financial ties to SAP or any other entity mentioned in this article.

  • This is published by a research entity, not some lowbrow entity that is part of the SAP ecosystem. 
  • Second, no one paid for this article to be written, and it is not pretending to inform you while being rigged to sell you software or consulting services. Unlike nearly every other article you will find from Google on this topic, it has had no input from any company's marketing or sales department. As you are reading this article, consider how rare this is. The vast majority of information on the Internet on SAP is provided by SAP, which is filled with false claims and sleazy consulting companies and SAP consultants who will tell any lie for personal benefit. Furthermore, SAP pays off all IT analysts -- who have the same concern for accuracy as SAP. Not one of these entities will disclose their pro-SAP financial bias to their readers. 

What is a Star Schema?

The star schema is a relational structure that emulates a multidimensional data cube. That is, it provides high-speed access. A star schema is made up of one fact table (where the actual values are stored) surrounded by multiple dimension tables.
For details on characteristics, see this article.

What Are Dimensions?

Dimensions are what are being analyzed and are called characteristics. A few examples  of dimensions include:

  • Time
  • Customer
  • Sales Org
  • Material

Up to 16 dimensions or dimension tables (each dimension has its table) can be used per cube, but a minimum of four must be used. However, there are multiple characteristics per dimension; in fact, up to 248 characteristics per dimension. Dimension tables are reused to reduce redundancy, and all master data is stored outside of the star schema.

Dimensions Makeup and InfoCube

There are multiple characteristics to a single dimension. There is a max of 16 dimensions per InfoCubes. The InfoCube is based on the star schema, which combines facts and dimensions. A BI star schema in an enhancement of the classic star schema.
To read about how to set up, InfoCubes see this link…

Building up an InfoCube is a lot of work. We go through the process in this link.

Extended Star Schema

The enhancement comes from The fact the dimension tables do not contain master data information. The master data information is stored in separate tables, called master data tables. The master data tables “extend” the star schema, resulting in an extended star schema.

SID Key

A numerical SID key is generated for each characteristic. This “alias” key replaces the characteristic as the component of the dimension table. They logically and physically link database tables. They also manage load and aggregation.

Here SID stands for Master Data ID or Surrogate ID.

Advantages and Disadvantages of Star Schema

Advantage

  1. Data access performs well due to the small number of joining operations.

Disadvantages

  1. Redundant entries exist
  2. In contrast to the historicization (how time is modeled) of the fact data, the historicization of dimensions is not easy to model.
  3. Modeling of hierarchy types in dimension can lead to anomalies.
  4. Query performance is lessened.

Benefits of BI Extended Star Schema

  1. Easy modeling of slow moving dimensions
  2. Multilingual capability
  3. Cross cube use of master data
  4. Ability to handle null values

Fact Data

Fact data is stored in a highly normalized fact table. Dimensions are stored in a dimension table. Any number of semantically related dimension attributes are stored in a hierarchy.

  • You can make the attributes navigational, and they act as characteristics in a query.
  • A SIG or surrogate table allows you to see the master data tables as a hierarchy.
  • You can view the fact table of an InfoCube by performing the following actions:

Right mouseclick the InfoCube and select “Manage.”

This brings you into the management area of the InfoCube. Each tab controls a different part of the InfoCube. This tab (contents) shows the data that is loaded into the cube:


From here, you can select the Fact Table button:

This brings you to the selection screen. When you select the green checkbox, you are taken to the fact table.

You can also go back and select the Contents tab:


This brings up the screen below:

Next, we will select “Feild Selection” and choose which fields we want to search for. After making the selection, the contents are displayed. This output is related to one of the dimension tables.

InfoPackages and Data Transfer Process

The data flow design uses metadata objects such as DataSources, Transformations, InfoSources, and InfoProviders. Once the data flow is designed, the InfoPackages and Data Transfer Processes take over to manage the actual data transfer execution and scheduling.

InfoCubes are one final destination for DP data. To see other on-InfoCube destinations, see this post.

Design Points

Speaking more broadly, the Book SAP BW Certification has a good list of Data Warehousing Design Decision Points, which are listed below:

  1. Identify the fact table.
  2. Identify the dimension tables.
  3. Identify the attributes with complete descriptions.
  4. Chose the grain of each table
  5. Identify the facts, including the pre-calculated facts.
  6. Determine how to track slowly changing dimensions
  7. Define the aggregations, query modes, and physical storage decisions
  8. Decide the historical duration of the database (archiving)
  9. Decide on the urgency with which the data are extracted and loaded into the data warehouse.