In an Enterprise Data Warehouse context, InfoObjects often play an arbitrary double role: modeling the Data Warehouse Layer and multi-dimensional modeling the Reporting Layer. I advise segregation of duties by introducing a second, independent set of InfoObjects: Data Warehouse InfoObjects.
We discussed a conceptual overview in Introducing Data Warehouse InfoObjects - Part 1: Conceptual Overview. This blog provides the technical details.
Furthermore, I would like to recommend the SAP Press book ABAP Dictionary by Tanmaya Gupta. You can find detailed explanation of domains and data elements in chapter 3 and 4.
ABAP Data Dictionary
The ABAP Data Dictionary is one of the cornerstones of SAP applications. You use the ABAP Dictionary to create and manage data definitions (metadata). The ABAP Dictionary permits a central description of all the data used in the system without redundancies. New or modified information is automatically provided for all the system components. This ensures data integrity, data consistency and data security.
The ABAP Dictionary supports the definition of user-defined types (data elements, structures and table types). You can create the corresponding objects (tables or views) in the underlying relational database using these data definitions. The ABAP Dictionary describes the logical structure of the objects used in application development and shows how they are mapped to the underlying relational database in tables or views.
In the context of this blog, we are going to use to the ABAP Dictionary to describe all the fields that are relevant for extraction. The purpose of all this is to collectively create the required InfoObjects in SAP BW in an automated way.
Analysis of the DataSource
Let’s start with the DataSource. The example is worked out for DataSource 2LIS_11_VAHDR (Sales Document Header Data).
Table ROOSOURCE contains the DataSource header data.
Figure 1: Table ROOSOURCE - DataSource Header
Next to some other interesting fields (e.g. type and delta method) we are particularly looking for the extract structure. In this case we find extract structure MC11VA0HDR.
You will notice that not every field of the extract structure is always relevant for extraction. There is another table that can overrule the appearance of fields: table ROOSFIELD. Here it’s determined which fields will be extracted.
Figure 2: Table ROOSFIELD - DataSource Fields
There are basically 2 settings important to exclude particular fields from extraction:
- SELECTION: Properties of a DataSource Field;
- NOTEXREL: Relevance Field for Standard Extractor.
If the field SELECTION is set to ‘A’, the field will be hidden during extraction in both source system and BW system.
If the field NOTEXREL is set to ‘N’, the field will not be passed to the extractor.
Conclusion: the fields relevant to extraction can be filtered by excluding both conditions.
Analysis of Extract Structure Fields
The next step is to determine the Data Dictionary properties of the remaining fields of the extract structure. This is done using table DD03L. Don’t forget to filter on the field COMPTYPE: only ‘E’ (Data Element) and <initial> (Build-in Type) are applicable.
Figure 3: Table DD03L - Extract Structure Fields
The important fields are:
- ROLLNAME: Data element (semantic domain);
- DATATYPE: Data Type in ABAP Dictionary;
- LENG: Length (No. of Characters);
- DECIMALS: Number of Decimal Places;
- DOMNAME: Domain name;
- COMPTYPE: DD: Component type.
Data Elements and Domains
Let’s have a closer look at 2 objects of the ABAP Data Dictionary: domains and data elements.
Tables and structures consist of fields. It’s very likely that the same field will occur in more than one table or structure. Moreover, it’s quite common that there will be synonyms for the same field. For this reason, the ABAP Dictionary introduced domains and data elements to offer flexibility and powerful maintenance of the data model.
Figure 4: Data Dictionary Overview (Source: SAP AG)
A domain defines the technical attributes of a table or structure field. One can think of data type, length, decimal places and conversion routine. This information can be found in table DD01L. The texts are stored in table DD01T.
Figure 5: Table DD01L - Domain
Figure 6: Table DD01T - Domain Texts
In the context of creating InfoObjects in SAP BW, a domain can be used to create the Basic InfoObjects and will often play the role of reference InfoObject for creating other related InfoObjects.
A data element defines the semantic attributes of a table of structure field. One can think of documentation, field labels and search help. This information can be found in table DD04L. The texts are stored in table DD04T.
Figure 7: Table DD04L - Data Element
Figure 8: Table DD04T - Data Element Texts
In the context of creating InfoObjects in SAP BW, a data element can be used to create the “dependent” InfoObjects and will often be based on a Reference InfoObject (the Basic InfoObject belonging to the related domain).
We had a look at the ABAP Data Dictionary, and how to analyze the DataSource and the extract structure. The purpose of all this is to come to a proposal for bottom-up generating the Data Warehouse InfoObjects based on the various data dictionary objects.
The generation of Data Warehouse InfoObjects can be quite time consuming and error prone. That’s why I suggest introducing an automated way for creating those InfoObjects collectively. Please consider writing an ABAP program, LSMW (Legacy System Migration Workbench) or a combination of both to automate the process.
Last but not least, I created an ABAP program to generate Data Warehouse InfoObjects for SAP source systems’ DataSources. Please refer to my blog Generating Data Warehouse InfoObjects - Part 1: Introduction for more details.