Exercise 4: Creating a Model from Data Tables

From QPR ProcessAnalyzer Wiki
Jump to: navigation, search

In this fourth exercise of QPR ProcessAnalyzer ETL Tutorial, you will create a new model from data tables.

  1. For this script, you will need to know the ID of the current model so you can refer to it in the script. (Model ID’s are always unique whereas model names are not, so it is better to refer to the ID to avoid ambiguity.) To find out the ID of the model, go to Project Workspace, right click a column title and select Column Chooser from the popup. Then drag the Id column to the desired position. Now you can see the ID of the model.
  2. In the Manage Scripts dialog, from the Context drop-down menu, select Project.
  3. From the Selected Project drop-down menu, select ETL Tutorial.
  4. Select New…
  5. Fill in the following properties for the script:
    • Name: Create model from data tables
    • Group: Exercise
    • Description: Fourth exercise
  6. In the Script Code, write the following script:
(SELECT 'AnalysisType', '18') UNION ALL 
(SELECT 'TargetTable', '#o2c_case') UNION ALL
(select 'DataTableName','ETL Case Attribute Data');

(SELECT 'AnalysisType', '18') UNION ALL 
(SELECT 'TargetTable', '#o2c_event') UNION ALL
(select 'DataTableName','ETL Event Data');

(SELECT 'ModelId', '2');
SELECT * FROM #o2c_event;

(SELECT 'ModelId', '2');
SELECT * FROM #o2c_case;

This script extracts events and case attribute data from the data tables and loads it into temporary tables in the sandbox database with the first two “--#GetAnalysis” commands. Then it imports events with the “--#ImportEvents” command into the specified model. Finally it imports case attributes with the “--#ImportCaseAttributes” command into the model.

7. Click Save and then Run.

Now you can move on to Exercise 5: Copying a Script to Another Context and Deleting a Script.