Unite SQL statements by CASE Expression

 Posted on 2022-03-19  |  2 minutes  |  335 words  |  Kazuhiko Takata

Until Split Long SQL Using Views post I created four activities and each SQLs, and I found three of four have same table join pattern. So I created VIEW to shorten JOIN predicate for each SQL. It is nice to shorten total statement volume but almost all statement except for JOIN predicate is duplicated among SQLs. When something change is required, maintenance of each SQLs is annoying work. Today I would like to integrate SQLs using CASE expression. [Read More]
Data Integration  Transformation 

Split Long SQL Using Views

 Posted on 2022-03-12  |  3 minutes  |  444 words  |  Kazuhiko Takata

At last post I wrote Compose Activity from Joining Multiple Tables to create second activity Close Issue. Final version of SQL statement was long even I just used three tables. In real process mining project I handled hundred of tables and wrote quite long SQLs. At that time I faced same patterns of SQL in multiple activities. So I introduces VIEW in my SQLs for grouping same pattern of SELECT SQL, similar to create function (method) in programming. [Read More]
Data Integration  Transformation 

Compose Activity from Joining Multiple Tables

 Posted on 2022-03-05  |  2 minutes  |  407 words  |  Kazuhiko Takata

In the last post of Insert Simple Record into Activity Table, I created SQL to insert Raise Issue activity. That SQL was simple because only one table issues are used as data source. Today I will create SQL of Close Issue activity that requires multiple tables. Same as previous post, first I should analyze columns to compose Close Issue activity. This is happend when I change Status column in each issue, so I need change history. [Read More]
Data Integration  Transformation 

Insert Simple Record into Activity Table

 Posted on 2022-02-26  |  3 minutes  |  439 words  |  Kazuhiko Takata

In the last post of Determine Process Mining Tables based on Project Goal, I talked strategy of transforming Planio data to event log. Then I wrote SQL to create activity table. From now I would like to insert activity record to that table. Based on the discussion of last post, at least I need two activities Raise Issue and Close Issue to measure throughput time between them. That is the next step to achieve my goal. [Read More]
Data Integration  Transformation 

Determine Process Mining Tables based on Project Goal

 Posted on 2022-02-19  |  2 minutes  |  400 words  |  Kazuhiko Takata

I talked about how to consider Case ID in advance in last post. That is same as goal setting of process mining project, it means what would like to be measured in process mining and why. Case is the unit of measuring performance and grouping activities. Considering my case of Planio, for example I would like to measure 1) Throughput time from raising issue to closing it, 2) How many users are involved until closing issue. [Read More]
Data Integration  Transformation  Data Model 

Consider Case ID before Starting Transformation

 Posted on 2022-02-12  |  2 minutes  |  380 words  |  Kazuhiko Takata

After long explanation of Extractor Builder, I can move to Transformation Topic from now, using Planio issue and change history. But before starting detail discussion, I would like to discuss general issues at first. In process mining context, Transformation is procedure to generate event log table from source tables. Event Log or Event Data is the collection of case and its event (activity) with timestamp. Case ID can be associated with multiple activities in source system, in other word it is not possible to generate event log without case ID. [Read More]
Data Integration  Transformation 

Tune Endpoint Parameter Relevant to Delta Load

 Posted on 2022-02-05  |  3 minutes  |  434 words  |  Kazuhiko Takata

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. [Read More]
Data Integration  Extractor Builder 

Setup Dependent Endpoint in Extractor Builder

 Posted on 2022-01-29  |  2 minutes  |  372 words  |  Kazuhiko Takata

In the last post Configure Endpoint for Suitable Extraction, I configured Endpoint in Extractor Builder to suit my business requirements, and still there are points to extract change history of issues, and to extract data by Delta Load option. Today I would like to setup regarding change history using Dependent Endpoint in Extractor Builder. At first how do I extract change history of Planio Issue ? Again I looked at Planio Documentation and found I can get single issue with journals (meaning change history in Planio). [Read More]
Data Integration  Extractor Builder 

Configure Endpoint for Suitable Extraction

 Posted on 2022-01-22  |  3 minutes  |  468 words  |  Kazuhiko Takata

In the last post Connect to Source System via REST API, I shared how to set up Extractor Builder and extracted Issue from Planio. It was shortest path to be avaiable for extraction job, so it is not enough for production job. Today I would like to configure Endpoint in Extractor Builder to resolve problems I experienced. First problem I faced is upper limit of extraction data. Some day I found that I could not get issue record until 25. [Read More]
Data Integration  Extractor Builder 

Connect to Source System via REST API

 Posted on 2022-01-15  |  3 minutes  |  506 words  |  Kazuhiko Takata

At previous post Prepare Source System to Generate Event Log, I prepared Planio as source system for this blog, and entered few events (create Issue, update Issue Status) to it. Now it is time to extract event log from Planio. As other SaaS solution do, Planio also has REST API to extract data from outside. Currently Celonis EMS has ability to extract from arbitrary system that has REST API, Extractor Builder. [Read More]
Data Integration  Extractor Builder