Purpose of this Blog

This blog is to share my experience and knowledge gained during the SAP BW and TrackWise integration project. Searched a lot on the internet, SAP blog, and community for this knowledge, all lead to blank spaces.

This blog gives an SAP BI consultant all information’s which require for successful integration.

Introduction:

This document helps SAP BW consultant to understand the integration process between SAP Data Warehouse & TrackWise system and covers a basic understanding of TrackWise. As TrackWise is a NON SAP product BI consultant needs to understand a couple of points before designing the data model. This document will cover all those aspects like:

• Database design
• What all tables need to refer the same process as SAP ECC
• Types of tables like SAP concepts (Master and Transaction tables)
• How to understand data in tables
• The process to make them in understandable/reporting format

Database Design:

TrackWise transaction tables are built on Normalization concepts and using internal ID’s to reference values. To generate transaction values, it requires a connection between multiple tables using internal ID’s. Important Fields to refer:

• Process record (PR)
• Attribute ID
• Data Field ID
• Grid ID
• Sequence numbers

Important tables in TrackWise:

Table Types:

Master data tables:

Master data tables have one to one mapping and contain attributes that will use for Transactional process records. Process record transactional tables contain the ID. These ids relate to Master data tables by which we connect these tables and generate analytics.

Single Level Transactional tables:

This concept tables have ONE to ONE cardinality in between Process ID and other transactional attributes.

Multi-level Transactional tables:

This concept tables have ONE to MANY cardinalities in between Process ID and other transactional attributes.
GRID tables also follow multi-level table concepts.

How to extract data?

Source system connection with SAP BW:

TrackWise (AWS) can connect with SAP BW (7.X) through DB Connect as it helps us to convert certain fields definition which is not supported by classical SAP BW system like Clob / Blob and long strings.

Tables Extraction:

Goto ->Data Source -> Select Source system -> Create Data Source as mention in below screenshot

Data Source Creation

Know your Tables and Data:

This section is important for SAP BI consultant to understand tables definition and data placement help to the link between Design Blueprint with user’s requirement as it is a NON SAP source system.
TrackWise tables follow normalization and internal ID concept to store data and tables connections. To retrieve information, we need to understand a few points:

• List of fields user required to extract
• List of Field ID’s for the required Fields
• What are the logic’s to extract values for fields

List of fields user require to extract:

This is generally formulated during the requirement gathering session with the end users.

List of Field ID’s for the required Fields:

Once we have final list of fields then refer to table DATA_FIELDS. This master table stores all attributes related to individual fields.

Generate required Fields:

Now I have a list of fields (User Requirement) and their respective Field ID. Please refer to below dummy example for understanding:

PR additional table has Process record and respective fields stores normalized and internal ID fashion as shown below.

Fields details are available in DATA Field table. As part of reporting requirement I need to translate this data in below format:

Achieve through a  ASSIGN COMPONENT <datafield> OF STRUCTURE code in routine.

                                                     De-Normalization Diagram

What are the logic’s to extract transactional values for fields?

The base table to start extraction is PR_ADDT_DATA. This table contains all Process Record and its transactional attributes placed in a normalized fashion. Refer to below structure shows PR_ADDT_DATA basic structure:

Important fields from PR additional table:

Single Level Transactional tables:

                                                   Process Diagram

There are multiple process available to generate field values based on the Field data types:

Process 1: De-normalize records Using FIELD_ID from DATA_FIELDS table to generate field names

Process 2: N_VALUE value pass to field values: Few fields data are stored directly in N_VALUE field of PR additional table.

Process 3: N_VALUE value pass to ADDITIONAL_TYPE: Pass N_VALUE field value to ADDITIOANL_TYPE table and receive values from it (PR_ADDT_DATA-N_VALUE = ADDITIONAL_TYPE-ID).

Process 4: N_VALUE value pass to ENTITY: Pass N_VALUE field value to ENTITY table and receive values from it (PR_ADDT_DATA-N_VALUE = ENTITY-ID).

Process 5: N_VALUE value pass to PERSON_RELATION: Pass N_VALUE field value to PERSON_RELATION table and receive values from it (PR_ADDT_DATA-N_VALUE = PERSON_RELATION-ID).

Process 6: DATE_VALUE value pass to field values: Few Field’s data are stored directly in DATE_VALUE field of PR additional table.

Process 7: S_VALUE value pass to field values: Few Field’s data are stored directly in S_VALUE field of PR additional table.

Multi-level Transactional tables:

The process is quite similar to the data extraction using Single transactional tables. The only key difference is during denormalization add one more object in Primary key to store record counts as one Process record will have multiple values for attributes.

Grid tables:

Grid tables refer to functional data containers. Example: Equipment, Product, Status, etc.
Each functional area assigns with GRID ID. Once you finalize GRID ID to respective tables then the remaining process follows the same as we refer in Single/Multiple transactional tables.

                                                       Grid Process Diagram

Assign fields to process

These are the 3 Key concepts (List of fields, Generate Fields, Extract values for fields).

Conclusion:

These all are the knowledge and experience I gained during my integration project. This document will help other colleagues during the integration project.
Once we understand this data profiling and Data extraction logic’s then it is easy to build multiple KPIs and Analytics report on top of it.