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, 0 = do not stop execution.
'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.

Example

The following example stops the script execution and gives a message.


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

--#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:

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

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

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

Case Analysis (5)

Event Analysis (6)

Event Type Analysis (7) Note: analysis is in table format only.

Variation Analysis (8) Note: analysis is in table format only.

User Report (11)

OperationLog Analysis (12)

Flow Analysis (13)

Influence Analysis (14)

Integration Table Analysis (18)

--#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 project is created.
'Append', '<Value>': Defines what to do with an existing target model contents. TRUE or any other Integer than "0" = the existing contents in the target model are not deleted before import, FALSE or "0" = the existing contents 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

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

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

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.


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

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 project is created.
'Append', '<Value>': Defines what to do with an existing target model contents. TRUE or any other Integer than "0" = the existing contents in the target model are not deleted before import, FALSE or "0" = the existing contents 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

'': 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) = 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.
'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 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.


(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


(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 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. 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 project is created.
'Append', '<Value>': Defines what to do with an existing target model contents. TRUE or any other Integer than "0" = the existing contents in the target model are not deleted before import, FALSE or "0" = the existing contents of the target model are deleted before the import. Not used when creating a new model. Default value is TRUE.
'': The database query whose results are to be imported.

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

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.


(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

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

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

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

Example Scripts

Example Script: Create a copy of existing model

The following script will:
1. Clear the current contents of the model
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:

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 (#ImportCaseData)

--#RemoveEvents

(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 'TargetTable', '#AnalysisResult2')
--#GetAnalysis

SELECT * FROM [#AnalysisResult2]
--#ImportCaseData

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 model
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:

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
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 (#ImportCaseData)

--#RemoveEvents

(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]
--#ImportCaseData

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 model
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:

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

--#RemoveEvents

(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'ViewId', '1234567') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis

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 model
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:

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], '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

--#RemoveEvents

(SELECT 'AnalysisType', '6') UNION ALL
(SELECT 'MaximumCount', '0') UNION ALL
(SELECT 'ViewId', '1234567') UNION ALL
(SELECT 'SelectedEventAttributes', '*') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis

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 model
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:

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], 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

--#RemoveEvents

(SELECT 'AnalysisType', '6') UNION ALL
(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]
--#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 ViewId = 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
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)

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

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

--#RemoveEvents

(SELECT 'AnalysisType', '12') UNION ALL
(SELECT 'MaximumCount', '10000') UNION ALL
(SELECT 'TargetTable', '#AnalysisResult')
--#GetAnalysis

(
  SELECT 
    [Session Id], 'InitializeConnection', DATEADD(ms, 3, [Start Time]), [Model Id], [Model Name],
    (CASE WHEN ([Message] LIKE 'Exception of type:%') THEN
	  'Exception'
	ELSE
	  NULL
	END) AS [Error], 
	[Message], [Additional Data]
  FROM 
    #AnalysisResult
  WHERE
    [Name] IN (
	  'SqlCore.InitializeConnection'
	)
)
UNION ALL
(
  SELECT 
    [Session Id], 'InitializeSessionManager', [Start Time], [Model Id], [Model Name],
    (CASE WHEN ([Message] LIKE 'Exception of type:%') THEN
	  'Exception'
	ELSE
	  NULL
	END) AS [Error], 
	[Message], [Additional Data]
  FROM 
    #AnalysisResult
  WHERE
    [Name] IN (
	  'SingletonSessionManager.InitializeSessionManager'
	)
)
UNION ALL
(
  SELECT 
    [Session Id], 'Start:' + [Name], [Start Time], [Model Id], [Model Name], NULL AS [Error], NULL AS [Message], [Additional Data]
  FROM 
    #AnalysisResult
  WHERE
    [Name] NOT IN (
	  'SingletonSessionManager.InitializeSessionManager',
	  'SqlCore.InitializeConnection',
	  'SqlCore.Dispose'
	)
)
UNION ALL
(
  SELECT 
    [Session Id], 'End:' + [Name], DATEADD(ms, 3, [End Time]), [Model Id], [Model Name],
    (CASE WHEN ([Message] LIKE 'Exception of type%') THEN
	  'Exception'
	ELSE
	  NULL
	END) AS [Error], 
	[Message], NULL AS [Additional Data]
  FROM 
    #AnalysisResult
  WHERE
    [End Time] IS NOT NULL
    AND [Name] NOT IN (
	  'SingletonSessionManager.InitializeSessionManager',
	  'SqlCore.InitializeConnection',
	  'SqlCore.Dispose'
	)
)
--#ImportEvents

SELECT 
  [Session Id], [User Id], [User Name]
FROM 
  #AnalysisResult
GROUP BY
  [Session Id], [User Id], [User Name]
HAVING
  [User Id] <> 0
--#ImportCaseData

SQL Scripting for ETL

Contents

Supported QPR ProcessAnalyzer Commands in Scripts

--#Exit

--#GetAnalysis

--#ImportCaseAttributes

--#ImportDataTable

--#ImportEvents

--#ImportSalesforceQuery

--#RemoveEvents

--#ShowReport

--#WriteLog

SQL Command Support

Print

Running Scripts

Running a Script from the Ribbon

Running a Script from the Script Manager Dialog

Running a Script by Importing a .pasql File

Terminating Scripts

Things to Note About Scripts

Example Scripts

Example Script: Create a copy of existing model

Example Script: Create an extended copy of existing model with new case attributes

Example Script: Create a copy of events and switch the ABPD dimension from original EventType to Event Attribute 'Organization'

Example Script: Create a copy of events and combine some event types to a new event type

Example Script: Create a copy of events and define new event types based on old event types and an event attribute

Example Script: calculate the amount of events within same case with same timestamp

Example Script: Create a new model from operation log

Navigation menu

SQL Scripting for ETL

Supported QPR ProcessAnalyzer Commands in Scripts

--#Exit

--#GetAnalysis

--#ImportCaseAttributes

--#ImportDataTable

--#ImportEvents

--#ImportSalesforceQuery

--#RemoveEvents

--#ShowReport

--#WriteLog

SQL Command Support

Print

Running Scripts

Running a Script from the Ribbon

Running a Script from the Script Manager Dialog

Running a Script by Importing a .pasql File

Terminating Scripts

Things to Note About Scripts

Example Scripts

Example Script: Create a copy of existing model

Example Script: Create an extended copy of existing model with new case attributes

Example Script: Create a copy of events and switch the ABPD dimension from original EventType to Event Attribute 'Organization'

Example Script: Create a copy of events and combine some event types to a new event type

Example Script: Create a copy of events and define new event types based on old event types and an event attribute

Example Script: calculate the amount of events within same case with same timestamp

Example Script: Create a new model from operation log

Navigation menu

Search