Until last post Setup Dependent Endpoint in Extractor Builder, I prepared endpoints of both Planio Issues and their journals. Today I would like to tackle final setup of extractor to deal with Delta Load option.
Referring to the Planio Documentation, updated_on column exists for filtering Issues. This timestamp column is updated when creating and updating relevant issue, so it is appropriate column for Delta Load. Open Celonis Extractor builder then go to 4 Define Endpoints. In the 2 Configure Request section click Request Parameters, then click New Request Parameter button. As below screen I setup updated_on parameter. This time filtering value is provided from already extracted data, so I clicked second radio button. Finish Endpoint setting then move to extraction setting.
In the extraction setting, I created new parameter today and set today’s date (this parameter will be updated from what triggers data job, in my case ML workbench). Then write Delta Filter Statement as updated_on = <%=today%>. You may think the best way is to get maximum date of updated_on and compare as updated_on > <%=maximum date of updated on%>, but for the moment I could not do that. I hope this function will work later.
I updated one of issue in Planio and started data job, then I can extract only this issue as expected. OK, delta load seems to work correctly, but if I plan to kick data job many times per day (e.g. hourly), I will extract same issues and journals many times. Below screen shows that same journal 32 is extracted twice by two extraction jobs respectively. I expect to overwrite first record by second one, but how to do it ?
I should go back to extractor builder and setup Table Configuration in Configure Response. In this setting I will tick primary key button against id column of journals table as below. After that id behaved as primary key at extraction data job then update existing record based on id.
Same setting is required for journals$details table, and this time not only id but property and name are also primary keys (this record is created per changed column of each journal).
I reset record by Full Load then tried Delta Load again. This time journal 32 is overwritten by delta load correctly.
It is long to explain extraction setting, but I intended to explain next step, transformation. From next post I will do that.
Kaz
I would like to share my configuration file to you. If you would like to just use this, you will click New Extractor menu and Import from file, then use my json file.