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

[former_member689462]

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 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.