Configuring Report Builder to use the relational database connection with existing data warehouse

Configure Report Builder to use the relational database connection for the following topologies.

Before you begin

Ensure that there are existing data sources such as Data Warehouse, Lifecycle Query Engine, and Lifecycle Query Engine scoped by a configuration, depending on whether you have projects where configurations are enabled or not.

If no data source exists, Report Builder automatically creates one. When you click Connect to data sources on the Administrator page,Report Builder discovers the following three data sources for each Lifecycle Query Engine registered with Jazz Team Server (JTS):

  1. Lifecycle Query Engine (/sparql)
  2. Lifecycle Query Engine scoped by a configuration (/sparql-variable-config)
  3. Data Warehouse

Topology 1: Two separate Lifecycle Query Engine instances, one for Jena and one for relational store (rs)

About this task

In this topology, an additional Lifecycle Query Engine rs instance has been added. Create additional data sources in Report Builder and configure them to point to the data warehouse database set up for the Lifecycle Query Engine rs instance. This helps ensure that the data warehouse and data warehouse (indexed by: Lifecycle Query Engine) options are available for the existing Lifecycle Query Engine and LQE scoped by configuration data sources. After configuration, archive the newly created data sources and update the existing data sources to point to the data warehouse database for the Lifecycle Query Engine rs instance.

This setup allows users to switch between SPARQL and SQL for existing reports and compare the results to ensure the results are as expected. Reusing the existing data sources is necessary because all existing SPARQL reports are linked to the data source by its ID. Therefore, to enable all existing SPARQL reports to use Lifecycle Query Engine rs (SQL), the data source that is used by these reports must be configured to connect to data warehouse setup for the Lifecycle Query Engine rs instance.

LQE rs architecture

Procedure

Configure two LQE instances, one points to LQE Jena triple store and one points to LQE relational store (rs), as shown in the preceding architecture diagram.

  1. On the Data sources page, click Connect.
    The following data sources are created:
    • LQE 1
    • LQE 1 scoped by configuration
    If additional data sources are not created, then create the data sources:
    1. To create a nonconfig scoped Lifecycle Query Engine data source, complete the following steps:
      1. Click Create a data source.
      2. Name the new data source as Lifecycle Query Engine rs.
      3. Make sure that the data source type is set to Lifecycle Query Engine SPARQL Endpoint.
      4. Set the SPARQL data source URL for the Lifecycle Query Engine data source on the Report Builder server as https://<server name>:<port>/lqe/sparql.
      5. In the Authorization Type field, select Jazz Security Architecture SSO.
      6. Set the Lifecycle Query Engine relational store to Data Warehouse.
      7. Click Save.
    2. To create a new config scoped Lifecycle Query Engine data source:
      1. Click Create a data source.
      2. Name the new data source as Lifecycle Query Engine rs scoped by a configuration.
      3. Make sure that the data source type is set to Lifecycle Query Engine SPARQL Endpoint.
      4. Set the SPARQL data source URL for the Lifecycle Query Engine scoped by a configuration data source on the Report Builder server as https://<server name>:<port>/lqe/sparql-variable-config.
      5. In the Authorization Type field, select Jazz Security Architecture SSO.
      6. Select Require a configuration to run a report.
      7. Set the Lifecycle Query Engine relational store to Data Warehouse (indexed by: Lifecycle Query Engine 2).
        Note: Ensure that you select the Data Warehouse via Lifecycle Query Engine rs, rather than the Data Warehouse via Lifecycle Query Engine.
      8. Click Save.
  2. Rename the newly created Lifecycle Query Engine data sources that are Lifecycle Query Engine 1 and Lifecycle Query Engine 1 scoped by configuration.

    Reusing the existing data source configurations to point to the Lifecycle Query Engine rs data warehouse (DW) enables switching between SPARQL and SQL in existing reports.

    This setup allows you to compare report results across both query types and verify that the outputs are consistent and as expected. Because existing SPARQL reports are tied to data sources by their data source ID, the same IDs must be reused and reconfigured to connect to the Lifecycle Query Engine rs SQL warehouse to support SQL-based reporting.

    1. To edit the non config scoped Lifecycle Query Engine 1 data source:
      1. Click the Lifecycle Query Engine data source in the list to connect:
      2. Rename the data source as Lifecycle Query Engine rs.
      3. Set the Lifecycle Query Engine relational store to Data Warehouse, if not set already.
      4. Click Save.
    2. To edit the config scoped Lifecycle Query Engine 1 data source:
      1. Click the LQE 1 scoped by a configuration data source in the list.
      2. Rename the data source as LQE rs scoped by a configuration.
      3. Set the Lifecycle Query Engine relational store to Data Warehouse that is indexed by Lifecycle Query Engine rs if not set already.
      4. Click Save.
  3. To edit the existing Lifecycle Query Engine data source:
    1. Set the Lifecycle Query Engine relational store to Data Warehouse.
    2. Save
  4. To edit the existing Lifecycle Query Engine scoped by configuration:
    1. Set the Lifecycle Query Engine relational store to Data Warehouse that is indexed by Lifecycle Query Engine rs
    2. Save.
  5. Archive the newly created Lifecycle Query Engine rs and Lifecycle Query Engine rs scoped by configuration data sources. Archiving ensures that the data source remains available for back-end use but is hidden from users when they create or modify reports.
    Do not delete the data sources. Retaining them ensures that you can reload the data sources in Jazz Reporting Service (JRS) if needed.
    Note: You can delete the data sources that are used by one or more reports.
  6. Refresh the type system models for all these data sources.

Topology 2: Single Lifecycle Query Engine instance that points to Jena and relational stores

Reconfigure the existing Lifecycle Query Engine and Lifecycle Query Engine scoped by configuration data sources to use the relational store. This configuration enables existing SPARQL-based reports to run SQL queries against the Lifecycle Query Engine relational store, allowing for consistent reporting and improved performance. This configuration also provides reports the option to run against Jena triple store with SPARQL query. You can hide the SPARQL options from Report Builder by selecting the SPARQL is disabled checkbox in the data sources. If the TRS processing in Lifecycle Query Engine Triple Store (TDB) is disabled, then the SPARQL is disabled checkbox is automatically selected.

Procedure

  1. In Report Builder, click the Administration icon and click Data Sources.
    Report Builder discovers the following 3 data sources for each Lifecycle Query Engine registered with JTS:
    1. LQE
    2. LQE scoped by configuration
    3. Data warehouse
  2. On the Data sources page, edit the existing Lifecycle Query Engine data sources.
    1. To edit the nonconfig scoped Lifecycle Query Engine data source:
      1. From the list of data sources, click the Lifecycle Query Engine data source.
      2. Set the Lifecycle Query Engine relational store to Data Warehouse.
      3. Click Save.
    2. To edit the config scoped Lifecycle Query Engine data source:
      1. From the list of data sources, click the LQE scoped by a configuration data source.
      2. Set the Lifecycle Query Engine relational store to Data Warehouse.
      3. Click Save.
  3. Refresh the type system models for all these data sources.