Explore Tables of a Master Data-Bearing Characteristic

To explore the possibilities with master data modeling in SAP BW/4HANA, let's have a look at the definition of a characteristic, with a focus on master data-bearing characteristics.

When you define a characteristic in BW Modeling Tools, settings like Data Type and Length can be defined on the General tab.

The maximum length for the characteristic values is 250 characters for the total (compound) length of the key.

Only if necessary, you can use special data types for big data:

• To overcome the limitation of two billion for standard integer values, use the key figures data type, Integer 8.

• Characteristics with the property High Cardinality can have more than two billion entries.

  Note

  The property High Cardinality is only supported for data type CHAR and NUMC with length >=10.

In the Compounding pane on the General tab page of a characteristic definition, determine whether you want to compound the characteristic to other InfoObjects. This means that the compounded InfoObjects will be part of the key in all master data tables of this characteristic.

Compounding is used to define characteristic values uniquely. For example, what if the storage location ID 200000 for plant A, and the same storage location ID 200000 for plant B refer to different physical locations? In this situation, you can only distinguish the characteristic storage location in connection with the plant. In this case, compounding the characteristic storage location to plant makes it unique.

If needed, you can also compound several characteristics, such as source system ID and plant.

A characteristic can store three types of master data:

• Attributes

• Texts

• Hierarchies

The master data is stored separately for attributes, texts, and hierarchies. An example of an attribute is a related field such as address. Texts provide descriptions, and hierarchies are used for rolling up transactional data, for example.

The system creates the following tables when a characteristic is activated (depending on the settings you have configured on the tab pages):

• Surrogate table (S table): It contains an artificial key (called a surrogate ID or SID) for each semantic key value of the corresponding characteristic.
• Attribute tables: The P table contains attributes that are not time-dependent, the Q table contains time-dependent attributes (in both cases, display and navigation attributes).
• Navigation attribute tables: The X table contains surrogate IDs of the time-independent attributes, the Y table contains surrogate IDs of the time-dependent attributes.
• Text table: The T table contains texts.
• Hierarchy tables: These tables are discussed later in this Unit.

The purpose of the S table is to list all existing values for reference checks. The purpose of the surrogate IDs in the S, X and Y table, is to improve the performance for finding navigation attribute values. The system automatically generates the Surrogate ID (SID) keys when the master data is uploaded. The key for the S table is the characteristic for which the Surrogate ID values have been generated. If the characteristic is compounded, the key also comprises the compounded characteristic.

A text table is created, only if you selected the Texts checkbox when you created, and then activated the characteristic InfoObject. You can choose from the following properties:

• Short text: 20 characters.

• Medium-length text: 40 characters.

• Long text: 60 characters.

  Long text with the option Long Text is Extra Long can have up to 1333 characters.

• Texts can be language-dependent.

• Texts can be time-dependent.

The preceding figure shows the structure of the text table, using the example of the customer-defined characteristic BP. The indicators for short text, medium-length text, time, and language-dependency were set on the Master data/Texts tab page. Therefore, these fields are included in the structure of the text table.

Characteristic ROLE is also included in the text table, because it has been defined as a compounding attribute.

If you have selected the Master data option on the General tab page, you can maintain attributes for the characteristic on the Attributes tab page.

You can choose the following settings related to attributes:

• Time-independent display attributes.

• Time-dependent display attributes.

• Time-independent navigation attributes.

• Time-dependent navigation attributes.

• XXL attributes.

Let's first have a look at tables related to display attributes.

For each master-data-bearing characteristic, a maximum of two attribute tables can be generated for display attributes (time-dependent [Q] and time-independent [P] attributes.) Providing that either the P table or the Q table exists, a view /BIC/M<name of characteristic> is automatically generated using P or Q, or P and Q.

Let's look at the following example, showing a table /BIC/PBP for time-independent display attributes, and /BIC/QBP for time-dependent display attributes for characteristic BP.

Let's look at navigation attributes.

If an attribute must be filtered and drilled down, turn it into a Navigation Attribute:

1. Choose Navigation Attribute as Attribute Type on the Attributes tab of its basic characteristic. Define if values should be stored as time-dependent. Note that if an attribute is defined as time-independent, existing versions with the same key will be overwritten by a new data load.
2. Release the navigation attributes at the InfoProvider level (CompositeProvider or Open ODS view). Otherwise, they can be used as display attributes only.
3. When defining Queries, use navigation attributes in the same way that you use characteristics, for example, as Free Characteristics or in a selection.

Let's look at the tables related to navigation attributes.

The X table is only generated if at least one time-independent attribute is defined as a navigation attribute. The key of the X table is the SID of the basic characteristic and the object version. The foreign key for a navigation attribute (data element S_<name of attribute>) is the SID of the attribute characteristic.

The Y table is only generated if at least one time-dependent attribute is defined as a navigation attribute. The behavior corresponds to the way that the X table is used, but with one difference: To distinguish different records for different time intervals, DATETO is part of the key. The record that contains the Query's key date is chosen.

SAP BW/4HANA also offers the option to use XXL attributes.

XXL attributes are XXL InfoObjects, which are logically assigned to the characteristic. You can use XXL InfoObjects to save additional information for a characteristic as data type STRING or XSTRING. You can further specify the data type by using an MIME type. Numerous formats are supported including different document types, audio files or video files, texts, and images.

When InfoObjects are associated to a field of a DataStore Object (advanced), and SID numbers are generated in the DataStore Object (advanced), several tables are involved in retrieving data. SID tables link the master data to the transactional data.

Suppose that a filter such as "P100" for profit center (time-independent navigation attribute for cost center) must be executed on a DataStore Object (advanced) with cost center data. The following steps are executed:

1. From the key value P100 of the profit center, the SID value is derived.

2. In the X table of the cost center, the system looks for all corresponding values of the cost center.

3. This SID number is chosen to search in the DataStore Object (advanced).

When using attribute settings, note the following:

• All InfoObjects can be display attributes, but units and key figures cannot be navigation attributes.

• It is best practice to use time characteristics with navigation attributes. (0CALYEAR as an attribute of 0CALDAY).

• A navigation attribute of a navigation attribute can be turned into a transitive navigation attribute.

  Note

  The use of transitive attributes is explained later in this unit.

• You can restrict the possibility to use an attribute as a navigation attribute. Characteristics that have the Attribute Only property, can only be display attributes, not navigation attributes of other characteristics. In addition, for a specific basic characteristic, you can switch back to Display Attribute in the Attribute tab of the basic characteristic.

• With time-dependent attributes, you can view current transaction data with historical attribute values and historical data with current attribute values. Make sure that the users know what they see.

• Using time-dependent attributes has a slight impact on the performance of data-loading processes and data-reading processes, compared to using time-independent attributes (because the corresponding time key field must be evaluated).

• Using navigation attributes has a slight impact on the performance of data-loading processes and data-reading processes, compared to using characteristics (because X or Y tables are involved).