SAP Business warehousing Tracking History

In the following business scenario we are in need of SAP business warehousing is needed.

As a member of the BW project team you are interviewing users to find out what they require from sales reports according to material group.One particular question that has been asked is what happens to the reports if a material changes material group, for example, from Food to Chemical, in the middle of a period?The users have many different opinions on this issue.

Often different users have different ideas on how to track historical data. In this section, we are going to look more closely at the four scenarios shown below.

Historical Data: The Role of the Fact Table

Changes that occur over time are usually taken into account when transaction data is loaded into the fact table.Each data record in the fact table is identified by a unique combination of generic dimension keys representing a unique combination of characteristic values that are taken directly from or are made up from the transaction data.

Example: Customer 'X' purchases material 'A' on day 'Y'. This new relationship between 'A', 'X', and 'Y' becomes a new record in the fact table.The fact table usually shows only events that have actually taken place and not events that have not happened!

If we have a combination of characteristics that is identical to a combination of characteristics currently in the fact table, the key figures for the incoming data are aggregated with the facts that are already in the table.* Ÿ *usually only the package dimension is different, because it identifies different loads of data.The fact table is not able to record the sentence; “We have NOT received a sales order for 100 cartons from company XYZ Inc.”.

Changes to attributes in different dimensions (a sales transaction, for example) as shown in the previous slide make up the day-to-day business of a data warehouse.How do we deal with changes to attributes in the same dimension?

Example: Marital status

What products do married people buy?
Which products did married people buy last year?

You must consider system performance and the option of creating aggregates for time-independent attributes. You must also consider how and if to reconstruct the InfoCube in the event that the model with a characteristic as a dimension is chosen and the master data changes again.

Modeling Methods: A Comparison

Other considerations:

ŸThe value of historical data decreases over time.
ŸIt is possible to combine Scenario A with other scenarios.

Category Dimensions

Support reports by generating artificial attributes that classify a characteristic.Customer is classified by income group, size, and so on.

Category dimensions are usually connected to attributes rather than to characteristics.
Income bracket - customer income n Size of customer - annual sales, potential sales, debts. and so on Whether you create category dimensions or hand over the categorization process to a query depends on:how complex the categorization process is

how frequently categorization is used in queries
The decision to create a category dimension comes from the MDM

If you want to use a query for categorization purposes, you need to include all the customers, whose income is greater than X but less than Y. Category dimensions allow you to use the Income characteristic with the values A < 1000, 1001 < B < 5000, and 5001 < C < 6000 during the data transfer. The user simply filters out all the “B” customers.

If the categorization changes, you need to ask the same questions as discussed in the section on ‘slowly changing' dimensions.From an analytical point of view, the attributes in the category dimension have to be stored in the master table of the categorized characteristic.In BW category dimensions are usually part of the dimension of the categorized characteristic.Use aggregates with category attributes.

Experts talk of a 10-20 to one relationship between the size of the fact table and the size of the dimension tables. Although the optimal relationship is difficult to determine, a large fact table and smaller dimension tables provide the best system performance.In some cases, you may want to find out more detailed information on line-item dimensions.

Degenerate Dimensions

Degenerate dimensions occur in nearly every case where the granularity of the fact table corresponds to a single document, for example an order number or invoice number.In a degenerate dimension with order numbers, all the descriptive attributes are located in other dimensions.Characteristics, for example, order numbers, summarize the data into groups. This enables you to analyze the materials in an order.

Line-Item Dimensions

If the situation described below occurs with an InfoObject (meaning that the dimension table is approaching the same size as the fact table) in BW 2.0 and later Releases the dimension must be designated as a line-item dimension during the design phase of an InfoCube. This dimension can be assigned to exactly one InfoObject (the line-item InfoObject).

When you activate the InfoCube, NO database tables are generated for this dimension. Instead, a field with the data element RSSID is written to the fact table. This field refers directly to the SID table for the InfoObject.

In other words: The dimension table is no longer used.In the star schema diagram:

ŸInfoCube -> Dimension -> InfoObject (for this line-item InfoObject) becomes
ŸInfoCube -> InfoObject.

The LISTSCHEMA transaction shows the fact table with a pointer aimed directly at the SID of a single characteristic that has been placed in the line-item dimension.Each characteristic can be a line-item - even Customer or Material.