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 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.

Example Load Script

The following Load Script will generate an operation log of 10000 latest 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