|
Transform Phase |
Scroll Previous Topic Top Next Topic More |
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 can be created here and data views as well. A data view is a join of tables that are re-used 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.
|
Recommended: 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.
Because these tables store lots of different table information, we already 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')))
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
