SQL Scripting for ETL

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search

This page describes how 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 and their descriptions are listed in QPR ProcessAnalyzer Scripting Commands. 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. Scripts can be written in the Manage Scripts dialog in the Excel Client. The script consists of SQL statements and QPR ProcessAnalyzer commands that take the preceding SQL statements as parameters.

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

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

Notes for Scripting

When working with scripts, note the following points:

  • 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. For more information, see Escaping for Attribute Values.
  • The following transport layer security protocols are supported when using for example the CallWebService, ImportOdbcQuery, ImportOleDbQuery, ImportSapQuery, ImportSqlQuery, and SendEmail script commands: TLS 1.0, TLS 1.1 and TLS 1.2. SSL2 and SSL3 are not supported.

See Also

Retrieved from "https://wiki.onqpr.com/pa/index.php?title=SQL_Scripting_for_ETL&oldid=18220"