Exercise 3: Showing a Data Table

From QPR ProcessAnalyzer Wiki
Jump to: navigation, search

In this third exercise of QPR ProcessAnalyzer ETL Tutorial, you will create an analysis from a data table and report the result on an Excel sheet. We will utilize the sample data we imported in the Getting Started section.

  1. In the Manage Scripts dialog, from the Context drop-down menu, select Project.
  2. From the Selected Project drop-down menu, select ETL Tutorial.
  3. Select New…
  4. Fill in the following properties for the script:
    • Name: Show data table
    • Group: Exercise
    • Description: Third exercise
  5. In the Script Code, write the following script (we will go through the script to explain it in detail later on):
(SELECT 'AnalysisType', '18') UNION ALL
(SELECT 'Show', '1') UNION ALL 
(SELECT 'Title', 'Data from GetAnalysis(18)') UNION ALL 
(SELECT 'SheetName', 'Ex.2 from getAnalysis') UNION ALL 
(SELECT 'TargetTable', '#o2c_case') UNION ALL
(SELECT 'ProjectName','ETL Tutorial') UNION ALL
(SELECT 'DataTableName','ETL Case Attribute Data');
--#GetAnalysis 
SELECT * from #o2c_case;
(SELECT 'Title', 'Data from #o2c_case') UNION ALL 
(SELECT 'SheetName', 'Ex.2 from showReport') UNION ALL
(SELECT 'ProjectName','ETL Tutorial') UNION ALL 
(SELECT 'MaximumCount', '0'); 
--#ShowReport 
SELECT [Product Group],[Account Manager],count(*) as [count] from #o2c_case group by [Product Group],[Account Manager]
(SELECT 'Title', 'Statistics ...#o2c_case') UNION ALL 
(SELECT 'SheetName', 'Ex.3 from showReport') UNION ALL 
(SELECT 'MaximumCount', '0'); 
--#ShowReport 

Before running the script, let’s go through it in more detail:

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

First you need to define the analysis type you want to create. ‘18’ means that this is a Data Table Analysis, i.e. it extracts data from the specified data table. For a list supported analysis parameters, see QPR ProcessAnalyzer Analysis Parameters.

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

This defines that the analysis is opened in Excel after the script is run.

(SELECT 'Title', 'Data from GetAnalysis(18)') UNION ALL
(SELECT 'SheetName', 'Ex.2 from getAnalysis') UNION ALL

This defines the title and the name for the Excel sheet to be created.

(SELECT 'TargetTable', '#o2c_case') UNION ALL<pre>
This defines the name for the temporary table to which the analysis is to be stored.
<pre>(SELECT 'ProjectName','ETL Tutorial') UNION ALL

This defines the project from which the data table is shown.

(SELECT 'DataTableName','ETL Case Attribute Data');

This defines the data table from which data is to be extracted.

--#GetAnalysis

This gives the command for QPR ProcessAnalyzer to create the analysis.

The following will report the selected data from the table (#o2c_case) we created above. We will name this sheet as “Ex. 2 from showReport”. The Excel sheet should be identical to the previous one.

SELECT * from #o2c_case;
(SELECT 'Title', 'Data from #o2c_case') UNION ALL 
(SELECT 'SheetName', 'Ex.2 from showReport') UNION ALL
(SELECT 'ProjectName','ETL Tutorial') UNION ALL 
(SELECT 'MaximumCount', '0'); 
--#ShowReport 

Finally we want to add some counts for the third sheet we will create (called “Ex. 3 from showReport”). This script will create columns “Product Group”, “Account Manager” and “count” which counts the occurrences of these two attributes in the order given in the “group by” statement.

SELECT [Product Group],[Account Manager],count(*) as [count] from #o2c_case group by [Product Group],[Account Manager]
(SELECT 'Title', 'Statistics ...#o2c_case') UNION ALL 
(SELECT 'SheetName', 'Ex.3 from showReport') UNION ALL 
(SELECT 'MaximumCount', '0'); 
--#ShowReport 


6. Click Save and then Run to see the result of the script you just created. You will have the following three new sheets created:

EtlShow1.png

EtlShow2.png

EtlShow3.png


Now you can move on to Exercise 4: Creating a Model from Data Tables.