SQL Scripting for ETL: Difference between revisions

Revision as of 12:11, 8 April 2015

It is possible to load raw data into QPR ProcessAnalyzer and do the data transformation and loading into QPR ProcessAnalyzer Service via 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 script via the Script Manager dialog. The script consists of SQL statements and QPR ProcessAnalyzer commands that take the preceding SQL statements as parameters.

Supported QPR ProcessAnalyzer Commands in Scripts

--#Exit

Stops the execution of the script and gives a message to the user.

Parameters

The parameters defined in the SQL SELECT statements:

'Exit', '<Value>'

Defines whether to stop the script execution:

1 = stop execution of the current script and call the script defined by the RunsScriptId parameter if it is given.

0 = if a value for the RunScriptId parameter is given, pause the execution of the current script and call the given script, then resume running the current script after the given script ends. If a value for RunScriptId is not given, do not pause or stop execution of the current script.

'MessageText', '<Text>'

Text to be shown to the user after the script execution is finished if the script finished because of the Exit command, i.e. when Exit=1. The default value is "Script execution finished.", which is shown also when the script finished normally, i.e. when Exit=0. The text is also written to the script log.

'RunScriptId', '<Id>'

Optional. The Id of the script to be run. Can be empty. Note that the script can call itself, so be careful not to create a looping script.

'CatchOperationExceptions', '<Value>'

Optional. Defines whether to stop the script execution or to continue to run the script from the next statement if an exception occurs when running the script:

1 = don't stop execution of the script, continue running the script from the next statement.

0 = stop execution of the current script and show the exception.

The following script variables will be set and are shown in the script log:

@_ExceptionOccurred: if there was an exception, then this value is 1, otherwise 0. INT

@_ExceptionType: if there was an exception, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.

@_ExceptionMessage: if there was an exception, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.

@_ExceptionDetails: if there was an exception, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.

Example

The Exit command can in effect be used to "call" a script, i.e. run a different script and then return to continue the current script.

(SELECT 'Exit', '0') UNION ALL 
(SELECT 'RunScriptId', '12')   
--#Exit

Example

The Exit command can also be used to "goto" a script, i.e. stop the execution of the current script and run a different script.

(SELECT 'Exit', '1') UNION ALL  
(SELECT 'RunScriptId', '12')  
--#Exit

Example

The following example stops the script execution, gives a message, and runs a script with Id 12.

(SELECT 'Exit', '1') UNION ALL
(SELECT 'MessageText', 'Data from SAP not valid. Script execution will be terminated. Check source data. Running script Id 12.') UNION ALL
(SELECT 'RunScriptId', '12')
--#Exit

Example

The following example script fragment checks if the previous ProcessAnalyzer command had any exceptions, and if it did, will goto script with Id 2. If the previous command didn't have any exceptions, the script execution is stopped.

DECLARE @ScriptToRun VARCHAR(10)

IF @_ExceptionOccurred = 1 
 SET @ScriptToRun = '2' 
 ELSE SET @ScriptToRun  = ''

(SELECT 'Exit', '1') UNION ALL  
(SELECT 'RunScriptId', @ScriptToRun)  
 --#Exit

--#GetAnalysis

Creates an analysis from the data which the preceding SQL statements given as parameters provide.

Parameters

The analysis parameters have to be defined in the SQL SELECT statements as follows:

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

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

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

Flowchart Analysis (0)

Variation Analysis in the Chart Mode (1)

Path Analysis (3)

Event Type Analysis in the Chart Mode (4)

Case Analysis (5)

Event Analysis (6)

Event Type Analysis in the Table Mode (7)

Variation Analysis in the Table Mode (8)

Duration Analysis (9)

Profiling Analysis (10)

User Report (11)

Operation Log Analysis (12)

Flow Analysis (13)

Influence Analysis (14)

Data Table Analysis (18)

Model Report (21)

Project Report (22)

Data Table Report (23)

Script Report (24)

'TargetTable', '<#TableName>'

The temporary table to which the analysis is to be stored. Note that only table format analyses can be stored to a temporary table.

You can define the 'TargetTable' when using the following analysis types:

- Case Analysis

- Event Analysis

- Event Type Analysis

- Variation Analysis

- User Permissions

- Operation Log

- Flow Analysis

- Influence Analysis

- Integration Table

- Model Report

- Project Report

- Data Table Report

- Script Report

'Show', '<Value>'

Optional. If TRUE or 1, the analysis is opened after the script is run.

'Title', '<Value>'

Optional. The title for the Excel sheet created when Show is TRUE or 1. Default value is the name of the analysis type.

'SheetName', '<Value>'

Optional. The name of the Excel sheet created when Show is TRUE or 1. Default value is the name of the analysis type.