Skip to main content

Transform Phase

In the transform phase the TaskApp with 02transform scenario is employed. In this phase, the .qvd-files from the first phase are transformed. If you have already extracted modified tables with a connector (i.e. SAP connector) you can store them through this app. New keys and data views can be created here as well. A data view is a join of tables that are reused several times in the third scenario 03eventsource.

An event log needs timestamps for each activity. If there is a need to create a timestamp from two different fields (a date and a time field) then make these changes in the tables in this step.

💡 Recommendation: If the tables are very large and not all the information is required for the event log generation, apply "where" filters in this step to reduce table size.

Example​

Continuing with the previous example, the tables CDHDR, CDPOS and RSEG need to be combined to one data view table to derive an event (see figure below). As the tables CDHDR and CDPOS are used multiple times for different purposes, the join of these two tables is performed in the 02transform scenario.

These tables store lots of different table information, so we reduced their size to the purchase orders information in the first phase (where statement OBJECTCLASS=’EINKBELEG’ - or Purchase Receipt in English) which reduces the size of the tables and improves their performance. The timestamps are also formatted here for the correct execution of the mining algorithms. We obtain the timestamp AETST from the CDHDR table by combining the UDATE with UTIME as done below:

  • AETST = timestamp(date(date#(CDHDR_UDATE,'YYYYMMDD')) +  time(time#(CDHDR_UTIME,'hhmmss')))

    Data Model of CDHDR - CDPOS and RSEG
    Data Model of CDHDR - CDPOS and RSEG
    To connect the tables, the following keys are used:

  • %CDHDR_PKEY: [MANDANT] & '_' & [OBJECTCLAS] & '_' & [OBJECTID] & '_' & [CHANGENR]

  • %EKKO_PKEY:  mid(TABKEY, 1, 3) & '_' &  mid(TABKEY, 4, 10)

 

Example CDHDR_CDPOS view creation​


LET mpmXvData = 'lib://mpmX_data/';

LET mpmXvETLFolder = '$(mpmXvData)process_analytic_models/$(mvProcessDescription)/';


SET mvDateFilter = '2018-07-01';

SET mvYearFilter = '2018';


sub mpmX_execute


  CDHDR_CDPOS_EINKBELEG:
  LOAD *,
       [CDPOS_MANDANT] & '_' & [CDPOS_OBJECTCLAS] & '_' & [CDPOS_OBJECTID] & '_' & [CDPOS_CHANGENR] as [%CDHDR_PKEY]
  FROM [$(mpmXvETLFolder)01_Extract/transformed/CDPOS_EINKBELEG.qvd]
  (qvd);

  inner join(CDHDR_CDPOS_EINKBELEG)
  LOAD *,
       [CDHDR_MANDANT] & '_' & [CDHDR_OBJECTCLAS] & '_' & [CDHDR_OBJECTID] & '_' & [CDHDR_CHANGENR] as [%CDHDR_PKEY],
       timestamp(date(date#(CDHDR_UDATE,'YYYYMMDD')) + time(time#(CDHDR_UTIME,'hhmmss'))) as CDHDR_AETST
  FROM [$(mpmXvETLFolder)01_Extract/transformed/CDHDR_EINKBELEG.qvd](qvd)
  where timestamp(date(date#(CDHDR_UDATE,'YYYYMMDD')) + time(time#(CDHDR_UTIME,'hhmmss'))) > '$(mvDateFilter)';

  store CDHDR_CDPOS_EINKBELEG into [$(mpmXvETLFolder)02_Transform/views/CDHDR_CDPOS_EINKBELEG.qvd] (qvd);
  drop table CDHDR_CDPOS_EINKBELEG;

end sub