SAP Business Warehouse Infoobject Continued

SAP Business Warehouse Info object is discussed in the previous topic and here is the continuation for that.n In BW the term master data refers to attributes,hierarchy information, and texts. Master data attributes in BW correspond closely with master data objects in R/3.

Example: 0SOLDTO attributes in BW are derived from the fields in the customer master table in R/3.The data (stored in a cluster of tables) inside the highlighted area is the Info Cube. Any information outside the Info Cube is additional information that can be used by all the other Info Cubes. Hierarchy information can also be shared like this.

Modeling Attributes: Decisions

If you use navigation attributes, the system performance is not as good as when you integrate the attribute directly into a dimension table.Using navigation attributes means that you do not need to reconstruct the fact table if the data changes.Aggregates cannot be generated from time-dependent navigation attributes. Aggregates can also not be generated from display attributes, whether they are time-dependent or not.

An attribute that has been set as a display attribute delivers only descriptive information in the query results. You are not able to use a display attribute to navigate in a set of data.An attribute that has been designated a navigation attribute has all the navigational capabilities of a dimension characteristic, without actually being in the dimension itself.You can make an attribute time-dependent if you need to determine separate time intervals in which each of the attribute values is valid. You are not, however, able to use a time-dependent attribute to construct aggregates.

Surrogate ID (SID) Tables in the Extended Star Schema

A SID table uses numeric fields as indexes. This makes it possible to access data more quickly. It also reduces the amount of time and effort needed to stage the data, because it means that the system does not need to access large master data tables unnecessarily.SID values are 4 byte integers. Using numeric fields as indexes also improves system performance, compared to if you use long alphanumeric values.

SID tables (pointer tables) provide the technical link to the master data and hierarchy tables that are outside the dimensions of a star schema.Surrogate ID (SID) Tables in the Extended Star Schema Benefits:
Large dimension tables are built using the combination of numeric SID values of each characteristic in the dimension. Secondary indexes for each dimension improve system performance.External information (attributes of the characteristics and external hierarchies) is stored separately (shared) and linked to the Info Cubes.You can report on historical relationships as well as the current status of the data without any major reduction in system performance.Several different languages can be supported without reducing system performance.

SAP BW: Extended Star Schema

The BW extended star schema is different to the basic star schema. It is subdivided into a solution dependent part (InfoCube) and a solution-independent part (attribute tables, text tables, and hierarchy tables) that is also shared among the other Info Cubes.The dimension attributes of the dimension tables are called characteristics. The dimension attributes located in the master data table of a characteristic are called the attributes of the characteristic.

The great challenge when designing a solution is to decide whether to store a dimension attribute in a dimension table (and therefore in the InfoCube) or in a master data table.Data is loaded separately into the master data tables (attribute tables) text tables and hierarchy tables. n The SID table is the link between the master data and the dimension tables.

Star Schema in BW  with Tables

In BW 2.0 the system creates SID tables for navigation attributes automatically whenever you create an Info Object with navigation attributes. This means that the OLAP processor reads only the SID tables when a query is executed. There is no need to read the master data tables for the navigation attribute, and this improves the system performance.Master data tables are named based on their time dependency. For example, for InfoObject 0MATERIAL the tables are named as follows:

ŸP-table = PMATERIAL - time-independent master data
ŸQ-table = QMATERIAL - time-dependent master data
ŸM-view = MMATERIAL - a combination of P and Q

Mater Data Tables and SIDS

These tables or views are created when the corresponding options are selected from the maintenance screens for a characteristic. None of the tables are mandatory. If you do not select the option for creating master data, no tables of this type are generated.You need to be aware of the following relationships: The P-table, for example, is linked to the Xtable, and the Q-table is linked to the Y-table.

Master data tables and views of InfoObjects:

Example: Info Object 0MATERIAL 

P-table = PMATERIAL (time-independent master data)
Q-table = QMATERIAL (time-dependent master data)
M-view = MMATERIAL (a combination of P and Q)
SID Tables for InfoObjects:
S-table = SMATERIAL  table to link material SIDS with material numbers
X-table = XMATERIAL A table to link material SIDs with SIDs for time-independent navigation attributes.
Y-table = YMATERIAL A table to link material SIDs with SIDS for time-dependent navigation attributes.

Extended Star Schema: Tables

The number of tables that are generated depends on the definition in the Info Object maintenance. The S-table links time-independent and time-dependent attributes to the dimension tables of an Info Cube. The P-table and the Q-table are displayed in a view (M-table)navigation attributes are read using the view (M) and the master data tables (P and Q) of the associated Info Object, before they are linked using the X-table or the Y-table of the higher-level Info Object to the dimension tables of the Info Cube.

Navigation Attributes in the Extended Star Schema

The first step in reading data usin g the material group (0MATL_GROUP) navigation attribute, is to read the data using the SID table of the navigation attribute.The SID of the navigation attribute links to the Y-table of the characteristic (in this case, MATH_TH_C) - this applies to time-dependent navigation attributes. The X-table is used for navigation attributes that are non time-dependent.In the Y-table, the key date of the query determines the data record that is valid for this particular time period.In the dimension table, the SID of the material characteristic determines the DIM ID.The DIM ID links the dimension table to the fact table. n When data is read using the OCALMONTH characteristic, there is a direct link between the SID table of the Info Object and the dimension table. Again, it is the DIM ID that accesses the fact table.

The LIST SCHEMA Transaction

The LISTSCHEMA transaction shows not only the InfoCube tables, but all the other tables in the extended star schema right down to the attribute tables. Hierarchy tables and text tables are not shown.

The LISTSCHEMA transaction provides:

The LISTSCHEMA Transaction Indented and nested lists of all the InfoCube tables and supporting tables. The data browser transaction accesses each table.Review table design and links AND  Navigate to table contents.Other options: InfoCube contents tab or LISTCUBE transaction

Review nested structure
Other options: ABAP Dictionary Browser

Table Partitioning

Use: You can use the partitioning function to divide the entire dataset of an InfoCube into several smaller units that are independent of one another and do not contain any redundant information. Subdividing the data in this way can improve the system performance when you are reporting on the data or deleting data from the InfoCube.

Requirements: The only way to implement the partitioning process is to use one of the two partitioning criteria - calendar month (0CALMONTH) or fiscal year/period (0FISCPER). The InfoCube must contain at least one of these two InfoObjects.Functions: When you activate the InfoCube, the fact table is saved to the database, with a number of partitions that correspond to the value area. You are able to determine this value area yourself.

Example: You select the 0CALMONTH partitioning criteria and determine a value area of 01.1998 to 12.2003 ==> 6 years * 12 months + 2 = 74 partitions are created (2 partitions for values that lie outside of the area, that is < 01.1998 or > 12.2003).

You can also determine the maximum number of partitions that are created on the database for the fact table of the InfoCube. You choose the 0CALMONTH partitioning criteria, determine a value area of 01.1998 to12.2003, and set the maximum number of partitions to 30 ==> The value area results in: 6 years * 12 calendar months + 2 marginal partitions = 74 single values. The system generates a partition every three months, meaning that one partition represents a separate quarter. 6 years * 4 partitions per year + 2 marginal partitions = 26 partitions generated on the database.

Related Post

No comments :

Post a Comment