SQL Scripting for ETL: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
(214155)
 
(257 intermediate revisions by 5 users not shown)
Line 1: Line 1:
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.
This page describes how to use the SQL-based scripting language to extract data from source systems and load data into QPR ProcessAnalyzer, i.e. how to do ETL (extract, transform and load). The supported commands are described in [[SQL Scripting Commands]]. Scripts can be written in the [[Managing_Scripts|Manage Scripts]] dialog. The SQL scripts consists of SQL statements and QPR ProcessAnalyzer commands that take the preceding SQL statements as parameters.


A user with the ManageIntegrations and RunScripts permissions can define a script via the [[Script Management#Script Manager|Script Manager]] dialog. The script consists of SQL statements and QPR ProcessAnalyzer commands that take the preceding SQL statements as parameters.
__TOC__


== Supported QPR ProcessAnalyzer Commands in Scripts ==
== Introduction to SQL Scripting ==
==== --#Exit ====
: Stops the execution of the script and gives a message to the user.


: '''Parameters'''
The QPR ProcessAnalyzer ETL system enables data extraction, transformation and loading based on SQL queries implemented by scripts. Scripts are written in SQL and consist of standard SQL commands, QPR ProcessAnalyzer commands and special parameters related to QPR ProcessAnalyzer. Scripts are useful for performing several database related operations. More specifically, with the QPR ProcessAnalyzer ETL system it is possible to, for example:  
: The parameters defined in the SQL SELECT statements:<br/>
* extract data from a source system to data tables
:; 'Exit', '<Value>'
* load data from data tables and create events and case attributes to new or existing models based on this data
:: Defines whether to stop the script execution. 1 = stop execution, 0 = do not stop execution.
* read data from existing models and create reports
:; 'MessageText', '<Text>'
* import data from existing models and create new models extending the current features of QPR ProcessAnalyzer
:: 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.


: '''Example'''
== Script Variables ==
: The following example stops the script execution and gives a message.
SQL scripts have the below listed variables available.
: <code>
: SELECT ('Exit', '1') UNION ALL
: SELECT ('MessageText', 'Data from SAP not valid. Script execution will be terminated. Check source data')
: --#Exit
</code>
----
==== --#GetAnalysis ====
: Creates an analysis from the data which the preceding SQL statements given as parameters provide. The analysis parameters have to be defined in the SQL SELECT statements as follows:<br/>


: '<Analysis Parameter>', '<Value>'
{| class="wikitable"
! Variable name
! Description
|-
|@_ProjectId (BIGINT)
|Id of the project in which context the script is run. Undefined if project context has not been specified in script execution parameters.
|-
|@_ModelId (BIGINT)
|Id of the model in which context the script is run. Undefined if model context has not been specified in script execution parameters.
|-
|@_FilterId (BIGINT)
|Id of the filter in which context the script is run. Undefined if filter context has not been specified in script execution parameters.
|-
|@_UserId (INT)
|Id of the user running the script.
|-
|@_ScriptId (BIGINT)
|Id of the script that originally started the script run.
|-
|@_CurrentScriptId (BIGINT)
|Id of the script where the execution currently is. If scripts call other scripts, @_ScriptId doesn't change, whereas @_CurrentScriptId changes when the parent script calls other script.
|-
|@_ExceptionOccurred (INT)
|If there was an exception when running the script, the value is '''1''', otherwise '''0'''. INT
|-
|@_ExceptionMessage (NVARCHAR(MAX))
|If there was an exception when running the script, contains the message of the exception.
|-
|@_Parameter_<ParameterName>&nbsp;(SQL_VARIANT)
|All passed parameters are available is variables with name @_Parameter_<ParameterName> where ''ParameterName'' is the name of the variable. For example, variable ''myVariable1'' can be used with name ''@_Parameter_myVariable1''.
|}


: See [[Analysis Parameters]] for a list of supported analysis parameters in QPR ProcessAnalyzer.
== Script Variable Examples ==
The following script command defines ProjectId, ModelId, and FilterId variables by using the script variables:
<pre>SELECT @_ProjectId as ProjectId, @_ModelId as ModelId, @_FilterId as FilterId;</pre>


: The --#GetAnalysis command supports the following analysis types:
The following script gets various information about the environment:
: Case Analysis (5)
<pre>SELECT @_QPRProcessAnalyzerVersion as QPRProcessAnalyzerVersion, @_UserId as Userid
: Event Analysis (6)
(SELECT 'SheetName', 'Info')
: Event Type Analysis (7) Note: analysis is in table format only.
--#ShowReport</pre>
: Variation Analysis (8) Note: analysis is in table format only.
: User Report (11)
: OperationLog Analysis (12)
: Flow Analysis (13)
: Influence Analysis (14)
: Integration Table Analysis (18)
----
==== --#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:<br/>
:; '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 [[Model Properties|Case Attribute Model]], those case attributes are not deleted. Not used when creating a new model. Default value is TRUE.
: '''Parameters for the Second Query'''
:; '<data>'
:: The database query whose results are to be imported.
----
==== --#ImportDataTable ====
: Imports data to a Data Table.
 
: '''Parameters'''
: The parameters defined in the SQL SELECT statements in the order they are listed below:<br/>
:; '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.
:; '<data>'
:: The database query whose results are to be imported.
 
: '''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.
: <code>
: SELECT 'START
: --#WriteLog
 
: (SELECT 'AnalysisType', '6') UNION ALL
: (SELECT 'MaximumCount', '0') UNION ALL
: (SELECT 'ViewId', '3') UNION ALL
: (SELECT 'SelectedEventAttributes', '*') UNION ALL
: (SELECT 'TargetTable', '#AnalysisResult')
: --#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
</code>
 
: '''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.
: <code>
: (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
</code>
----
==== --#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:<br/>
:; '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.
: '''Parameters for the Second Query'''
:; '<data>'
:: The database query whose results are to be imported.
----
==== --#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.
'''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) = [http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_queryall.htm queryall()], 2 = [http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_query.htm query()], 3 = [http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_describesobject.htm describeSObject()].
:; 'SalesforceQuery', '<Value>'
:: The query to be run in the Salesforce cloud. Note that "*" cannot be used in the query. See [http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_query.htm Salesforce API] and [http://www.salesforce.com/us/developer/docs/soql_sosl/index.htm SOQL Reference] for more information.
:; '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 [http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_query.htm Salesforce API] for more information.
 
: '''Example'''
: The following example will load date data from the "Contact" table in the Salesforce cloud and will put that data into the "ExampleDataTable" data table 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.
: <code>
: (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')
: --#ImportSalesforceQuery
: <br/>
: (SELECT 'AnalysisType', '18') UNION ALL
: (SELECT 'ProjectName', 'ExampleProject') UNION ALL
: (SELECT 'MaximumCount', '0') UNION ALL
: (SELECT 'DataTableName', 'ExampleDataTable') UNION ALL
: (SELECT 'TargetTable', '#TABLE')
: --#GetAnalysis
: <br/>
: (SELECT * FROM #TABLE)
: (SELECT 'MaximumCount', '0')
: --#ShowReport
</code>
----
 
==== --#RemoveEvents ====
: Removes all events in the target model, but retains Cases, Event Types, and Variations.
: '''Parameters'''
: The parameters defined in the SQL SELECT statements:<br/>
:; '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.
----
==== --#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:<br/>
:; '<data>'
:: 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>'
::: Name of the created report (used as sheet name in QPR ProcessAnalyzer Excel Client). Any existing sheet with the same title will be overwritten, so a script containing multiple ShowReport commands should use a unique title for each of the ShowReport commands. If not given, "Report" will be used as a default.
:: 'MaximumCount', '<Value>'
::: The maximum number of rows to show (0 = all, default = 1000).
 
: '''Example'''
: The following example opens the data table identified by data table name "SqlTable" and project name "Test" as a report.
: <code>
: (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 'MaximumCount', '0')  
: --#ShowReport  
</code>
----
 
==== --#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 [http://msdn.microsoft.com/en-us/library/ms176047.aspx 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.
: <code>
: SELECT 'Script started'
: SELECT 'Example'
: --#WriteLog
: PRINT 'Print Example'
</code>
----
 
== 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 [http://msdn.microsoft.com/en-us/library/ms176047.aspx 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 ===
# On the QPR ProcessAnalyzer tab of the ribbon, click '''Run'''.
# Select the script to be run from the Script Gallery that opens.
 
=== Running a Script from the Script Manager Dialog ===
# On the QPR ProcessAnalyzer tab of the ribbon, click '''Run''' > '''Manage Scripts'''.
# From the dialog, select the context in which the script you wish to run exists.
# Select the script you wish to run.
# Click '''Run'''.
 
=== Running a Script by Importing a .pasql File ===
# Write the script into a file that has a .pasql file format.
# On the QPR ProcessAnalyzer tab of the ribbon, click '''Project Workspace'''.
# Click '''Import'''.
# Select '''QPR ProcessAnalyzer File''' and click '''Next'''.
# Click '''Select''' and choose '''QPR ProcessAnalyzer SQL Script File (*.pasql)''' from the file type menu.
# Browse to the .pasql file containing the script, select it, and click '''Open'''.
# Click '''Next'''.
# Select the import target:
#* '''Create New''' will create a new model and set it as the target of the script.
#* '''Add to Existing''' will set the target of the script to the selected model.
#: Either way, the script in the .pasql file will be set as the script of the target model.
# Click '''Import'''.
 
== 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!'''<br/>
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 Attribute|case attributes]], [[Event Attribute|event attributes]], and data inside [[Data Table|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 [[#ConvertExample|this example]].
 
== Example Scripts ==
 
=== Example Script: Create a copy of existing model ===
The following script will:<br/>
1. Clear the current contents of the model<br/>
2. Select all events from another model defined with ViewId = 1234567. (note: change this Id to be an Id from the default Filter (previously called View) of the source model:<br/>
* AnalysisType 6 => Event Table
* MaximumCount 0 => Retrieve all events from the model
* ViewId 1234567 => CHANGE THIS to be the Filter ID / ViewId 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 current model (#ImportEvents)
 
4. Select all case attributes from the source model (defined with ViewId = 1234567)
* AnalysisType 5 => Case Table
* MaximumCount 0 => Retrieve all cases from the model
* ViewId 1234567 => CHANGE THIS to be the Filter ID / ViewId 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 current model (#ImportCaseAttributes)


== Calling Expression Script from SQL Script ==
Expression scripts can be called from SQL scripts by using the [[SQL_Scripting_Commands#--.23GetAnalysis|--#GetAnalysis]] command. There you need to call the ''AnalysisType=33'' to run an expression to use the [[QPR_ProcessAnalyzer_Objects_in_Expression_Language#Script|Run]] function. Here is an example (to call an expression script with id 123 and pass parameters values):
<pre>
<pre>
--#RemoveEvents
(SELECT 'AnalysisType', '33') UNION ALL
 
(SELECT 'TargetTable', '#ExampleTable') UNION ALL
(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'Configuration', '{
(SELECT 'MaximumCount', '0') UNION ALL
"Root": "ScriptById(123).Run(#{
(SELECT 'ViewId', '1234567') UNION ALL
\"parameter1\": \"value1\",
(SELECT 'SelectedEventAttributes', '*') UNION ALL
\"parameter2\": 321
(SELECT 'TargetTable', '#AnalysisResult')
})"
}')
--#GetAnalysis
--#GetAnalysis
SELECT * FROM [#AnalysisResult]
--#ImportEvents
(SELECT 'AnalysisType', '5') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'ViewId', '1234567') UNION ALL
(SELECT 'SelectedCaseAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult2')
--#GetAnalysis
SELECT * FROM [#AnalysisResult2]
--#ImportCaseAttributes
</pre>
</pre>


=== Example Script: Create an extended copy of existing model with new case attributes ===
In the above example, if the expression script returns a DataFrame, it is stored to the ''#ExampleTable'' temporary table in the scripting sandbox database.
This scripts extends the simple model copying script by creating new case attributes from Case Table report<br/>
1. Clear the current contents of the model<br/>
2. Select all events from another model defined with ViewId = 1234567. (note: change this Id to be an Id from the default Filter (previously called View) of the source model:<br/>
* AnalysisType 6 => Event Table
* MaximumCount 0 => Retrieve all events from the model
* ViewId 1234567 => CHANGE THIS to be the Filter ID / ViewId 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 current model (#ImportEvents)
== Calling SQL Script from SQL Script ==
 
In an SQL script, it's possible to call other SQL script using the [[SQL_Scripting_Commands#--.23Run|--#Run]] command. For more information, see its [[SQL_Scripting_Commands#--.23Run|documentation]] and [[Run_Script_Examples|examples]].
4. Select all case attributes from the source model (defined with ViewId = 1234567)
* AnalysisType 5 => Case Table
* MaximumCount 0 => Retrieve all cases from the model
* ViewId 1234567 => CHANGE THIS to be the Filter ID / ViewId 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 current model (#ImportCaseAttributes)


== 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.<br/>
<br/>
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:<br/>
<pre>
<pre>
--#RemoveEvents
SELECT @_ExceptionOccurred 'ExceptionOccurred', @_ExceptionMessage 'ExceptionMessage'
 
INTO #PACommandExceptions
(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'ViewId', '1234567') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis
 
SELECT * FROM [#AnalysisResult]
--#ImportEvents
 
(SELECT 'AnalysisType', '5') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'ViewId', '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 * FROM [#AnalysisResult2]
--#ImportCaseAttributes
</pre>
</pre>


=== Example Script: Create a copy of events and switch the ABPD dimension from original EventType to Event Attribute 'Organization' ===
== Example Script Walkthough==
The following script will:<br/>
Here is an example of a simple ETL script that prints data taken from the table #Customers:
1. Clear the current contents of the model<br/>
2. Select all events from another model defined with ViewId = 1234567. (note: change this Id to be an Id from the default Filter (previously called View) of the source model:<br/>
* AnalysisType 6 => Event Table
* MaximumCount 0 => Retrieve all events from the model
* ViewId 1234567 => CHANGE THIS to be the Filter ID / ViewId 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 current model (#ImportEvents)
* 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
 
<pre>
<pre>
--#RemoveEvents
SELECT * FROM #Customers;
 
(SELECT 'Title', 'Customers') UNION ALL  
(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0');
(SELECT 'MaximumCount', '0') UNION ALL
--#ShowReport
(SELECT 'ViewId', '1234567') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis
 
SELECT [Case], [Organization], [Start Time], * FROM [#AnalysisResult]
--#ImportEvents
</pre>
</pre>


=== Example Script: Create a copy of events and combine some event types to a new event type ===
This script consists of SQL statements (lines 1-3) and a QPR ProcessAnalyzer command (line 4) that take the results of the preceding SQL SELECT statements as parameters.
The following script will:<br/>
1. Clear the current contents of the model<br/>
2. Select all events from another model defined with ViewId = 1234567. (note: change this Id to be an Id from the default Filter (previously called View) of the source model:<br/>
* AnalysisType 6 => Event Table
* MaximumCount 0 => Retrieve all events from the model
* ViewId 1234567 => CHANGE THIS to be the Filter ID / ViewId 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 current model (#ImportEvents)
Let’s go through this example in more detail (line 1):
* SELECT [Case], 'COMBINED', [Start Time] FROM [#AnalysisResult] WHERE [Event Type] IN ('Sales Order', 'Shipment')
<pre>SELECT * FROM #Customers; </pre>
=> Select those event type that should be combined and use the new EventType name for them<br/>
=> This example replaces 'Sales Order' and 'Shipment' with new EventType name 'COMBINED'<br/>
* 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<br/>


<pre>
SELECT is one of the most basic SQL commands you can use. The SELECT statement is used to extract data from a table. You can define the criteria for what data is selected for extraction. On line 1 of the example above, the SQL statement selects all the columns from the #Customers table. In addition, this produces the first input argument for the QPR ProcessAnalyzer command —#ShowReport by defining what to print.
--#RemoveEvents
The preceding hash symbol (#) indicates that results are stored to a temporary table. They exist only during the execution of the script so they are not stored permanently.


(SELECT 'AnalysisType', '6') UNION ALL
Lines 2-3:
(SELECT 'MaximumCount', '0') UNION ALL
<pre>(SELECT 'Title', 'Customers') UNION ALL  
(SELECT 'ViewId', '1234567') UNION ALL
(SELECT 'MaximumCount', '0'); </pre>
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis


SELECT [Case], 'COMBINED', [Start Time], * FROM [#AnalysisResult] WHERE [Event Type] IN ('Sales Order', 'Shipment')
These lines create the second input argument for the command --#ShowReport by giving a label for the sheet and defining how many lines to print. ‘0’ means that all rows will be printed.
UNION ALL
SELECT [Case], [Event Type], [Start Time], * FROM [#AnalysisResult] WHERE [Event Type] NOT IN ( 'Sales Order', 'Shipment')
--#ImportEvents
</pre>


<div id="ConvertExample"></div>
SQL uses the UNION operator to combine the result-set of two or more SELECT statements. UNION ALL is used to select all the values (including duplicate values) whereas UNION is used to select only the distinct values.
=== 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:<br/>
1. Clear the current contents of the model<br/>
2. Select all events from another model defined with ViewId = 1234567. (note: change this Id to be an Id from the default Filter (previously called View) of the source model:<br/>
* AnalysisType 6 => Event Table
* MaximumCount 0 => Retrieve all events from the model
* ViewId 1234567 => CHANGE THIS to be the Filter ID / ViewId 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 current model (#ImportEvents)
Line 4:
* SELECT [Case], convert(varchar(255),[Event Type]) + '_' + convert(varchar(255),[SAP_User]), [Start Time], * FROM [#AnalysisResult]
<pre>--#ShowReport </pre>
=> Create the new EventType by combining original Event Type, a string '_' and event attribute SAP_User<br/>
<pre>
--#RemoveEvents


(SELECT 'AnalysisType', '6') UNION ALL
When writing scripts with the QPR ProcessAnalyzer ETL system, the lines in the script that start with "--#" (without the quotes and without any preceding blank spaces at the beginning of the line) are treated as QPR ProcessAnalyzer Commands and SQL ignores these lines as comments. The supported QPR ProcessAnalyzer commands and some examples of how to use them in scripts are listed in [[SQL_Scripting_Commands|here]].
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'ViewId', '1234567') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis


SELECT [Case], convert(varchar(255),[Event Type]) + '_' + convert(varchar(255),[SAP_User]), [Start Time], * FROM [#AnalysisResult]
== Tips for Writing SQL Scripts ==
--#ImportEvents
* It is important to follow the T-SQL syntax when writing SQL queries.
</pre>
* SQL is not case sensitive, so you can write either ''SELECT'' or ''select''. However, the parameter values and the QPR ProcessAnalyzer commands given as comments are case sensitive.
 
* Semicolon is used to end SQL statements. It is not always required, but in some cases the missing semicolon may cause SQL errors due to ambiguity. We recommend using a semicolon especially at the end of SELECT statements preceding the QPR ProcessAnalyzer ETL commands to avoid SQL errors.
=== Example Script: calculate the amount of events within same case with same timestamp ===
* Table field names may contain white spaces, special characters, or reserved words but then they must be referred to by using brackets ([]) around them. This situation happens frequently in QPR ProcessAnalyzer ETL scripts since the case and event attribute data contain fields that have extra spaces.
The following script will:<br/>
* When transforming data, ''temporary tables'' can be used (temporary tables names start with ''#''). Note that ''global temporary tables'' (tables names start with ''##'') is not allowed.
1.. Select all events from another model defined with ViewId = 1234567. (note: change this Id to be an Id from the default Filter (previously called View) of the source model:<br/>
* The [http://msdn.microsoft.com/en-us/library/ms176047.aspx Print SQL statement] can be used to generate log entries into the script execution log.
* AnalysisType 6 => Event Table
* 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.
* MaximumCount 0 => Retrieve all events from the model
* 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.
* ViewId 1234567 => CHANGE THIS to be the Filter ID / ViewId of the source model from which you want to make a copy
* 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 datatables 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 [[#ConvertExample|this example]].
* SelectedEventAttributes * => Get all Event Attributes
* For certain characters in attribute values, you need to use escaping in order to have them interpreted correctly in the script.
* 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
 
<pre>
 
(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'ViewId', '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
 
</pre>
 
=== 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
 
<pre>
--#RemoveEvents
 
(SELECT 'AnalysisType', '12') UNION ALL
(SELECT 'MaximumCount', '10000') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis
 
(
  SELECT
    [Session Id], 'InitializeConnection', DATEADD(ms, 3, [Start Time]), [Model Id], [Model Name],
    (CASE WHEN ([Message] LIKE 'Exception of type:%') THEN
  'Exception'
ELSE
  NULL
END) AS [Error],  
[Message], [Additional Data]
  FROM
    #AnalysisResult
  WHERE
    [Name] IN (
  'SqlCore.InitializeConnection'
)
)
UNION ALL
(
  SELECT
    [Session Id], 'InitializeSessionManager', [Start Time], [Model Id], [Model Name],
    (CASE WHEN ([Message] LIKE 'Exception of type:%') THEN
  'Exception'
ELSE
  NULL
END) AS [Error],
[Message], [Additional Data]
  FROM
    #AnalysisResult
  WHERE
    [Name] IN (
  'SingletonSessionManager.InitializeSessionManager'
)
)
UNION ALL
(
  SELECT
    [Session Id], 'Start:' + [Name], [Start Time], [Model Id], [Model Name], NULL AS [Error], NULL AS [Message], [Additional Data]
  FROM
    #AnalysisResult
  WHERE
    [Name] NOT IN (
  'SingletonSessionManager.InitializeSessionManager',
  'SqlCore.InitializeConnection',
  'SqlCore.Dispose'
)
)
UNION ALL
(
  SELECT
    [Session Id], 'End:' + [Name], DATEADD(ms, 3, [End Time]), [Model Id], [Model Name],
    (CASE WHEN ([Message] LIKE 'Exception of type%') THEN
  'Exception'
ELSE
  NULL
END) AS [Error],
[Message], NULL AS [Additional Data]
  FROM
    #AnalysisResult
  WHERE
    [End Time] IS NOT NULL
    AND [Name] NOT IN (
  'SingletonSessionManager.InitializeSessionManager',
  'SqlCore.InitializeConnection',
  'SqlCore.Dispose'
)
)
--#ImportEvents
 
SELECT
  [Session Id], [User Id], [User Name]
FROM
  #AnalysisResult
GROUP BY
  [Session Id], [User Id], [User Name]
HAVING
  [User Id] <> 0
--#ImportCaseAttributes
</pre>
 
[[Category:ETL]]
[[Category:API]]

Latest revision as of 13:39, 31 August 2023

This page describes how to use the SQL-based scripting language to extract data from source systems and load data into QPR ProcessAnalyzer, i.e. how to do ETL (extract, transform and load). The supported commands are described in SQL Scripting Commands. Scripts can be written in the Manage Scripts dialog. The SQL scripts consists of SQL statements and QPR ProcessAnalyzer commands that take the preceding SQL statements as parameters.

Introduction to SQL Scripting

The QPR ProcessAnalyzer ETL system enables data extraction, transformation and loading based on SQL queries implemented by scripts. Scripts are written in SQL and consist of standard SQL commands, QPR ProcessAnalyzer commands and special parameters related to QPR ProcessAnalyzer. Scripts are useful for performing several database related operations. More specifically, with the QPR ProcessAnalyzer ETL system it is possible to, for example:

  • extract data from a source system to data tables
  • load data from data tables and create events and case attributes to new or existing models based on this data
  • read data from existing models and create reports
  • import data from existing models and create new models extending the current features of QPR ProcessAnalyzer

Script Variables

SQL scripts have the below listed variables available.

Variable name Description
@_ProjectId (BIGINT) Id of the project in which context the script is run. Undefined if project context has not been specified in script execution parameters.
@_ModelId (BIGINT) Id of the model in which context the script is run. Undefined if model context has not been specified in script execution parameters.
@_FilterId (BIGINT) Id of the filter in which context the script is run. Undefined if filter context has not been specified in script execution parameters.
@_UserId (INT) Id of the user running the script.
@_ScriptId (BIGINT) Id of the script that originally started the script run.
@_CurrentScriptId (BIGINT) Id of the script where the execution currently is. If scripts call other scripts, @_ScriptId doesn't change, whereas @_CurrentScriptId changes when the parent script calls other script.
@_ExceptionOccurred (INT) If there was an exception when running the script, the value is 1, otherwise 0. INT
@_ExceptionMessage (NVARCHAR(MAX)) If there was an exception when running the script, contains the message of the exception.
@_Parameter_<ParameterName> (SQL_VARIANT) All passed parameters are available is variables with name @_Parameter_<ParameterName> where ParameterName is the name of the variable. For example, variable myVariable1 can be used with name @_Parameter_myVariable1.

Script Variable Examples

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

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

The following script gets various information about the environment:

SELECT  @_QPRProcessAnalyzerVersion as QPRProcessAnalyzerVersion, @_UserId as Userid
(SELECT 'SheetName', 'Info')
--#ShowReport

Calling Expression Script from SQL Script

Expression scripts can be called from SQL scripts by using the --#GetAnalysis command. There you need to call the AnalysisType=33 to run an expression to use the Run function. Here is an example (to call an expression script with id 123 and pass parameters values):

(SELECT 'AnalysisType', '33') UNION ALL
(SELECT 'TargetTable', '#ExampleTable') UNION ALL
(SELECT 'Configuration', '{
	"Root": "ScriptById(123).Run(#{
		\"parameter1\": \"value1\",
		\"parameter2\": 321
	})"
}')
--#GetAnalysis

In the above example, if the expression script returns a DataFrame, it is stored to the #ExampleTable temporary table in the scripting sandbox database.

Calling SQL Script from SQL Script

In an SQL script, it's possible to call other SQL script using the --#Run command. For more information, see its documentation and examples.

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', @_ExceptionMessage 'ExceptionMessage'
INTO #PACommandExceptions

Example Script Walkthough

Here is an example of a simple ETL script that prints data taken from the table #Customers:

SELECT * FROM #Customers; 
(SELECT 'Title', 'Customers') UNION ALL 
(SELECT 'MaximumCount', '0'); 
--#ShowReport 

This script consists of SQL statements (lines 1-3) and a QPR ProcessAnalyzer command (line 4) that take the results of the preceding SQL SELECT statements as parameters.

Let’s go through this example in more detail (line 1):

SELECT * FROM #Customers; 

SELECT is one of the most basic SQL commands you can use. The SELECT statement is used to extract data from a table. You can define the criteria for what data is selected for extraction. On line 1 of the example above, the SQL statement selects all the columns from the #Customers table. In addition, this produces the first input argument for the QPR ProcessAnalyzer command —#ShowReport by defining what to print.

The preceding hash symbol (#) indicates that results are stored to a temporary table. They exist only during the execution of the script so they are not stored permanently.

Lines 2-3:

(SELECT 'Title', 'Customers') UNION ALL 
(SELECT 'MaximumCount', '0'); 

These lines create the second input argument for the command --#ShowReport by giving a label for the sheet and defining how many lines to print. ‘0’ means that all rows will be printed.

SQL uses the UNION operator to combine the result-set of two or more SELECT statements. UNION ALL is used to select all the values (including duplicate values) whereas UNION is used to select only the distinct values.

Line 4:

--#ShowReport 

When writing scripts with the QPR ProcessAnalyzer ETL system, the lines in the script that start with "--#" (without the quotes and without any preceding blank spaces at the beginning of the line) are treated as QPR ProcessAnalyzer Commands and SQL ignores these lines as comments. The supported QPR ProcessAnalyzer commands and some examples of how to use them in scripts are listed in here.

Tips for Writing SQL Scripts

  • It is important to follow the T-SQL syntax when writing SQL queries.
  • SQL is not case sensitive, so you can write either SELECT or select. However, the parameter values and the QPR ProcessAnalyzer commands given as comments are case sensitive.
  • Semicolon is used to end SQL statements. It is not always required, but in some cases the missing semicolon may cause SQL errors due to ambiguity. We recommend using a semicolon especially at the end of SELECT statements preceding the QPR ProcessAnalyzer ETL commands to avoid SQL errors.
  • Table field names may contain white spaces, special characters, or reserved words but then they must be referred to by using brackets ([]) around them. This situation happens frequently in QPR ProcessAnalyzer ETL scripts since the case and event attribute data contain fields that have extra spaces.
  • When transforming data, temporary tables can be used (temporary tables names start with #). Note that global temporary tables (tables names start with ##) is not allowed.
  • The Print SQL statement can be used to generate log entries into the script execution log.
  • 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 datatables 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.