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

'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 following example will get an Event analysis, open that analysis with "Analysis Title" as the title on a sheet named "Example Sheet Name", and store the Event analysis results to the "#ExampleTable" data table.

(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'FilterId', '3') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'Show', '1') UNION ALL
(SELECT 'Title', 'Analysis Title') UNION ALL
(SELECT 'SheetName', 'Excel Sheet Name') UNION ALL
(SELECT 'TargetTable' '#ExampleTable')
--#GetAnalysis

Example

The following example will get a Model Report analysis, store the analysis results to a temporary table called "#ModelResult" and show the results on an Excel sheet. This table contains the same information as is visible in the Project Workspace. For explanations of the columns, see Models.

(SELECT 'AnalysisType', '21') UNION ALL
(SELECT 'Show', '1') UNION ALL
(SELECT 'TargetTable', '#ModelResult')
--#GetAnalysis

Example

The following example will get a Project Report analysis, store the analysis results to a temporary table called "#ProjectResult" and show the results on an Excel sheet. This table contains the same information as is visible in the Project Workspace. For explanations of the columns, see Models.

(SELECT 'AnalysisType', '22') UNION ALL
(SELECT 'Show', '1') UNION ALL
(SELECT 'TargetTable', '#ProjectResult')
--#GetAnalysis

Example

The following example will get a Data Table Report analysis, store the analysis results to a temporary table called "#DataTableResult" and show the results on an Excel sheet. This table contains the same information as is visible in the Project Workspace. For explanations of the columns, see Data Tables.

(SELECT 'AnalysisType', '23') UNION ALL
(SELECT 'Show', '1') UNION ALL
(SELECT 'TargetTable', '#DataTableResult')
--#GetAnalysis

Example

The following example will get a Script Report analysis, store the analysis results to a temporary table called "#ScriptResult" and show the results on an Excel sheet. This table contains the same information as is visible in the Script Manager. For explanations of the columns, see Script Manager.

(SELECT 'AnalysisType', '24') UNION ALL
(SELECT 'Show', '1') UNION ALL
(SELECT 'TargetTable', '#ScriptResult')
--#GetAnalysis

---

--#ImportCaseAttributes

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

Parameters for the First Query

The parameters defined in the SQL SELECT statements:

'ProjectId' or 'ProjectName', '<Value>'

The id or the name of the project in which the target model exists. Defaults to the current project. If the given ProjectName doesn't exist, a new project is created.

'ModelId' or 'ModelName', '<Value>'

The id or the name of the existing/new target model. Defaults to the current model. If ModelId is given, neither ProjectId nor ProjectName are used. If the given ModelName doesn't exist, a new model is created.

'Append', '<Value>'

Defines what to do with an existing target model case attributes. TRUE or any other Integer than "0" = the existing case attributes in the target model are not deleted before import, FALSE or "0" = the existing case attributes of the target model are deleted before the import. If the target model is set to use another model as the Case Attribute Model, those case attributes are not deleted. Not used when creating a new model. Default value is TRUE.

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

Parameters for the Second Query

''

The database query whose results are to be imported. Note that the geometry, geography, hierarchyid, and image SQL data types are not supported by the ImportCaseAttributes command.

---

--#ImportDataTable

Imports data to a Data Table.

Parameters

The parameters defined in the SQL SELECT statements in the order they are listed below:

'ProjectId' or 'ProjectName', '<Value>'

The id or the name of the project in which the target data table exists.

'DataTableId' or 'DataTableName', '<Value>'

The id or the name of the existing/new target data table.

'Append', '<Value>'

Defines what to do with an existing target data table contents. TRUE or any other Integer than "0" = the existing contents in the target data table are not deleted before import, FALSE or "0" = the existing contents of the target data table are deleted before the import. Not used when creating a new data table.

''

The database query whose results are to be imported. Note that if the query doesn't return any data, the data table is not created.

'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 following example will load data from a model and put it into the "AnalysisResult" table and then add that data to the "ExampleTable" data table, and catch exceptions.

SELECT 'START'

--#WriteLog

(SELECT 'AnalysisType', '6') UNION ALL

(SELECT 'MaximumCount', '0') UNION ALL

(SELECT 'FilterId', '3') UNION ALL

(SELECT 'SelectedEventAttributes', '*') UNION ALL

(SELECT 'TargetTable', '#AnalysisResult') UNION ALL

(SELECT 'CatchOperationExceptions', '1')

--#GetAnalysis

SELECT 'DataLoaded'

--#WriteLog

SELECT count(*) from [#AnalysisResult]

--#WriteLog

(SELECT 'ProjectId', '1') UNION ALL

(SELECT 'DataTableName', 'ExampleTable') UNION ALL

(SELECT 'Append', '1')

(SELECT * FROM [#AnalysisResult])

--#ImportDataTable

Example

The following example will load data from the "ExampleTable" data table in the "ExampleProject" project, put that data into the "CSV1" table and then show the contents of the "CSV" table. In effect, it shows the contents of the "ExampleTable" data table.

(SELECT 'AnalysisType', '18') UNION ALL

(SELECT 'ProjectName', 'ExampleProject') UNION ALL

(SELECT 'MaximumCount', '0') UNION ALL

(SELECT 'DataTableName', 'ExampleTable') UNION ALL

(SELECT 'TargetTable', '#CSV1')

--#GetAnalysis

(SELECT * FROM #CSV1)

(SELECT 'Title', 'CSV Table') UNION ALL

(SELECT 'MaximumCount', '0')

--#ShowReport

---

--#ImportEvents

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

Parameters for the First Query

The parameters defined in the SQL SELECT statements:

'ProjectId' or 'ProjectName', '<Value>'

The id or the name of the project in which the target model exists. Defaults to the current project. If the given ProjectName doesn't exist, a new project is created.

'ModelId' or 'ModelName', '<Value>'

The id or the name of the existing/new target model. Defaults to the current model. If ModelId is given, neither ProjectId nor ProjectName are used. If the given ModelName doesn't exist, a new model is created.

'Append', '<Value>'

Defines what to do with the existing target model events. TRUE or any other Integer than "0" = the existing events in the target model are not deleted before import, FALSE or "0" = the existing events of the target model are deleted before the import. Not used when creating a new model. Default value is TRUE.

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

Parameters for the Second Query

''

The database query whose results are to be imported. Note that the geometry, geography, hierarchyid, and image SQL data types are not supported by the ImportEvents command.

---

--#ImportOdbcQuery

Extracts data directly from the ODBC data source and imports it to QPR ProcessAnalyzer Data Table or QPR ProcessAnalyzer temporary table. Column names are parsed from the query result. If a column name contains illegal characters for table names, the illegal characters are converted to be underscore characters. Columns are extracted as text data.

Parameters

The parameters defined in the SQL SELECT statements in the order they are listed below:

Data Destination

'TargetTable', '<#TableName>'

The temporary table to which the data is to be imported. If not used, define the target using the ProjectId/ProjectName, DataTableId/DataTableName, and Append parameters described below.

'ProjectId' or 'ProjectName', '<Value>'

The id or the name of the project in which the target data table exists.

'DataTableId' or 'DataTableName', '<Value>'

The id or the name of the existing/new target data table.

'Append', '<Value>'

Defines what to do with an existing target data table contents. TRUE or any other Integer than "0" = the existing contents in the target data table are not deleted before import, FALSE or "0" = the existing contents of the target data table are deleted before the import. Not used when creating a new data table.

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

ODBC specific parameters

'OdbcConnectionString', '<Value>'

The ODBC driver connection string that includes the settings needed to establish the initial connection. Mandatory. See OdbcConnection.ConnectionString Property in Microsoft Development Network for more information on the possible connection strings. You can also configure a data source name for connecting to QPR ProcessAnalyzer, for instructions see How to Configure an ODBC Data Source Name for Connecting to QPR ProcessAnalyzer.

'OdbcQueryString', '<Value>'

The SQL query string. Mandatory. Note that if the query doesn't return any data, the target data table or temporary table is not created.

'ExecuteInClientSide', '<Value>'

Defines whether the command is executed in the client side or in the server side when using QPR ProcessAnalyzer Pro. This parameter is used when there is no server connection available, for example. TRUE or any other Integer than "0" = the import query is executed in the client side. FALSE or "0" = the import query is executed in the server side. Not used with QPR ProcessAnalyzer Xpress or QPR ProcessAnalyzer Database as they always execute the command in the client side. Supports only data table as the import destination. If 'TargetTable' has been defined as the import destination and the value of this parameter is given as TRUE or any other Integer than "0", you will receive an error message. Optional. Default value is FALSE.

Example

The following script extracts data from an ODBC using a data source name configured as described in the link above and selects all columns from the table PA_MODEL, and catches exceptions.

(SELECT 'OdbcConnectionString', 'DSN=PA_EXPRESS_40') UNION ALL

(SELECT 'OdbcQueryString', 'SELECT * FROM PA_MODEL') UNION ALL

(SELECT 'TargetTable', '#ImportOdbcTable') UNION ALL

(SELECT 'Append', '1') UNION ALL

(SELECT 'CatchOperationExceptions', '1')

--#ImportOdbcQuery

---

--#ImportOleDbQuery

Extracts data from an OLE DB source and imports it to QPR ProcessAnalyzer Data Table or QPR ProcessAnalyzer temporary table. Column names are parsed from the query result. It is possible to both create new Data Tables as well as modify existing Data Tables with this command.

Parameters

The parameters defined in the SQL SELECT statements in the order they are listed below:

Data Destination

'TargetTable', '<#TableName>'

The temporary table to which the data is to be imported. If not used, define the target using the ProjectId/ProjectName, DataTableId/DataTableName, and Append parameters described below.

'ProjectId' or 'ProjectName', '<Value>'

The id or the name of the project in which the target data table exists.

'DataTableId' or 'DataTableName', '<Value>'

The id or the name of the existing/new target data table.

'Append', '<Value>'

Defines what to do with an existing target data table contents. TRUE or any other Integer than "0" = the existing contents in the target data table are not deleted before import, FALSE or "0" = the existing contents of the target data table are deleted before the import. Not used when creating a new data table.

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

OLE DB Query Parameters

'OleDbConnectionString', '<Value>'

The OLE DB connection string that includes the settings needed to establish the initial connection. Mandatory. See OleDbConnection.ConnectionString Property in Microsoft Development Network for more information on the possible connection strings.

'OleDbQueryString', '<Value>'

The SQL query string. Mandatory. Note that if the query doesn't return any data, the target data table or temporary table is not created.

'ExecuteInClientSide', '<Value>'

Defines whether the command is executed in the client side or in the server side when using QPR ProcessAnalyzer Pro. This parameter is used when there is no server connection available, for example. TRUE or any other Integer than "0" = the import query is executed in the client side. FALSE or "0" = the import query is executed in the server side. Not used with QPR ProcessAnalyzer Xpress or QPR ProcessAnalyzer Database as they always execute the command in the client side. Supports only data table as the import destination. If 'TargetTable' has been defined as the import destination and the value of this parameter is given as TRUE or any other Integer than "0", you will receive an error message. Optional. Default value is FALSE.

Example

The following example will load data from an OLE DB source (a sample database called DB1) and selects all columns from the table EXAMPLE. It will then put that data into the "#TABLE" temporary table and then show the contents of that table, and catch exceptions.

(SELECT 'OleDbConnectionString', 'Provider=SQLOLEDB;Data Source=(local);Initial Catalog=DB1;Integrated Security=SSPI;') UNION ALL

(SELECT 'OleDbQueryString', 'SELECT * FROM EXAMPLE') UNION ALL

(SELECT 'TargetTable', '#TABLE') UNION ALL

(SELECT 'Append', '1') UNION ALL

(SELECT 'CatchOperationExceptions', '1')

--#ImportOleDbQuery

(SELECT * FROM #TABLE) UNION ALL

(SELECT 'MaximumCount', '0') UNION ALL

(SELECT 'CatchOperationExceptions', '1')

--#ShowReport

Example

The following example will load all the columns from "EXAMPLE" table from an OLE DB source and will put that data into the "ExampleDataTable" in the "ExampleProject" project. It will then get the Data Table analysis from the "ExampleDataTable", put that into the "#TABLE" temporary table and then show the contents of that table.

(SELECT 'ProjectName', 'ExampleProject') UNION ALL

(SELECT 'DataTableName', 'ExampleDataTable') UNION ALL

(SELECT 'Append', '0') UNION ALL

(SELECT 'OleDbConnectionString', 'Provider=SQLOLEDB;Data Source=(local);Initial Catalog=DB1;Integrated Security=SSPI;') UNION ALL

(SELECT 'OleDbQueryString', 'SELECT * FROM EXAMPLE')

--#ImportOleDbQuery

(SELECT 'AnalysisType', '18') UNION ALL

(SELECT 'ProjectName', 'ExampleProject') UNION ALL

(SELECT 'MaximumCount', '0') UNION ALL

(SELECT 'DataTableName', 'ExampleDataTable') UNION ALL

(SELECT 'TargetTable', '#TABLE')

--#GetAnalysis

(SELECT * FROM #TABLE)

(SELECT 'MaximumCount', '0')

--#ShowReport

---

--#ImportSalesforceQuery

Extracts data from Salesforce cloud and imports it into a data table as NVARCHAR(MAX) or SQL_VARIANT type data. Note that this command requires the Salesforce username and password to be visible in the script!

Parameters

The parameters defined in the SQL SELECT statements in the order they are listed below:

Data Destination

'TargetTable', '<#TableName>'

The temporary table to which the data is to be imported. If not used, define the target using the ProjectId/ProjectName, DataTableId/DataTableName, and Append parameters described below.

'ProjectId' or 'ProjectName', '<Value>'

The id or the name of the project in which the target data table exists.

'DataTableId' or 'DataTableName', '<Value>'

The id or the name of the existing/new target data table.

'Append', '<Value>'

Defines what to do with an existing target data table contents. TRUE or any other Integer than "0" = the existing contents in the target data table are not deleted before import, FALSE or "0" = the existing contents of the target data table are deleted before the import. Not used when creating a new data table.

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

Salesforce Query Parameters

'SalesforceUser', '<Value>'

Username for the Salesforce cloud.

'SalesforcePW', '<Value>'

Password for the Salesforce cloud.

'SalesforceQueryMode', '<Value>'

Optional. The Salesforce query function to be used. 1 (default) = queryall(), 2 = query(), 3 = describeSObject().

'SalesforceQuery', '<Value>'

The query to be run in the Salesforce cloud. Note that "*" cannot be used in the query. See Salesforce API and SOQL Reference for more information. Note that if the query doesn't return any data, the target data table or temporary table is not created.

'SalesforceQueryRetries', '<Value>'

Optional. Number of retries to attempt if the Salesforce query doesn't succeed. Default value is 3.

'SalesforceQueryRetryWait', '<Value>'

Optional. Number of milliseconds to wait between query retries. Default is 3000 ms.

'SalesforceBatchSize', '<Value>'

Optional. The number of rows of data the query returns in one batch. Minimum = 200, Maximum = 2000, Default = 500. See Salesforce API for more information.

Example

The following example will load date data from the "Contact" table in the Salesforce cloud and put that data into the "ExampleDataTable" data table in the "ExampleProject" project, and catch exception when loading the data. It will then get the Data Table analysis from the "ExampleDataTable", put that into the "#TABLE" temporary table and then show the contents of that table.

(SELECT 'ProjectName', 'ExampleProject') UNION ALL

(SELECT 'DataTableName', 'ExampleDataTable') UNION ALL

(SELECT 'Append', '0') UNION ALL

(SELECT 'SalesforceUser', 'example.user@qpr.com') UNION ALL

(SELECT 'SalesforcePW', 'examplepassword') UNION ALL

(SELECT 'SalesforceQuery', 'SELECT CreatedDate FROM Contact') UNION ALL

(SELECT 'CatchOperationExceptions', '1')

--#ImportSalesforceQuery

(SELECT 'AnalysisType', '18') UNION ALL

(SELECT 'ProjectName', 'ExampleProject') UNION ALL

(SELECT 'MaximumCount', '0') UNION ALL

(SELECT 'DataTableName', 'ExampleDataTable') UNION ALL

(SELECT 'TargetTable', '#TABLE')

--#GetAnalysis

(SELECT * FROM #TABLE)

(SELECT 'MaximumCount', '0')

--#ShowReport

---

--#ImportSapQuery

Extracts data from SAP and imports it to QPR ProcessAnalyzer Data Table or QPR ProcessAnalyzer temporary table. Column names are parsed from the query result. If a column name contains illegal characters for table names, the illegal characters are converted to be underscore characters, e.g. "sap:Owner" -> "sap_Owner". Columns are extracted as text data. Note that using this command requires some dlls not provided by QPR Software.

Parameters

The parameters defined in the SQL SELECT statements in the order they are listed below.

Destination Parameters:

'TargetTable', '<#TableName>'

If this parameter is given, store the results into a temporary SQL table in ETL sandbox.

If the TargetTable parameter is not given, use the following destination parameters:

'ProjectId' or 'ProjectName', '<Value>'

The id or the name of the project in which the target data table exists.

'DataTableId' or 'DataTableName', '<Value>'

The id or the name of the existing/new target data table.

'Append', '<Value>'

Defines what to do with an existing target data table contents. TRUE or any other Integer than "0" = the existing contents in the target data table are not deleted before import, FALSE or "0" = the existing contents of the target data table are deleted before the import. Not used when creating a new data table.

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

SAP Connection Parameters:

'SapUser', '<Username>'

SAP user used to connect to SAP. Mandatory. Corresponds to the "USER" constant on SAP side. See the SAP .NET Connector documentation for more info.

'SapPW', '<Password>'

Password of the SAP user used to connect to SAP. Mandatory. Corresponds to the "PASSWD" constant on SAP side. See the SAP .NET Connector documentation for more info.

'SapClient', '<Value>'

The SAP backend client. Mandatory. Corresponds to the "CLIENT" constant on SAP side. See the SAP .NET Connector documentation for more info.

'SapAppServerHost', '<Hostname or IP>'

The hostname or IP of the specific SAP application server, to which all connections shall be opened. Mandatory if SapMessageServerHost is not defined. Corresponds to the "ASHOST" constant on SAP side. See the SAP .NET Connector documentation for more info.

'SapMessageServerHost', '<Hostname or IP>'

The hostname or IP of the SAP system’s message server (central instance). Mandatory if SapAppServerHost is not defined. Corresponds to the "MSHOST" constant on SAP side. See the SAP .NET Connector documentation for more info.

'SapSystemNumber', '<Value>'

The SAP system’s system number. Mandatory if SapSystemID is not defined. Corresponds to the "SYSNR" constant on SAP side. See the SAP .NET Connector documentation for more info.

'SapSystemID', '<Value>'

The SAP system’s three-letter system ID. Mandatory if SapSystemNumber is not defined. Corresponds to the "SYSID" constant on SAP side. See the SAP .NET Connector documentation for more info.

'ExecuteInClientSide', '<Value>'

Defines whether the command is executed in the client side or in the server side when using QPR ProcessAnalyzer Pro. This parameter is used when there is no server connection available, for example. TRUE or any other Integer than "0" = the import query is executed in the client side. FALSE or "0" = the import query is executed in the server side. Not used with QPR ProcessAnalyzer Xpress or QPR ProcessAnalyzer Database as they always execute the command in the client side. Supports only data table as the import destination. If 'TargetTable' has been defined as the import destination and the value of this parameter is given as TRUE or any other Integer than "0", you will receive an error message. Optional. Default value is FALSE.

Other SAP Parameters:

'SapLanguage', '<Value>'

SAP language used. Default value is "EN". Optional. Corresponds to the "LANG" constant on SAP side. See the SAP .NET Connector documentation for more info.

'SapPoolSize', '<Number>'

The maximum number of RFC connections that this destination will keep in its pool. Default value is "5". Optional. Corresponds to the "POOL_SIZE" constant on SAP side. See the SAP .NET Connector documentation for more info.

'SapPeakConnectionsLimit', '<Value>'

In order to prevent an unlimited number of connections to be opened, you can use this parameter. Default value is "10". Optional. Corresponds to the "MAX_POOL_SIZE" constant on SAP side. See the SAP .NET Connector documentation for more info.

'SapConnectionIdleTimeout', '<Value>'

If a connection has been idle for more than SapIdleTimeout seconds, it will be closed and removed from the connection pool upon checking for idle connections or pools. Default value is "600". Optional. Corresponds to the "IDLE_TIMEOUT" constant on SAP side. See the SAP .NET Connector documentation for more info.

'SapRouter', '<List>'

A list of host names and service names / port numbers for the SAPRouter in the following format: /H/hostname/S/portnumber. Optional. Corresponds to the "SAPROUTER" constant on SAP side. See the SAP .NET Connector documentation for more info.

'SapLogonGroup', '<Value>'

The logon group from which the message server shall select an application server. Optional. Corresponds to the "GROUP" constant on SAP side. See the SAP .NET Connector documentation for more info.

'SapQueryMode', '<Number>'

If this is set to "1", then the query result will have the SAP Table field names as data table column names and actual data rows as rows. If this is set to "3", the query result will get the field descriptions from the SAP query using NO_DATA parameter, i.e. the returned columns are the following (in this order): Field, Type, Description, Length, Offset. Default value is "1". Optional. See the SAP .NET Connector documentation for more info.

'SapQueryTable', '<Name>'

Name of the SAP table to be extracted. Specifies the value for the parameter QUERY_TABLE in tab: 'Import' or function module 'rfc_read_table' in SAP. Mandatory. See the SAP .NET Connector documentation for more info. Note that if the query doesn't return any data, the target data table or temporary table is not created.

'SapRowcount', '<Value>'

The maximum amount of rows to fetch. Specifies the value for parameter ROWCOUNT in tab: 'Import' or function module 'rfc_read_table' in SAP. Optional. See the SAP .NET Connector documentation for more info.

'SapRowskips', '<Value>'

The amount of rows to skip. Specifies the value for parameter ROWSKIPS in tab: 'Import' or function module 'rfc_read_table'. in SAP. Optional. See the SAP .NET Connector documentation for more info.

'SapWhereClause', '<List>'

A comma separated list of WHERE clause elements passed for the SapQueryTable. Can be used with or without the SapWhereClauseSelect parameter. If used together with the SapWhereClauseSelect parameter, use the SapWhereClause parameter first. NOTE: The default maximum length for the Where Clause string is 72 characters in SAP, so the recommended maximum length of the SapWhereClause value is also 72 characters. In effect, specifies the value for parameter OPTIONS in tab: 'Import' or function module 'rfc_read_table' in SAP. Optional. See the SAP .NET Connector documentation for more info.

'SapWhereClauseSelect', '<SELECT query to be executed>'

The SELECT query to be executed in QPR ProcessAnalyzer sandbox. Used with or without the SapWhereClause parameter to pass WHERE clauses to SapQueryTable. If used together with the SapWhereClause parameter, use the SapWhereClause parameter first. The query is expected to return a table with at least one column, as the contents from the rows in the first column of the table are concatenated together to form the WHERE clause in SAP RFC_ReadTable. Therefore, it's recommended to first create the table with the WHERE clauses into a temporary table. In addition, it's recommended to have an order number column in the table and use that in the SELECT query to make sure the WHERE clause elements are concatenated in the correct order. The default maximum length for Where Clause string is 72 characters in SAP, so the recommended maximum length for the WHERE clause string in each row of the table is also 72. In effect, specifies the value for parameter OPTIONS in tab: 'Import' or function module 'rfc_read_table' in SAP. Optional. The contents up to the first 10 rows in the first column of the SELECT query are shown in the QPR ProcessAnalyzer Script Log. See the SAP .NET Connector documentation for more info.

'SapFieldNames', '<List>'

A comma separated list of field names for columns to be imported. Default value is empty, resulting in all columns being imported. Specifies the value for parameter FIELDNAME in tab: 'Tables' for table 'FIELDS' for function module 'rfc_read_table' in SAP. Optional. See the SAP .NET Connector documentation for more info.

'SapFunction', '<Value>'

If you define a value for this parameter, then the new value specifies the SAP function that is called inside the #ImportSapQuery command. Optional. The default value is RFC_READ_TABLE. Another possible value is BBP_RFC_READ_TABLE. See the SAP .NET Connector documentation for more info.

Example

The following script will get the "VBELN", "ERDAT", "ERZET", "ERNAM", "NETWR", and "WAERK" columns from the "VBAK" table in a SAP system and put them into a data table named "SapQueryTableExample", and catch exceptions when getting the data.

(SELECT 'ProjectName', 'ImportSapQueryExample') UNION ALL

(SELECT 'DataTableName', 'SapQueryTableExample') UNION ALL

(SELECT 'Append', 'TRUE') UNION ALL

(SELECT 'SapUser', 'exampleuser') UNION ALL

(SELECT 'SapPW', 'examplepassword') UNION ALL

(SELECT 'SapClient', '200') UNION ALL

(SELECT 'SapAppServerHost', '127.0.0.1') UNION ALL

(SELECT 'SapSystemNumber', '10') UNION ALL

(SELECT 'SapLanguage', 'EN') UNION ALL

(SELECT 'SapPoolSize', '5') UNION ALL

(SELECT 'SapPeakConnectionsLimit', '10') UNION ALL

(SELECT 'SapConnectionIdleTimeout', '600') UNION ALL

(SELECT 'SapRouter', '/H/127.0.0.1/A/1234/H/') UNION ALL

(SELECT 'SapLogonGroup', 'GROUPXNAME') UNION ALL

(SELECT 'SapQueryMode', '1') UNION ALL

(SELECT 'SapQueryTable', 'VBAK') UNION ALL

(SELECT 'SapDelimiter', '|') UNION ALL

(SELECT 'SapRowcount', '0') UNION ALL

(SELECT 'SapRowskips', '0') UNION ALL

(SELECT 'SapWhereClause', 'VBELN EQ `0060000039`, OR VBELN EQ `0060000040`') UNION ALL

(SELECT 'SapFieldNames', 'VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK') UNION ALL

(SELECT 'CatchOperationExceptions', '1')

--#ImportSapQuery

Example

The following script will extract values for the VBELN field from the VBAK table where the value of the VBELN field is between 0060000039` and `0060000041. It will also catch possible exceptions when getting the data and print out them on a separate sheet. The extracted data is also shown on its own sheet:

(SELECT 'CatchOperationExceptions', '1') UNION ALL

(SELECT 'SapAppServerHost', '127.0.0.1') UNION ALL

(SELECT 'SapSystemNumber', '10') UNION ALL

(SELECT 'SapUser', 'qpr') UNION ALL

(SELECT 'SapPW', 'demo') UNION ALL

(SELECT 'SapRouter', ) UNION ALL

(SELECT 'SapClient', '200') UNION ALL

(SELECT 'SapLanguage', 'EN') UNION ALL

(SELECT 'SapPoolSize', '5') UNION ALL

(SELECT 'SapPeakConnectionsLimit', '10') UNION ALL

(SELECT 'SapConnectionIdleTimeout', '600') UNION ALL

(SELECT 'ExecuteInClientSide', '1') UNION ALL

(SELECT 'TargetTable', '#SAPmode1') UNION ALL

(SELECT 'Append', '0') UNION ALL

(SELECT 'SapWhereClause', 'VBELN BETWEEN `0060000039` AND `0060000041`') UNION ALL

(SELECT 'SapQueryTable', 'VBAK')

--#ImportSapQuery

DECLARE @_SuccessOrNot as NVARCHAR(MAX);

SET @_SuccessOrNot = CASE @_ExceptionOccurred

WHEN 1 THEN

'Exception(s) occurred!'

ELSE

'SAP import OK.'

END

SELECT

@_SuccessOrNot as Result,

@_ExceptionOccurred as ExceptionOccurred,

@_ExceptionType as ExceptionType,

@_ExceptionMessage as ExceptionMessage,

@_ExceptionDetails as ExceptionDetails

(SELECT 'SheetName' , 'ExceptionData')

--#ShowReport

(SELECT * FROM #SAPmode1)

(SELECT 'SheetName' , 'SAPmode1')

--#ShowReport

Example

The following script will return the values for the VBELN, ERDAT, ERZET, ERNAM, NETWR, and WAERK fields from the VBAK table where the value of the VBELN field is between 0060000039 and 0060000041:

/* First, create the temporary table that holds the WHERE clause. */

CREATE TABLE #SapWhereClauseTable (sap_select_string varchar(255), order_number int)

INSERT INTO #SapWhereClauseTable SELECT 'VBELN BETWEEN ''0060000039''', 1

INSERT INTO #SapWhereClauseTable SELECT 'AND ''0060000041''', 2

/* Specify the target for the data that the script extracts from SAP */

(SELECT 'TargetTable', '#SAPmode1') UNION ALL

(SELECT 'Append', '0') UNION ALL

/* Define the SAP connection parameters */

(SELECT 'SapAppServerHost', '127.0.0.1') UNION ALL

(SELECT 'SapSystemNumber', '10') UNION ALL

(SELECT 'SapUser', 'exampleuser') UNION ALL

(SELECT 'SapPW', 'examplepassword') UNION ALL

(SELECT 'SapClient', '200') UNION ALL

(SELECT 'SapLanguage', 'EN') UNION ALL

(SELECT 'SapPoolSize', '5') UNION ALL

(SELECT 'SapPeakConnectionsLimit', '10') UNION ALL

(SELECT 'SapConnectionIdleTimeout', '600') UNION ALL

(SELECT 'SapFieldNames', 'VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK') UNION ALL

/* Use the WHERE clause defined in the temporary table */

(SELECT 'SapWhereClauseSelect', 'SELECT * from #SapWhereClauseTable ORDER BY order_number') UNION ALL

(SELECT 'SapQueryTable', 'VBAK')

--#ImportSapQuery

/* Show the results */

(SELECT * FROM #SAPmode1)

(SELECT 'SheetName' , 'SAPmode1')

--#ShowReport

Example

The following script will get the "VBELN", "ERDAT", "ERZET", "ERNAM", "NETWR", and "WAERK" columns from the "VBAK" table where the value of the VBELN field is between 0060000039 and 0060000041 and put them into a data table named "SapQueryTableExample". The query is made on the client side.

/* First, create the temporary table that holds the WHERE clause.*/

CREATE TABLE #SapWhereClauseTable (sap_select_string varchar(255), order_number int)

INSERT INTO #SapWhereClauseTable SELECT 'VBELN BETWEEN 0060000039', 1

INSERT INTO #SapWhereClauseTable SELECT 'AND 0060000042', 2

/* Define that the command is executed in the client side.*/

(SELECT 'ExecuteInClientSide', 'True') UNION ALL

/* Specify the data table where the data is imported into.*/

(SELECT 'DataTableName', 'SapQueryTableExample') UNION ALL

(SELECT 'Append', '0') UNION ALL

/* Define the SAP connection parameters.*/

(SELECT 'SapAppServerHost', '127.0.0.1') UNION ALL

(SELECT 'SapSystemNumber', '10') UNION ALL

(SELECT 'SapUser', 'exampleuser') UNION ALL

(SELECT 'SapPW', 'examplepassword') UNION ALL

(SELECT 'SapRouter', ) UNION ALL

(SELECT 'SapClient', '200') UNION ALL

(SELECT 'SapLanguage', 'EN') UNION ALL

(SELECT 'SapPoolSize', '5') UNION ALL

(SELECT 'SapPeakConnectionsLimit', '10') UNION ALL

(SELECT 'SapConnectionIdleTimeout', '600') UNION ALL

(SELECT 'SapFieldNames', 'VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK') UNION ALL

/* Use the WHERE clause defined in the temporary table.*/

(SELECT 'SapWhereClauseSelect', 'SELECT sap_select_string from #SapWhereClauseTable ORDER BY order_number') UNION ALL

(SELECT 'SapQueryTable', 'VBAK')

--#ImportSapQuery

/* Create an analysis.*/

(SELECT 'AnalysisType', '18') UNION ALL

(SELECT 'DataTableName', 'SapQueryTableExample') UNION ALL

(SELECT 'TargetTable', '#Result')

--#GetAnalysis

/* Show the results.*/

SELECT * FROM #Result

--#ShowReport

---

--#ImportSqlQuery

Extracts data from an ADO.NET source (which in this case is the SQL Server database) and imports it to QPR ProcessAnalyzer Data Table or QPR ProcessAnalyzer temporary table. Column names are parsed from the query result. It is possible to both create new Data Tables as well as modify existing Data Tables with this command.

Parameters

The parameters defined in the SQL SELECT statements in the order they are listed below:

Data Destination

'TargetTable', '<#TableName>'

The temporary table to which the data is to be imported. If not used, define the target using the ProjectId/ProjectName, DataTableId/DataTableName, and Append parameters described below.

'ProjectId' or 'ProjectName', '<Value>'

The id or the name of the project in which the target data table exists.

'DataTableId' or 'DataTableName', '<Value>'

The id or the name of the existing/new target data table.

'Append', '<Value>'

Defines what to do with an existing target data table contents. TRUE or any other Integer than "0" = the existing contents in the target data table are not deleted before import, FALSE or "0" = the existing contents of the target data table are deleted before the import. Not used when creating a new data table.

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

SQL Query Parameters

'SqlConnectionString', '<Value>'

The SQL connection string that includes the settings needed to establish the initial connection. Mandatory. See SqlConnection.ConnectionString Property in Microsoft Development Network for more information on the connection parameters.

'SqlQueryString', '<Value>'

The SQL query string. Mandatory. Note that if the query doesn't return any data, the target data table or temporary table is not created.

'ExecuteInClientSide', '<Value>'

Defines whether the command is executed in the client side or in the server side when using QPR ProcessAnalyzer Pro. This parameter is used when there is no server connection available, for example. TRUE or any other Integer than "0" = the import query is executed in the client side. FALSE or "0" = the import query is executed in the server side. Not used with QPR ProcessAnalyzer Xpress or QPR ProcessAnalyzer Database as they always execute the command in the client side. Supports only data table as the import destination. If 'TargetTable' has been defined as the import destination and the value of this parameter is given as TRUE or any other Integer than "0", you will receive an error message. Optional. Default value is FALSE.

Example

The following example will load data from an ADO.NET source (a sample database called DB1 on Microsoft SQL Server) and select all columns from the table EXAMPLE. It will then put that data into the "#TABLE" temporary table, catch exceptions when doing this, and then show the contents of that table.

(SELECT 'SqlConnectionString', 'Data Source=(local);Initial Catalog=DB1;Integrated Security=true') UNION ALL

(SELECT 'SqlQueryString', 'SELECT * FROM EXAMPLE') UNION ALL

(SELECT 'TargetTable', '#TABLE') UNION ALL

(SELECT 'Append', '1') UNION ALL

(SELECT 'CatchOperationExceptions', '1')

--#ImportSqlQuery

(SELECT * FROM #TABLE)

(SELECT 'MaximumCount', '0')

--#ShowReport

Example

The following example will load all the columns from "EXAMPLE" table in SQL Server database and will put that data into the "ExampleDataTable" in the "ExampleProject" project. It will then get the Data Table analysis from the "ExampleDataTable", put that into the "#TABLE" temporary table and then show the contents of that table.

(SELECT 'ProjectName', 'ExampleProject') UNION ALL

(SELECT 'DataTableName', 'ExampleDataTable') UNION ALL

(SELECT 'Append', '0') UNION ALL

(SELECT 'SqlConnectionString', 'Data Source=(local);Initial Catalog=DB1;Integrated Security=true') UNION ALL

(SELECT 'SqlQueryString', 'SELECT * FROM EXAMPLE')

--#ImportSqlQuery

(SELECT 'AnalysisType', '18') UNION ALL

(SELECT 'ProjectName', 'ExampleProject') UNION ALL

(SELECT 'MaximumCount', '0') UNION ALL

(SELECT 'DataTableName', 'ExampleDataTable') UNION ALL

(SELECT 'TargetTable', '#TABLE')

--#GetAnalysis

(SELECT * FROM #TABLE)

(SELECT 'MaximumCount', '0')

--#ShowReport

--#RemoveEvents

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

Parameters

The parameters defined in the SQL SELECT statements:

'ProjectId' or 'ProjectName', '<Value>'

The id or the name of the project in which the target model exists. Defaults to the current project.

'ModelId' or 'ModelName', '<Value>'

The id or the name of the target model. Defaults to the current model. If ModelId is given, neither ProjectId nor ProjectName are used.

'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 following example will remove all events in the model with Id "22931" in the project with Id "234".

(SELECT 'ProjectId', '234') UNION ALL

(SELECT 'ModelId', '22931')

--#RemoveEvents

---

--#SendEmail

Sends an e-mail and writes a message to script log whether sending the email was successful or not. Script execution continues even when the sending isn't successful.

Parameters

The parameters defined in the SQL SELECT statements:

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

E-mail Parameters

'EmailFrom', '<Address>'

Defines the from address for this e-mail message. Mandatory.

'EmailTo', '<Address list>'

Defines the recipient(s) for this e-mail message given in a list separated by comma. Mandatory.

'EmailSubject', '<Text>'

Defines the subject of the email. Default value is empty. Optional.

'EmailBody', '<Text>'

Defines the message body. Default value is empty. Optional.

'EmailCc', '<Address list>'

Defines the carbon copy recipient(s) for this e-mail message given in a list separated by comma. Optional.

'EmailBcc', '<Address list>'

Defines the blind carbon copy recipient(s) for this e-mail message given in a list separated by comma. Optional.

'EmailIsBodyHtml', '<Value>'

Defines whether the e-mail message body is in HTML. TRUE or any other Integer than "0" = body is in HTML, FALSE or "0" = body is not in HTML. Default value is FALSE. Optional.

'EmailSender', '<Address>'

Defines the sender's address for this e-mail message. Default value is empty. Optional.

'EmailReplyTo', '<Address list>'

Defines the ReplyTo address(es) for the mail message given in a list separated by comma. Optional.

'EmailPriority', '<Value>'

Defines the priority of this e-mail message. Possible values are "High", "Normal", and "Low". Default value is "Normal". Optional.

'EmailDeliveryNotification', '<Value>'

Defines the delivery notifications for this e-mail message. Possible values are "Delay", "Never", "None", "OnFailure", and "OnSuccess". Default value is "None". Optional.

'EmailBodyEncoding', '<Encoding>'

Defines the encoding used to encode the message body. Supported encodings are listed in the "Remarks" section at http://msdn.microsoft.com/en-us/library/System.Text.Encoding.aspx. Optional.

'EmailSubjectEncoding', '<Encoding>'

Defines the encoding used for the subject content for this e-mail message. Supported encodings are listed in the "Remarks" section at http://msdn.microsoft.com/en-us/library/System.Text.Encoding.aspx. Optional.

SMTP Server Parameters

'SmtpServer', '<Value>'

Defines the hostname or the IP address of the server. Mandatory for the first occurrence of the SendEmail command during script execution.

'SmtpPort', '<Value>'

Defines the port of the SMTP server. Default value is "25". Optional.

'SmtpAuthenticationUsername', '<User name>'

Defines the user name for the SMTP server. Note that the user name is in plain text and visible to all users who have access to the script. Optional.

'SmtpAuthenticationPassword', '<Password>'

Defines the password for the SMTP server. Note that the password is in plain text and visible to all users who have access to the script. Optional.

'SmtpEnableSSL ', '<Value>'

Defines whether SSL should be enabled for the SMTP connection. TRUE or any other Integer than "0" = SSL is enabled, FALSE or "0" = SSL is not enabled. Default value is "FALSE". Optional.

Example

The following example will send an e-mail message to multiple recipients.

(SELECT 'EmailFrom', 'example.from@address.com') UNION ALL

(SELECT 'EmailTo', 'recipient.one@address.com,recipient.two@address.com,recipient.three@address.com') UNION ALL

(SELECT 'EmailSubject', 'Example E-mail') UNION ALL

(SELECT 'EmailBody', 'QPR ProcessAnalyzer example script started running.') UNION ALL

(SELECT 'SmtpServer', 'localhost')

--#SendEmail

See also How to Define the SMTP Server Connection in an On-Site Deployment.

---

--#ShowReport

Creates a new excel sheet containing a table that contains the results of the user specified SQL query. The result column names are the field names of the SQL query and the rows are the actual data rows of the SQL query. The report can be used to see, for example, the events that would be loaded into QPR ProcessAnalyzer before actually loading them. If the events have problems that cause errors when loaded it is useful to be able to see the row data in a report.

Parameters

The parameters defined in the SQL SELECT statements:

''

Mandatory. The database query whose results are to be returned.

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

Optional. The Analysis Parameters given for the operation. Some suggested parameters to be used:

'Title', '<Title of the report>'

The title of the created report. If not given, "Report" will be used as a default.

'SheetName', '<Name for the sheet>'

The name of the Excel sheet to be created.

'MaximumCount', '<Value>'

The maximum number of rows to show (0 = all, default = 1000).

'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 following example opens the data table identified by data table name "SqlTable" and project name "Test" as a report.

(SELECT 'AnalysisType', '18') UNION ALL

(SELECT 'ProjectName', 'Test') UNION ALL

(SELECT 'DataTableName', 'SqlTable') UNION ALL

(SELECT 'TargetTable', '#AnalysisResult')

--#GetAnalysis

SELECT * FROM #AnalysisResult;

(SELECT 'Title', 'Report1') UNION ALL

(SELECT 'SheetName', 'Sheet1') UNION ALL

(SELECT 'MaximumCount', '0')

--#ShowReport

---

--#WriteLog

Adds the first column values from the preceding SQL statements to the log that is shown after the whole script execution is completed.

In addition to the WriteLog command, you can also use the Print SQL statement to generate log entries into the script execution log. The difference to the WriteLog command is that the Print statement can use also variables.

Example

The following example will write "Script started", "Example", "Print Example" into the log.

SELECT 'Script started'

SELECT 'Example'

--#WriteLog

PRINT 'Print Example'

---

Script Variables

The scripts have the following variables available:

• @_ProjectId: the Id of the project in whose context the script is being run. BIGINT.
• @_ModelId: the Id of the model in whose context the script is being run. BIGINT.
• @_FilterId: the Id of the view in whose context the script is being run. BIGINT.
• @_QPRProcessAnalyzerVersion: the QPR Process Analyzer core dll version as string in the format: <major>.<minor>.<build>.<revision>. NVARCHAR(64).
• @_ScriptingVersion: The scripting version number that identifies the version which was used when script was saved. Can be used for indicating, for example, the version for which the script was originally planned. A script created for newer (bigger) scripting version doesn't necessarily work on a PA server supporting older (smaller) version. INT.
• @_UserId: the Id of the user running the script. INT.
• @_ScriptId: Id of the script being run. BIGINT.
• @_EngineScriptingVersion: The scripting version that identifies the functionalities available from QPR ProcessAnalyzer server. A script created for newer (bigger) scripting version doesn't necessarily work on a QPR ProcessAnalyzer server supporting older (smaller) version. INT.
• @_DatabaseId: the Id of the database in use. GUID.
• @_ExceptionOccurred: if there was an exception when running the script, then this value is 1, otherwise 0. INT
• @_ExceptionType: if there was an exception when running the script, shows the C# class name for the exception, NVARCHAR(MAX), otherwise NULL.
• @_ExceptionMessage: if there was an exception when running the script, contains a message that would have been displayed, NVARCHAR(MAX), otherwise NULL.
• @_ExceptionDetails: if there was an exception when running the script, contains the details that would have been displayed, including the system stack trace, NVARCHAR(MAX), otherwise NULL.
• #_ViewSettings: The Settings shown in the right-hand pane in Excel used to generate the selected view. Note that this doesn't include analysis results or QPR ProcessAnalyzer Server related information, such as FilterId, TotalEventCount, or DatabaseId. Represented as a table two column table variable. The columns are:
  • Name: the name of the parameter. NVARCHAR(440).
  • Value: the value of the parameter. NVARCHAR(MAX).
• #_Selection: The objects the user has currently selected on the analysis. Supported objects are the event types and transitions in the Flowchart or Path analyses and the Excel cells in the Duration, Profiling, Cases, Variations, Event Types, or Flows analyses. Any filters already applied are included in the selection. The maximum supported amount for the selection is 1000 separate objects and 10000 cells and their values. Represented as a three column table variable. The columns are:

• Type: the type of the selected object. INT.

Type Description 0 Common. Values for the Common Type are represented as a Value-IntValue pair. See the table on the right for descriptions. 2 Activity 6 Activity Path 8 Attribute Value 10 Case 12 Count 14 Duration 16 Transition 18 Variation

Common Type Value Description NumberOfActivities The amount of activities in the selection. NumberOfActivityPaths The amount of activity paths in the selection. NumberOfAttributeValues The amount of attribute values in the selection. NumberOfCases The amount of cases in the selection. NumberOfCounts See the "Group By Event Count" entry in the Variations page. NumberOfDurations The amount of durations in the selection. NumberOfTransitions The amount of transitions in the selection. NumberOfVariations The amount of variations in the selection. DurationGranularity The duration time unit used in the selection. See the "Group By" entry in the Duration Analysis page for reference. DurationMaximum The duration limit in the selection. See the "Duration Limit" entry in the Duration Analysis page for reference. ReversedPaths The value is "1" if the "Predecessors" Direction was selected in the Path Analysis, otherwise "0". SelectionType What was selected. The value for this parameter is shown on the "Value" column instead of the IntValue column.

• IntValue: the integer representation of the selection value (used for object IDs etc). BIGINT.
• Value: the string representation of the selected value. Used for selections that require more complex representation than single integer value. NVARCHAR(MAX).

Note that even when a script context hasn't been selected, the variables get their values from a default filter. The default filter is the filter that was last used by the user in the model the user has last opened. If the user hasn't opened any model before, the default filter is taken from the last created model to which the user has access rights.

Example

The following script fragment defines the ProjectId, ModelId, and FilterId parameters by using the script variables:

SELECT @_ProjectId as ProjectId, @_ModelId as ModelId, @_FilterId as FilterId;

Example

The following script gets the View Settings currently in use in the Settings pane in Excel and shows them as a table in a new Excel sheet:

(SELECT * FROM #_ViewSettings)

(SELECT 'SheetName', 'ViewSettings')

--#ShowReport

Example

The following script gets the current selection on the analysis and shows the data as a table in a new Excel sheet:

(SELECT * FROM #_Selection)

(SELECT 'SheetName' , 'Selection')

--#ShowReport

Example

The following script gets various information about the environment and shows them in a new Excel sheet:

SELECT @_QPRProcessAnalyzerVersion as QPRProcessAnalyzerVersion, @_ScriptingVersion as ScriptingVersion, @_EngineScriptingVersion as EngineScriptingVersion, @_UserId as Userid, @_DatabaseId as DatabaseId

(SELECT 'SheetName', 'Info')

--#ShowReport

Exception Handling

In general, scripts are meant to be developed in such a way that in the end you can run the scripts without any errors. However, sometimes there may be some system issues (timeouts SAP etc.) that can cause for example data extraction scripts to fail. For these kind of situations and for development time and troubleshooting purposes, you can use the CatchOperationExceptions parameter and the @_ExceptionOccurred, @_ExceptionType, @_ExceptionMessage, and @_ExceptionDetails script variables with the QPR ProcessAnalyzer script commands to handle exceptions in ProcessAnalyzer. Out of these, the @_ExceptionOccurred is handy for example in defining some other command to be run in case there was an exception. For SQL errors, the TRY-CATCH error handling should be used.

Note that the CatchOperationExceptions parameter is in effect only for the command it is used with, i.e. it isn't in effect in child scripts or scripts that are run via the --#Exit command. In addition, when there are multiple ProcessAnalyzer script commands in the script, the @_ExceptionOccurred, @_ExceptionType, @_ExceptionMessage, and @_ExceptionDetails script variables get updated each time, i.e. the variable values are available only until the next ProcessAnalyzer command is executed. To use the same variable values in multiple ProcessAnalyzer commands in the script, place the values into a temporary table:

SELECT

@_ExceptionOccurred 'ExceptionOccurred',

@_ExceptionType 'ExceptionType',

@_ExceptionMessage 'ExceptionMessage',

@_ExceptionDetails 'ExceptionDetails'

INTO #PACommandExceptions

SQL Command Support

When transforming data in QPR ProcessAnalyzer Pro (i.e. when connected to the QPR ProcessAnalyzer Service), 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. When using QPR ProcessAnalyzer Xpress, these limitations do not apply.

Print

The Print SQL statement can be used to generate log entries into the script execution log.

Running Scripts

A script can be run in the following ways:

Running a Script from the Ribbon

1. On the QPR ProcessAnalyzer tab of the ribbon, click Run.
2. Select the script to be run from the Script Gallery that opens.

Running a Script from the Script Manager Dialog

1. On the QPR ProcessAnalyzer tab of the ribbon, click Run > Manage Scripts.
2. From the dialog, select the context in which the script you wish to run exists.
3. Select the script you wish to run.
4. Click Run.

Terminating Scripts

A script can be terminated by the following ways:

• The user running the script can click the Cancel button when the script is running.
• The script can use the "--#Exit" command stop script execution.
• 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 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 script will not revert any changes the script has already done in the database before the Cancel button is clicked.

Things to Note About Scripts

When writing and using scripts, take the following things into account:

• Only those lines in the script that start with "--#" (without the quotes) are treated as QPR ProcessAnalyzer Commands, i.e. if there are leading whitespaces before the command, the line is treated as a comment.
• If you don't define a value for the MaximumCount parameter, 1000 will be used as default, i.e. only the 1000 first rows from a given table or model will be used.
• When doing more advanced operations with scripts, you may run into the error messages such as: "The data types sql_variant and varchar are incompatible in the add operation.", "Argument data type sql_variant is invalid for argument 1 of like function.", "Argument data type sql_variant is invalid for argument 1 of left function.". This is because case attributes, event attributes, and data inside data tables are sql_variant type data. In order to use them with more advanced operations (e.g. Add), you need to CONVERT or CAST them into some other data type before the operations. See this example.
• For certain characters in attribute values, you need to use escaping in order to have them interpreted correctly in the script. For more information, see Escaping for Attribute Values.

Troubleshooting Scripts

After a script has been run, the Script Log dialog is shown. You can use it to troubleshoot your script.

Invalid Object Name

• "Invalid object name" exception is thrown when running a script with #GetAnalysis and #ImportEvents commands. What causes this?

The issue may arise when there are no events to import, i.e. the #GetAnalysis command doesn't create any table. In this case, the missing event table needs to be created in the script between the #GetAnalysis and #ImportEvents commands:

/*This is the GetAnalysis part */

(SELECT 'AnalysisType', '6') UNION ALL

(SELECT 'MaximumCount', '0') UNION ALL

(SELECT 'FilterId', '10') UNION ALL

(SELECT 'SelectedEventAttributes', '*') UNION ALL

(SELECT 'TargetTable', '#AnalysisResult')

--#GetAnalysis

/*Here we create the event table, if it is missing */

IF object_id('tempdb..#AnalysisResult') is null

BEGIN

CREATE TABLE #AnalysisResult (

CaseName NVARCHAR(MAX),

EventTypeName NVARCHAR(MAX),

TimeStamp DATETIME,

Cost FLOAT,

TotalCost FLOAT

);

END

/*Then we use the ImportEvents */

(SELECT 'ProjectName', 'ExampleProject') UNION ALL

(SELECT 'ModelName', 'ExampleModel')

(SELECT * FROM [#AnalysisResult])

--#ImportEvents

Incorrect Syntax When Using ' Characters

• SQL error: Incorrect syntax near '

If you get this error message, check the ' characters in the parameter definitions. Note that you cannot use the ' character in the parameter value.

Incorrect Syntax When Running Scripts Copied from QPR ProcessAnalyzer Wiki

• SQL error: Incorrect syntax

You may get this kind of error message when trying to run a script that you have copied and pasted directly from, for example, the examples given in Supported QPR ProcessAnalyzer Commands in Scripts. This happens because the scripts are then likely to contain non-breaking spaces (encoded as 0xA0 but not visible when pasted to the Script Code field) which cause the running of the scripts to fail as they are not accepted in the SQL syntax.

The solution is to manually remove the non-breaking spaces after copying and pasting the script, so there are no extra spaces at the beginning of each line, for example.

Using "##" in a Script

• Using "##" in a script is not allowed. Did you mistype temporary table name?

This error message is displayed if the name of the project or model name contains two consecutive # characters (i.e. ##) and you try creating a new project or a model using scripts. Note that you cannot use the ## character in the name of the project or the model.

The ? Character in Project and Model Names

• A project or model name contains question marks.

The problem arises if you try to, for example, create a project or a model with a name containing multibyte characters using scripts. The created project or model will then include question mark characters instead of multibyte characters. The fix is to add a prefix N' to the name containing multibyte characters (for example, N'【隱藏▲】【純文字檢視】【複製到剪貼簿】'). Another workaround is not to use multibyte characters at all in scripts but instead ProjectId and ModelId, if possible. To do that, you can view the Id column in the Models tab of the Workspace dialog in the same way as described for data tables in Adding an Id Column to the Data Tables List.

Multiple Projects with the Same Name

• "There are multiple projects with name '<ProjectName>'. Please, specify ProjectId in sandbox script operation: #<Operation>"

This error message is displayed when trying to run a script if you have access to two or several projects with the same name and you refer to the project by name in the script. The fix for resolving this ambiguity is to refer to the project by its ID using the 'ProjectId' parameter instead of 'ProjectName'. An ID is always unique, whereas a name is not.

Multiple Models with the Same Name

• "There are multiple models with name '<ModelName>'. Please, specify ModelId in sandbox script operation: #<Operation>"

This error message is displayed when trying to run a script if you have access to two or several models with the same name and you refer to the model by name in the script. The fix for resolving this ambiguity is to refer to the model by its ID using the 'ModelId' parameter instead of 'ModelName'. An ID is always unique, whereas a name is not.

• For example, the following script will show characters that are treated similar as zero "0":

DECLARE @referenceAscii int = 0

IF object_id('tempdb..#ids') is null 
CREATE TABLE #ids ( id int)

DECLARE @i int = 0
WHILE @i < 70000 BEGIN
    SET @i = @i + 1
    INSERT #ids VALUES (@i)
END

select * FROM (
  select 
    sys.fn_varbintohexstr(CONVERT(varBINARY(8), id)) as CharacterAsHex,
    id as ASCIICode,
    nchar(id) as Character,
   (CASE WHEN Nchar(@referenceAscii) = NCHAR(id) THEN 1 ELSE 0 END) as ShownAsSimilar 
  FROM #ids ) as x
where x.ShownAsSimilar = 1
--#ShowReport

Example Scripts

Example Script: 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

Example Script: 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

Example Script: 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

Example Script: 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

Example Script: 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

Example Script: 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

Example Script: 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