SQL Scripting for ETL

It is possible to load raw data into QPR ProcessAnalyzer and do the data transformation and loading into QPR ProcessAnalyzer Service via Load Scripts using temporary database tables, so that the resulting transformed data can be used for analyses in QPR ProcessAnalyzer.

A user with the ManageIntegrations and RunScripts permissions can define a Load Script in the Model Properties dialog. The Load Script consists of SQL statements and QPR ProcessAnalyzer commands that take the preceding SQL statements as parameters.

Supported QPR ProcessAnalyzer Commands in Load Scripts

--#GetAnalysis

Creates an analysis from the data which the preceding SQL statements given as parameters provide. The analysis parameters have to be defined in the SQL SELECT statements as follows:

SELECT '<Analysis Parameter>', '<Value>'

See Analysis Parameters for a list of supported analysis parameters in QPR ProcessAnalyzer.

The --#GetAnalysis command supports the following analysis types:

Case Analysis (5)

Event Analysis (6)

Event Type Analysis (7) Note: analysis is in table format only.

Variation Analysis (8) Note: analysis is in table format only.

User Report (11)

OperationLog Analysis (12)

Flow Analysis (13)

Influence Analysis (14)

Integration Table Analysis (18)

--#ImportEvents

Loads Events from the data which the preceding SQL statements given as parameters provide.

--#ImportCaseData

Loads Case Attributes from the data which the preceding SQL statements given as parameters provide.

--#RemoveEvents

Removes all events in the model, but retains Cases, Event Types, and Variations.

--#WriteLog

Displays a log after the script execution is completed.

SQL Command Support

When transforming data, only temporary tables (#) should be used. Note that global temporary tables (##) should never be used in the SQL scripts, and using them is not allowed.

Terminating Load Scripts

A Load Script can be terminated by the following ways:

• The user running the script can click the Cancel button when the script is running.
• A QPR ProcessAnalyzer Administrator user can terminate the script via the Operations Log.
• The SQL Server System Administrator can kill the session using the script by using e.g. SQL Server Management Studio.
• The Internet Information Services Administrator can recycle the application pool if the Load Script has caused it to hang. Note however, that this may also cause other requests by other users being processed at the same time to be aborted.
• The Windows Administrator can kill the w3wp.exe-process processing a problematic script. Note however, that this may also cause other requests by other users being processed at the same time to be aborted.

NOTE!
Terminating the Load Script will not revert any changes the script has already done in the database before the Cancel button is clicked.

Example Load Script: Create a new model from operation log

The following Load 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

--#RemoveEvents

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

(
  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
--#ImportCaseData