ETL Script Examples

From QPR ProcessAnalyzer Wiki
Jump to: navigation, search

This page consists of examples of ETL (Extract, Transform, Load) scripts you can use in QPR ProcessAnalyzer as examples when creating your own scripts.

Create a copy of existing model

The following script will:
1. Clear the current contents of the target model

  • ProjectName ExampleProject => CHANGE THIS to be the project in which the target model exists.
  • ModelName ExampleModel => CHANGE THIS to be the name of the target model.

2. Select all events from another model defined with FilterId = 1234567. (note: change this Id to be an Id from the default Filter (previously called View) of the source model:

  • AnalysisType 6 => Event Table
  • MaximumCount 0 => Retrieve all events from the model
  • FilterId 1234567 => CHANGE THIS to be the Filter ID / FilterId of the source model from which you want to make a copy
  • SelectedEventAttributes * => Get all Event Attributes
  • TargetTable #AnalysisResult => Store the result into a temporary SQL table named #AnalysisResult
  • #GetAnalysis => Execute the query (stores the result into #AnalysisResult)

3. Import all events to the specified model (#ImportEvents)

  • ProjectName ExampleProject => CHANGE THIS to be the project in which the target model exists.
  • ModelName ExampleModel => CHANGE THIS to be the name of the target model.

4. Select all case attributes from the source model (defined with FilterId = 1234567)

  • AnalysisType 5 => Case Table
  • MaximumCount 0 => Retrieve all cases from the model
  • FilterId 1234567 => CHANGE THIS to be the Filter ID / FilterId of the source model from which you want to copy case attributes
  • SelectedCaseAttributes * => Get all Case Attributes
  • TargetTable #AnalysisResult2 => Store the result into a new temporary SQL table named #AnalysisResult2
  • #GetAnalysis => Execute the query (stores the result into #AnalysisResult)

5. Import all case attributes to the specified model (#ImportCaseAttributes)

  • ProjectName ExampleProject => CHANGE THIS to be the project in which the target model exists.
  • ModelName ExampleModel => CHANGE THIS to be the name of the target model.
(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'ModelName', 'ExampleModel')
--#RemoveEvents

(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'FilterId', '1234567') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis

(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'ModelName', 'ExampleModel')
(SELECT * FROM [#AnalysisResult])
--#ImportEvents

(SELECT 'AnalysisType', '5') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'FilterId', '1234567') UNION ALL
(SELECT 'SelectedCaseAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult2')
--#GetAnalysis

(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'ModelName', 'ExampleModel')
(SELECT * FROM [#AnalysisResult2])
--#ImportCaseAttributes

Create an extended copy of existing model with new case attributes

This scripts extends the simple model copying script by creating new case attributes from Case Table report
1. Clear the current contents of the target model

  • ProjectName ExampleProject => CHANGE THIS to be the project in which the target model exists.
  • ModelName ExampleModel => CHANGE THIS to be the name of the target model.

2. Select all events from another model defined with FilterId = 1234567. (note: change this Id to be an Id from the default Filter (previously called View) of the source model:

  • AnalysisType 6 => Event Table
  • MaximumCount 0 => Retrieve all events from the model
  • FilterId 1234567 => CHANGE THIS to be the Filter ID / FilterId of the source model from which you want to make a copy
  • SelectedEventAttributes * => Get all Event Attributes
  • TargetTable #AnalysisResult => Store the result into a temporary SQL table named #AnalysisResult
  • #GetAnalysis => Execute the query (stores the result into #AnalysisResult)

3. Import all events to the specified model (#ImportEvents)

  • ProjectName ExampleProject => CHANGE THIS to be the project in which the target model exists.
  • ModelName ExampleModel => CHANGE THIS to be the name of the target model.

4. Select all case attributes from the source model (defined with FilterId = 1234567)

  • AnalysisType 5 => Case Table
  • MaximumCount 0 => Retrieve all cases from the model
  • FilterId 1234567 => CHANGE THIS to be the Filter ID / FilterId of the source model from which you want to copy case attributes
  • SelectedCaseAttributes * => Get all Case Attributes
  • SelectedActivityCounts * => Get the counts for all EventTypes
  • SelectedEventAttributes * => Get the values for all EventAttributes consolidated to Case level
  • ShowDurations 255 => Retrieve all duration columns
  • ShowStarts 7 => Retrieve all Starts columns
  • ShowEnds 7 => Retrieve all Starts columns
  • ShowVariationIds => Retrive the Variation ID for each case
  • ShowVariationPaths => Retrieve the ordered list of Event Type Strings for each case
  • TargetTable #AnalysisResult2 => Store the result into a new temporary SQL table named #AnalysisResult2
  • #GetAnalysis => Execute the query (stores the result into #AnalysisResult)

5. Import all case attributes to the specified model (#ImportCaseAttributes)

  • ProjectName ExampleProject => CHANGE THIS to be the project in which the target model exists.
  • ModelName ExampleModel => CHANGE THIS to be the name of the target model.
(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'ModelName', 'ExampleModel')
--#RemoveEvents

(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'FilterId', '1234567') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis

(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'ModelName', 'ExampleModel')
(SELECT * FROM [#AnalysisResult])
--#ImportEvents

(SELECT 'AnalysisType', '5') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'FilterId', '1234567') UNION ALL
(SELECT 'SelectedCaseAttributes', '*') UNION ALL
(SELECT 'SelectedActivityCounts', '*') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'ShowDurations', '255') UNION ALL
(SELECT 'ShowStarts', '7') UNION ALL
(SELECT 'ShowEnds', '7') UNION ALL
(SELECT 'ShowVariationIds', 'True') UNION ALL
(SELECT 'ShowVariationPaths', 'True') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult2')
--#GetAnalysis

(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'ModelName', 'ExampleModel')
(SELECT * FROM [#AnalysisResult2])
--#ImportCaseAttributes

Create a copy of events and switch the ABPD dimension from original EventType to Event Attribute 'Organization'

The following script will:
1. Clear the current contents of the target model

  • ProjectName ExampleProject => CHANGE THIS to be the project in which the target model exists.
  • ModelName ExampleModel => CHANGE THIS to be the name of the target model.

2. Select all events from another model defined with FilterId = 1234567. (note: change this Id to be an Id from the default Filter (previously called View) of the source model:

  • AnalysisType 6 => Event Table
  • MaximumCount 0 => Retrieve all events from the model
  • FilterId 1234567 => CHANGE THIS to be the Filter ID / FilterId of the source model from which you want to make a copy
  • SelectedEventAttributes * => Get all Event Attributes
  • TargetTable #AnalysisResult => Store the result into a temporary SQL table named #AnalysisResult
  • #GetAnalysis => Execute the query (stores the result into #AnalysisResult)

3. Import all events to the specified model (#ImportEvents)

  • ProjectName ExampleProject => CHANGE THIS to be the project in which the target model exists.
  • ModelName ExampleModel => CHANGE THIS to be the name of the target model.
  • SELECT [Case], [Organization], [Start Time], * FROM [#AnalysisResult] => Import the event by selecting the column [Organization] as the Event Type. Note: CHANGE this column to the desired dimension for ABPD analysis
(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'ModelName', 'ExampleModel')
--#RemoveEvents

(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'FilterId', '1234567') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis

(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'ModelName', 'ExampleModel')
(SELECT [Case], [Organization], [Start Time], * FROM [#AnalysisResult])
--#ImportEvents

Create a copy of events and combine some event types to a new event type

The following script will:
1. Clear the current contents of the target model

  • ProjectName ExampleProject => CHANGE THIS to be the project in which the target model exists.
  • ModelName ExampleModel => CHANGE THIS to be the name of the target model.

2. Select all events from another model defined with FilterId = 1234567. (note: change this Id to be an Id from the default Filter (previously called View) of the source model:

  • AnalysisType 6 => Event Table
  • MaximumCount 0 => Retrieve all events from the model
  • FilterId 1234567 => CHANGE THIS to be the Filter ID / FilterId of the source model from which you want to make a copy
  • SelectedEventAttributes * => Get all Event Attributes
  • TargetTable #AnalysisResult => Store the result into a temporary SQL table named #AnalysisResult
  • #GetAnalysis => Execute the query (stores the result into #AnalysisResult)

3. Import all events to the specified model (#ImportEvents)

  • ProjectName ExampleProject => CHANGE THIS to be the project in which the target model exists.
  • ModelName ExampleModel => CHANGE THIS to be the name of the target model.
  • SELECT [Case], 'COMBINED', [Start Time] FROM [#AnalysisResult] WHERE [Event Type] IN ('Sales Order', 'Shipment')

=> Select those event type that should be combined and use the new EventType name for them
=> This example replaces 'Sales Order' and 'Shipment' with new EventType name 'COMBINED'

  • UNION ALL => Use this statement to build the events from several sub queries
  • SELECT [Case], [Event Type], [Start Time] FROM [#AnalysisResult] WHERE [Event Type] NOT IN ( 'Sales Order', 'Shipment')

=> Select all other Event Types and include them as they are, ie. no change to these events

(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'ModelName', 'ExampleModel')
--#RemoveEvents

(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'FilterId', '1234567') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis

(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'ModelName', 'ExampleModel')
(SELECT [Case], 'COMBINED', [Start Time], * FROM [#AnalysisResult] WHERE [Event Type] IN ('Sales Order', 'Shipment')) UNION ALL
(SELECT [Case], [Event Type], [Start Time], * FROM [#AnalysisResult] WHERE [Event Type] NOT IN ( 'Sales Order', 'Shipment'))
--#ImportEvents

Create a copy of events and define new event types based on old event types and an event attribute

The following script will:
1. Clear the current contents of the target model

  • ProjectName ExampleProject => CHANGE THIS to be the project in which the target model exists.
  • ModelName ExampleModel => CHANGE THIS to be the name of the target model.

2. Select all events from another model defined with FilterId = 1234567. (note: change this Id to be an Id from the default Filter (previously called View) of the source model:

  • AnalysisType 6 => Event Table
  • MaximumCount 0 => Retrieve all events from the model
  • FilterId 1234567 => CHANGE THIS to be the Filter ID / FilterId of the source model from which you want to make a copy
  • SelectedEventAttributes * => Get all Event Attributes
  • TargetTable #AnalysisResult => Store the result into a temporary SQL table named #AnalysisResult
  • #GetAnalysis => Execute the query (stores the result into #AnalysisResult)

3. Import all events to the specified model (#ImportEvents)

  • ProjectName ExampleProject => CHANGE THIS to be the project in which the target model exists.
  • ModelName ExampleModel => CHANGE THIS to be the name of the target model.
  • SELECT [Case], convert(varchar(255),[Event Type]) + '_' + convert(varchar(255),[SAP_User]), [Start Time], * FROM [#AnalysisResult]

=> Create the new EventType by combining original Event Type, a string '_' and event attribute SAP_User

(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'ModelName', 'ExampleModel')
--#RemoveEvents

(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'FilterId', '1234567') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis

(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'ModelName', 'ExampleModel')
(SELECT [Case], convert(varchar(255),[Event Type]) + '_' + convert(varchar(255),[SAP_User]), [Start Time], * FROM [#AnalysisResult])
--#ImportEvents

Calculate the amount of events within same case with same timestamp

The following script will:
1.. Select all events from another model defined with FilterId = 1234567. (note: change this Id to be an Id from the default Filter (previously called View) of the source model:

  • AnalysisType 6 => Event Table
  • MaximumCount 0 => Retrieve all events from the model
  • FilterId 1234567 => CHANGE THIS to be the Filter ID / FilterId of the source model from which you want to make a copy
  • SelectedEventAttributes * => Get all Event Attributes
  • TargetTable #AnalysisResult => Store the result into a temporary SQL table named #AnalysisResult
  • #GetAnalysis => Execute the query (stores the result into #AnalysisResult)

2. Calculate the amount of events within same case with same timestamp SELECT count(*) FROM [#AnalysisResult] AS AR1, [#AnalysisResult] AS AR2 => Return the count(*) as the result of the query WHERE AR1.[Case] = AR2.[Case] AND AR1.[Event Type] <> AR2.[Event Type] AND AR1.[Start Time] = AR2.[Start Time] => Case & Start Time are same in both events, Event Type is different --#WriteLog => show the result to user


(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'FilterId', '10') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis

SELECT count(*) FROM [#AnalysisResult] AS AR1, [#AnalysisResult] AS AR2 
WHERE AR1.[Case] = AR2.[Case] AND AR1.[Event Type] <> AR2.[Event Type] AND AR1.[Start Time] = AR2.[Start Time]
--#WriteLog

Create a new model from operation log

The following script will read the operation log of QPR ProcessAnalyzer and create process model based on those log entries. Comments: - AnalysisType 12 => Operation Log - MaximumCount 10000 => Retrieve only the 10000 most recent operations - Import all events to the specified model (#ImportEvents)

  • ProjectName ExampleProject => CHANGE THIS to be the project in which the target model exists.
  • ModelName ExampleModel => CHANGE THIS to be the name of the target model.
(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'ModelName', 'ExampleModel')
--#RemoveEvents

(SELECT 'AnalysisType', '12') UNION ALL
(SELECT 'MaximumCount', '10000') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis

(SELECT 'ProjectName', 'ExampleProject') UNION ALL
(SELECT 'ModelName', 'ExampleModel')
(
  SELECT 
    [Session Id], 'InitializeConnection', DATEADD(ms, 3, [Start Time]), [Model Id], [Model Name],
    (CASE WHEN ([Message] LIKE 'Exception of type:%') THEN
	  'Exception'
	ELSE
	  NULL
	END) AS [Error], 
	[Message], [Additional Data]
  FROM 
    #AnalysisResult
  WHERE
    [Name] IN (
	  'SqlCore.InitializeConnection'
	)
)
UNION ALL
(
  SELECT 
    [Session Id], 'InitializeSessionManager', [Start Time], [Model Id], [Model Name],
    (CASE WHEN ([Message] LIKE 'Exception of type:%') THEN
	  'Exception'
	ELSE
	  NULL
	END) AS [Error], 
	[Message], [Additional Data]
  FROM 
    #AnalysisResult
  WHERE
    [Name] IN (
	  'SingletonSessionManager.InitializeSessionManager'
	)
)
UNION ALL
(
  SELECT 
    [Session Id], 'Start:' + [Name], [Start Time], [Model Id], [Model Name], NULL AS [Error], NULL AS [Message], [Additional Data]
  FROM 
    #AnalysisResult
  WHERE
    [Name] NOT IN (
	  'SingletonSessionManager.InitializeSessionManager',
	  'SqlCore.InitializeConnection',
	  'SqlCore.Dispose'
	)
)
UNION ALL
(
  SELECT 
    [Session Id], 'End:' + [Name], DATEADD(ms, 3, [End Time]), [Model Id], [Model Name],
    (CASE WHEN ([Message] LIKE 'Exception of type%') THEN
	  'Exception'
	ELSE
	  NULL
	END) AS [Error], 
	[Message], NULL AS [Additional Data]
  FROM 
    #AnalysisResult
  WHERE
    [End Time] IS NOT NULL
    AND [Name] NOT IN (
	  'SingletonSessionManager.InitializeSessionManager',
	  'SqlCore.InitializeConnection',
	  'SqlCore.Dispose'
	)
)
--#ImportEvents

SELECT 
  [Session Id], [User Id], [User Name]
FROM 
  #AnalysisResult
GROUP BY
  [Session Id], [User Id], [User Name]
HAVING
  [User Id] <> 0
--#ImportCaseAttributes