Skip to Content
Technical Articles

Integration of SAP BW (7.X) and TrackWise (AWS)

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 Name Description Table Type
ADDTL_TYPE Additional Type Master Data
ADDRESS Address Master Data
DATA_FIELDS Data Field Master Data
DIVISION_TYPE Division Type Master Data
ENTITY Entity Master Data
GRID_DATA Grid Data Master Data
PR_ADDT_DATA PR Additional Data Transaction Data
PR_ACTIVITY Activity Master Data
PR_ACTIVITY_TYPE Activity Type Master Data
PR_STATE_NODE State Node Master Data
PR_STATUS_TYPE Status Type Master Data
PR_TEXT_DATA Text Data Master Data
PR Process Record Master Data
PR_ELEMENT Element Master Data
PR_PRIORITY_TYPE Priority Type Master Data
PERSON Person Master Data
PERSON_RELATION Person Relation Master Data
PROJECT Project Master Data

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.

PR Additional Table Data field Table
Process ID Field ID Field ID Field Name
9898 123 123 State
8989 321 321 Formula Number
9999 111 111 Batch Numbers

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

Process Transaction
Process ID State Formula Number Batch Numbers
9898 XX 123XX BXCH

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:

ID
DATA_FIELD_ID
PR_ID
S_VALUE
N_VALUE
DEC_VALUE
DATE_VALUE
DATE_TIME_VALUE
SEQ_NO
DATE_UPDATED
FILE_TYPE
EXTERNAL_DATA_ID
N_VALUE_ADDTL

 

 

 

 

 

 

 

 

 

 

Important fields from PR additional table:

Field Name Description
PR_ID Process record ID
DATA_FIELD_ID Field ID
S_VALUE S_VALUE
N_VALUE N_VALUE
DATE_VALUE DATE_VALUE

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%20Process%20Diagram

                                                       Grid Process Diagram

 

Assign fields to process

Process No. Data Type
2 Numbers
3 Single Selection
4 Entity
5 Person
6 Date
7 String

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.

Be the first to leave a comment
You must be Logged on to comment or reply to a post.