Working with Lifecycle Query Engine relational store reports

Editing queries to create advanced reports

About this task

The basic workflow for creating advanced reports is to create a simple report by using the guided Report Builder and then if necessary, modify the generated SQL or SPARQL query. When you modify the generated query and then make changes by using the guided Report Builder interface, the generated query is automatically updated and becomes read only. The generated query is displayed in the text editor under the Advanced section.
For additional information, see Advanced Report Creation in Jazz® Reporting Service and SPARQL tutorial
Note: This article was originally written for Jazz Reporting Service (Jazz Reporting Service) 5.0.2 but it is also applicable to the current release.

Converting a SPARQL query to an SQL query in Report Builder

Procedure

To convert an Lifecycle Query Engine report to use the new Lifecycle Query Engine relational store:

  1. Edit the report.
  2. You can enable the new SQL option in one of two ways. Go to Choose data page and select SQL option in the Choose a report type section or go to Format results page and select SQL option in the Advanced section. When the SQL option is enabled, the underlying query is changed to SQL instead of SPARQL. You can see this in the Advanced section of the Format results page.
    Note: You will see a beta banner on the screen when you go to any Report Builder page. To remove the beta indication, you must contact IBM support and acquire a license key. Add the license key in the LQE relational store activation key field on Advanced Properties page in Report Builder.
  3. The generated query displays SQL instead of SPARQL.
    Note: If the report uses any custom expression or advanced SQL, you must edit and convert them manually to SQL. For more information, see section Manually converting SPARQL to SQL.
  4. You can specify the SQL query hint into select statements to improve report performance.
    Note: This is only applicable for Lifecycle Query Engine relational store.
  5. Run the report and ensure that it gives the same results. For more information, see section Comparing query results between SPARQL and SQL.
  6. If you are satisfied with the results save the report. The report starts using the Lifecycle Query Engine relational store.
    Note: The report can always be flipped back to using the LQE Jena database by clearing the SQL checkbox and saving the report again.

Manually converting SPARQL to SQL

Procedure

SQL is a standardized language for defining and manipulating data in a relational database.

In accordance with the relational model of data:

  • The database is treated as a set of tables.
  • Relationships are represented by values in tables.
  • Data is retrieved by specifying a result table that can be derived from one or more base tables.

All executable SQL statements must be prepared before they can be executed. The result of preparation is the executable. The method of preparing an SQL statement and the persistence of its operational form distinguish static SQL from dynamic SQL. See Db2 SQL for additional details.

To convert SPARQL to SQL, the manual approach is the hardest but usually the fastest. The following are examples for converting SQARQL to Db2 SQL:

  1. SPARQL SUM of all user story points when user story is accepted. 
    SUM(IF($User Story:Story Points (numeric)$ && $User Story:Status$ = 'Accepted',$User Story:Story Points (numeric)$,0))

    Db2/Oracle SQL translation of the SPARQL

    
         SUM(
             CASE 
                 WHEN 
                 $User Story:Story Points (numeric)$ AND $User Story:Status$ = 'Accepted'  
                 THEN $User Story:Story Points (numeric)$  
             ELSE 0 END)
  2. SPARQL SUM of all user story points when user story is accepted. 
    
              SUM(IF((
                    (lqe_fn:dateDiff('day', $RFE for internal use only:Creation Date$, NOW()) <31  )
                     &&
                    ($RFE for internal use only:Status$="New" || $RFE for internal use only:Status$="Submitted")
                    ),1,0))

    Db2 SQL translation of the SPARQL

    
         SUM(
             CASE
                 WHEN
                 (DAYS(CURRENT TIMESTAMP) - DAYS($RFE for internal use only:Creation Date$)) < 31 AND
                 ($RFE for internal use only:Status$='New' OR $RFE for internal use only:Status$='Submitted')
                 THEN 1
             ELSE 0 END)
  3. SPARQL evaluates score value and mark it high, medium, low, or NA. 
    
              IF((($Eval:Score$>=8)),"HIGH", 
              IF((($Eval:Score$>=4.0) && ($Eval:Score$ < 8)),"MEDIUM", 
              IF((($Eval:Score$>=1.0) && ($Eval:Score$ < 4)),"LOW", "NA")))

    Db2 SQL translation of the SPARQL

    
         CASE
	      WHEN ($Eval:Score$>= 8) THEN 'HIGH'
                  WHEN (($Eval:Score$ < 8) AND ($Eval:Score$>= 4)) THEN 'MEDIUM'
	      WHEN (($Eval:Score$ < 4) AND ($Eval:Score$> 1)) THEN 'LOW'
                  ELSE 'NA'
         END
  4. Coalesce usage example. 
    SUM(IF(COALESCE($User Story:Resolved$, false), 
COALESCE($User Story:Complexity$, 0), 0))

    Db2 SQL translation of the SPARQL

    
     SUM(
         CASE 
             WHEN COALESCE($User Story:Resolved$, 0) = 1 
             THEN COALESCE($User Story:Complexity$, 0) ELSE 0 END)
  5. Complex coalesce usage example. 
    
              IF (SUM(COALESCE($User Story:Complexity$, 0)) = 0, 0, 
                  100 * SUM(
                  IF(COALESCE($User Story:Resolved$, false), COALESCE($User Story:Complexity$, 0), 0)
                 )/SUM(COALESCE($User Story:Complexity$, 0))
               )

    Db2 SQL translation of the SPARQL

    
         CASE WHEN SUM(COALESCE($User Story:Complexity$, 0)) = 0 THEN 0
            ELSE
            100 * CAST(SUM(
                CASE WHEN COALESCE($User Story:Resolved$, 0) = 1 THEN  
                COALESCE($User Story:Complexity$, 0) ELSE 0 END)
          AS DECIMAL)/CAST(SUM(COALESCE($User Story:Complexity$, 0))   AS DECIMAL) END
  6. Custom expression SPARQL and Db2 or Oracle functions
    Function Type SPARQL Db2/Oracle
    Average function AVG(<Replace with Attribute>) AVG(<Replace with Attribute>)
    Count function COUNT(<Replace with Attribute>) COUNT(<Replace with Attribute>)
    dateDiff function 
    lqe_fn:dateDiff('day', <Replace with Attribute>,   <Replace with
                  Attribute>)
    		 
    TIMESTAMPDIFF(16,<Replace with Attribute> - <Replace   with
                  Attribute>)*
    group_concat function 
    GROUP_CONCAT(DISTINCT <Replace with Attribute>;separator
                  ='<Replace with Separator>')
    		 LISTAGG(<Replace with Attribute>, ', ')
    Max function MAX(<Replace with Attribute>) MAX(<Replace with Attribute>)
    Min function MIN(<Replace with Attribute>) MIN(<Replace with Attribute>)
    Sum function SUM(<Replace with Attribute>) SUM(<Replace with Attribute>)
    Note: Oracle does not support TIMESTAMPDIFF and instead, you need to perform explicit conversion of Datetime Data Types. See https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG253 for an example.

Comparing query results between SPARQL and SQL

About this task

You can manually check and compare the SPARQL and SQL query results, but it is not a reliable way of establishing if the results are similar. There might be visible differences in the ordering of results, even when sorting is well defined. For instance, an equivalent result might be on a different page of the reports that are generated from SPARQL versus SQL. Even if the ordering of results is identical, visually checking many rows and columns is not a reliable method to compare.

Instead of manually checking results that are generated from SPARQL versus SQL, you can click Compare query results in the Advanced section of the Format results tab or Run report page to compare the query. The comparison service in Report Builder generates the SPARQL and executes it, generates SQL and executes it, and then compares the query results between SPARQL and SQL for semantic equivalence. Based on the comparison, a warning or successful message is displayed on the screen with Expand and Collapse buttons. When you expand the message, a table with number of results and the execution time for SPARQL and SQL queries is displayed. You can also download the comparison report in text format by clicking Download comparison report. This can be used to test if the LQE rs delivers equivalent results to the existing Jena TDB-based solution. However, for this to be possible, there are some requirements and constraints. For more information, see Limitations of the Lifecycle Query Engine relational store.

To further assist with LQE rs, a new feature was introduced allowing Report Builder users to add custom expression with both SPARQL and SQL. This allows users to further take advantage of the Comparing query results service. To take advantage of this feature, users need to enclose SPARQL or SQL with the #if_sparql or #if_sql and #endif_sparql or #endif_sql. See the following example:
#if_sparql
AVG(<Replace with Attribute>)
#endif_sparql

#if_sql
AVG(<Replace with Attribute>)
#endif_sql

Once custom expression contains both SPARQL and SQL, user would use validation service to update custom expression. Validation service validates either SPARQL or SQL based on the report query language selection. To validate both SPARQL and SQL, user would need to validate default query language first, for example SPARQL, and then update query language to SQL, and run custom expression validation. This would ensure both SPARQL and SQL are validated before report with custom expression or Compare query results is run.

Note: The same syntax that is used for supporting SQL and SPARQL in custom calculations can also be used for supporting advanced SQL and SPARQL report.