Best practices for creating reports with Report Builder

General reporting practices

Make each report fit for purpose

To make the report fit for the purpose, consider the following tips:

• Define the user of the report and what they want to use it for.
• Define the decisions or actions that the users need to take.
• Gather the information that is required to create the report. Scope your report to include only the needed data, so consumers can quickly digest the results.
• Avoid general-purpose results intended for use by different consumers with different needs. Such reports tend to include a superset of data, which can result both in a slower-running report, and more difficulty for user to filter out the data they don't need. If your report is complex and includes large amounts of data, consider decomposing it into a set of smaller, more targeted reports that can be grouped on a dashboard or consumed independently for specific needs.

Keep the report concise, returning only the data you need

Ensure that the report is focused on the data that's important for that purpose. Structure the reports to only include the data that fits the requirement and highlights the areas where an action is needed. For example, if you need to tackle outstanding defects, do you really need data on all the closed defects?

Reports that return large quantities of data make it hard for consumers to quickly scan and locate the important information, especially in tables that cross multiple pages. Including more data than necessary can impact report performance and the consumer's ability to use the information.

Identify the most appropriate reporting technologies at your disposal based on your needs

JRS and Report Builder help you create traceability and statistical reporting. Report Builder generates interactive tables and graphs, and supports options to export results or the query itself, to different formats.

Other tools such as IBM Engineering Lifecycle Optimization Publishing (PUB) can generate formatted documents. For some reports, a document might be easier to use than an interactive online report. PUB also provides capabilities different from Report Builder, including stylesheets to control format on the page, document comparison, and scripting. For more details on PUB, including the web-based Document Builder interface for users to generate documents, see Engineering Publishing documentation.

Manage what you include on dashboards

Minimize the number of reports and widgets on common landing dashboard pages, so the system isn't constantly running those reports as each user opens the dashboard. To manage dashboard load time and load against your reporting server, put few widgets on each tab. To help the users, you can also group the widgets by user or purpose.

Ideally the dashboard reports return a relatively small result set. Put reports with large result sets or long run times on a separate tab, so they are run only as needed. You might even exclude some reports from the dashboard catalog, so users run them only through the Report Builder interface, or schedule them.

With Report Builder version 7.0.3 or later, you can reduce the loading time of the reports on the server by caching them. With this feature, you can view performant dashboard reports after a report is run for the first time. The default duration of caching is set to 10 mins.

Establish experts in your organization

Report Builder allows users to write their own reports. It's important that all report creators understand the reporting best practices to ensure that the reports they create use appropriate amount of resources. If you can establish one or more experts within your organization, users can contact those experts for additional guidance or assistance in improving their reports.

Best practices for building efficient reports in Report Builder

Keep traceability paths as simple as possible

Large, complex traceability paths can increase the time and resources that are required to run a report. Consider the following practices, wherever possible:

Avoid optional relationships

The optional relationships take longer to evaluate, especially if they lead to other relationships and multiple paths to trace. The more optional relationships that you include, the more paths there are to consider, and typically that causes the report to run longer or even time out.

As an alternative, use two paths: one where the relationship doesn't exist, and one where it is required. You can then further extend the traceability for the case where the relationship is required. If it is important to include an optional relationship, put it at the end of the traceability path to reduce the number of paths to evaluate.

Minimize many-to-many relationships, and one-to-many relationships

The many-to-many relationships, and one-to-many relationships take longer to evaluate. You might sometimes run into issues with conditions that are applied at an early point in the path, which no longer applies later.

Append results instead of merging

For multiple paths, appending results is faster than merging, which requires additional operations. But there are cases where you need to merge results. It is better to merge two paths than to use an optional relationship.

If your report requires complex traceability paths, keep it as simple as possible while still achieving your goals. Test your reports for performance. If you encounter problems, simplify your paths and add back on. Consider whether there are alternative paths to access the same data. Some reports might take longer to run, in which case you might consider scheduling them to run during times of low server usage. For more information, see Tips for working with Report Builder traceability reports.

Set conditions to limit the result set

Use conditions to filter the set of data that is returned. You can save the conditions as part of the report, or allow (or require) users to change them at run time.

• In traceability paths, apply conditions as early in the path as possible to limit results to be evaluated later in the path.
• Minimize exclusion conditions such as is not or none of, which take longer to process. Where possible, reframe the condition to search for the values that you do want or that do exist.
• Minimize combining conditions with OR. It takes longer to process an OR condition because it needs to be applied near the end of all processing. Report Builder can break down AND conditions to run more efficiently.
• For time series or trend reports, limit the time interval to manage the size of result sets. Larger time intervals increase the load on the server.
• To scope results to a specific component or set of components for versioned artifacts that use Global Configuration Management, set a condition for the artifact type based on its component value.
  Note: The generic OSLC artifact type (for example, Requirement) does not include a component attribute.

Eliminate data columns that you don't need to see

On the Formatting tab, remove data columns that you don't need in the final report. For example, you might not need to see the project area value, or if you have a condition to show a single specific status value, you might not need to show the value in the report itself. If you remove a column with condition data, the system still evaluates the condition to run the report, but it doesn't need to return and render that data.

Practices related to the Report Builder

Choose the right data source

The first step in Report Builder is selecting your data source for the report. Depending on what is deployed in your environment, the list might include the Data Warehouse (DW), LQE, LQE scoped by a configuration, or one or more configuration-based data sources.

If you are using Global Configuration Management, you must use LQE scoped by a configuration or a configuration-based data source to report on versioned artifacts; use LQE to report on configurations and components themselves. For unversioned artifacts, including work items, and all artifacts that are not using Global Configuration Management, the DW provides a richer set of time-based metrics.

Start simple and add complexity

Reports can get complicated as you build conditions and relationships. A good practice is to start with a simple version of the report you want, and ensure that it runs correctly. Then gradually layer on relationships, conditions, calculations, custom expressions, and so on, verifying at each step that your results are as expected.

Note: The Report Builder preview shows only a sampling of data. You need to run the report to view the actual results.

Consider the scenarios when filters are required while running the report

While running a report in Report Builder, filters are required every time when user runs the report in the following scenarios:

• When the report is created with Lifecycle Query Engine scoped by a configuration data source.
• When selected Unlocked + required radio button when adding or editing conditions.
• When selected Unlocked + required from Lock icon of conditions.

Use filters to narrow the scope of results

Use project scoping, specific artifact types, relationships, and conditions to limit the amount of data returned. Retrieving large amounts of data, then applying filters or joins, can increase the run time and the memory demands.

• Set the project area scope where appropriate.
• Use traceability paths and conditions to scope the artifacts returned.
  • Use container-like relationships, for example, to scope requirements by a module, test results by a test plan, work items by an iteration.
  • Constrain scope as early as possible in the traceability path, which limits the number of results to evaluate for the rest of the path. You can continue to add conditions for artifacts later in the path.

Choose an artifact type

You can choose from the following artifact types:

Top-level and nested artifact types

The top-level artifact types correspond to OSLC types. Nested artifact types are merged artifact types from one application.

If you select a top-level (OSLC) type, you might see unexpected results in your report if multiple applications publish that type of artifact to LQE.

For example, if you choose the OSLC Requirement type to find all DOORS® Next requirements, your report might also return Requirement artifacts that are published by the EWM application. To exclude EWM-related artifacts from your report results and report only on DOORS Next requirements, select the nested Requirement type, and in the Limit scope section, select only DOORS Next projects.

Combined artifact types

Merged (non-OSLC) artifact types are combined based on their name.

• If two project areas in an application define semantically different types but use the same type name, Report Builder assumes that they are equivalent.

  For example, if ETM project area Proj1 defines an artifact shape type QM Test Case and ETM Proj2 defines a QM Test Case that has different properties, the hierarchy in the Choose artifact section shows only one QM Test Case type.

• If you select a merged type for your report, it shows resources from both projects, which might include unexpected results.

To avoid this problem, in the Limit scope section, select only the projects that contain the type that you want to report on.

Differences in the DN application:

• DOORS Next merged shape types are shown under both Requirement and Requirement Collection.
• In DOORS Next, a type can be used for both a collection and a regular artifact. A specific merged type that is shown in both places is the same type. The generated report does not infer which hierarchy you chose it from. The system examines all the artifacts that correspond to that merged type, whether the underlying artifact type is Requirement or Requirement Collection.

Identify equivalent but conflicting properties in merged shapes

Conflicting properties

Conflicts among properties can occur when the system (LQE + Report Builder) merges project-specific artifact shapes. These conflicts are indicated by a question mark in the Add Attributes to the Report section of Report Builder.

Visual cue

Hover over the question mark to identify and select the correct property (attribute). The tooltip typically includes the project name and indicates the reason for the conflict.

Effect on cross-project reports

If you add a property that has a conflict, your report might return unexpected results. These properties prevent cross-project reporting. The report results for that type are limited to the resources in the corresponding project.

Identify equivalent properties across projects in an application

Different projects might use different names for an equivalent property. For example, in DN projects, a Feature in Project 1 might use a property that is called Priority, and in Project 2 that same property might be called Urgency.

Report Builder considers properties as equivalent when you specify the same RDF URI in the projects that use it.

1. Complete one of the following steps:
   • DN application: Click Administration menu > Manage Component > Properties > Artifact Types.
   • ETM application: Click Administration menu > Manage Project Properties > Custom Attributes.
2. For an artifact type, specify the RDF URI for that property and then click Save.
3. Repeat for each project that uses that property.

If the property is an enumeration, in each project where the values are equivalent, you must also specify an RDF URI for each value in the enumeration.

For more information, see Best Practice: Enable Users to Specify URIs for Custom Attributes and Values.

To build a graph, first get the accurate table

Use the table view to ensure that you have the right data and any necessary calculations before you switch to the graph design view. It is easier to debug data issues from the table view.

Use meaningful labels

For both tables and graphs, the default labels that are generated by Report Builder might not be meaningful to report consumers. Where appropriate, replace labels for data columns with more meaningful text. Provide useful labels for graph axes and legends. For graphs, ensure that colors are easy to differentiate for better accessibility.

Understand your data model

The Data Warehouse data model is reasonably documented in the IBM Documentation; you can also use database tools to query the tables. LQE builds a dynamic schema based on the data it indexes from your organization. Understand how your organization defines artifact types, attributes, and relationships. Sometimes there is more than one approach to reporting on artifacts in a relationship. Consider the following examples of possible impacts:

• Base and module artifacts in DOORS Next. Because base and module instances have unique URIs, you can get multiple results for a single artifact. Conversely, if you filter based on module, an artifact or its metadata might be included or excluded. Use a consistent strategy for modules, linking requirements, and reporting.
• Scoping in relationship paths. A scoping or filtering condition set on one artifact does not necessarily scope artifacts later in the traceability path. For example, you might query a QM test plan with all of its QM test cases, and all the test case results. However, the test case might have results that are associated with multiple test plans; such test cases would all be returned because the relationship is valid. So, understand the relationships between the artifacts and where scope might change, and build your path. In this example, you would query the test plan and its results, which are associated to only one test plan, and then the test cases (a result has only one test case).

Refresh the data after the change

If you are building reports in a pre-production environment, you might be creating data as you build your report. If you are using the Data Warehouse, the data will only be available after the next data collection job completes. If you are using LQE, you might need to manually refresh the metamodel to reflect changes to metadata.

To modify the generated query, use custom expressions or make a copy

Sometimes, you might want to further manipulate the data that is returned by Report Builder, for example to change the display format or perform more complex calculations. Where possible, use Custom Expressions to include custom SQL (for DW) or SPARQL (LQE) operations within the Report Builder UI. If that is not sufficient, you can edit the generated query directly.

Attention: If you are editing the generated query directly, you can no longer use the Report Builder UI to modify that report. The filtering options for the report are constrained, and you won't be able to drill down within the report results.

Consider the following guidelines before you edit the generated query directly:

• Do as much editing as you can in Report Builder first.
• Save your report, and make a copy.
• In the copy, edit the query in the Advanced section.
• If you are making extensive changes, ensure you narrow scope as soon as possible in the query.
• Ensure that you test your query thoroughly, with representative result size, and optimize for performance.

Test report performance

Test the performance of the report with a representative sample size, especially for the reports with large result sets or time series, complex traceability or calculations. You might find ways to optimize the report to improve performance, or manage how the report is run (for example, scheduled and not included on a dashboard).

Reports for multiple configurations on the same dashboard

Dashboard report widgets default to using your current configuration context. However, you can change that default. To show the same report for multiple configurations at once, you can add multiple instances of the report widget, and set the filter of each one to a different specific configuration context. Ensure that you also consider the overall content of the dashboard tab. You can also use PUB to create a template that iterates through multiple configuration contexts to collect the specified data for each.

Use SPARQL query to access link validity information

You must write SPARQL queries to access and report about link validity. In the LQE application, you must have permission to read data in the TRS 2.0 for Link Validity Resources data group. If your report does not return link validity information, ask an application or project administrator to grant you this permission.

Reporting about configurations

You can report on global configurations by using the Report Builder interface. To report on data about local configurations themselves, you must write SPARQL queries in the Advanced section of Report Builder. Use the LQE scoped by a Configuration data source for your report.

For example, to find out which local configurations refer to a specific requirement, create a Report Builder report about requirements, and replace the generated query with a custom SPARQL query that extracts data from the configurations directly.

Otherwise, Report Builder exposes configurations only in the Choose a Configuration menu when you run a report that uses the LQE scoped by a Configuration data source. Because you can pick only one configuration from this menu, the query that Report Builder generates cannot find the information that you want from all configurations.

Edit SPARQL queries to report on the artifacts

To report on some types of artifacts or project elements, you must manually edit the query for your report in the Advanced section of Report Builder. After you save your changes, you can add only query parameters and columns to that report in Report Builder. You can no longer modify the links in Trace artifact relationships section.

Use unique names for attributes

Sometimes a set of projects uses the same custom attribute, even if the attribute has a different name across the projects in an application. Across projects, when you specify the same RDF URI for the attribute, Report Builder shows only one instance of it. If the attribute has a different name across projects, Report Builder shows the first attribute name that it discovers. For example: Project 1 might have an attribute called Priority; Project 2 might have an attribute called Urgency, and both attributes have the same RDF URI. In Report Builder, the attribute might be listed by either name.

Use the dashboard widgets to report the specific ELM application

The data warehouse and the LQE does not contain the following artifact types and information. To report on such artifact types and information with Report Builder, use the dashboard widgets for the specific ELM application.

EWM

Plan resources, work item, comments, and complexity.

DOORS Next

Change set information; review or module hierarchy information; tags.

Tips for administrators

Ensure appropriate LQE server size

The LQE server operates as both data source and query executor, and requires adequate resources to handle report demands and indexing data updates. For details on sizing your LQE server, see Best practices for configuring LQE for performance and scalability.

Monitor LQE performance

Use the Health page in the LQE application administration and the other ELM monitoring capabilities (including MBeans) to monitor the health and performance of the LQE server. You can identify reports and queries that are consuming system resources, and sometimes, stop them. The capabilities vary depending on the ELM version that you are using. For more information, see Monitoring, Monitoring Jazz applications using JMX MBeans, and Monitoring LQE using MBeans. Administrators can also limit query results and use other LQE properties to manage performance. For more information, see LQE Properties.

Set property to optimize for large project areas

When you are reporting against large project areas, Report Builder (LQE) can optimize the query differently to improve performance. To enable this runtime optimization, edit the conf/rs/app.properties file and set large.project.query.optimization=true. Save the file and restart JRS to apply the change. You don't need to modify any reports.

Minimize the use of work item tags with partial matching conditions such as contains, does not contain, starts with, and ends with. The partial matching conditions require relatively slow string comparisons of all tags that affect the performance of report.

Note: This property is supported as of JRS version 7.0.1 and later. It is not available in earlier versions.

Define your type system consistently (with URIs) to facilitate reuse

If you want some reports to be reusable, so you can run them for different organizations with the same need. For example, different teams can run the Outstanding Defects report for their set of artifacts. In that case, those organizations need some consistency in the attributes and attribute values they use, and also that you define the reports to support reuse, instead of tailoring them to the values of a specific team.

If you are using Global Configuration Management, it is critical to define URIs for artifact types, attributes, and attribute values, to improve ease of building reports and reusing across teams. For more information, see Maintaining your DOORS Next type system in a configuration-management-enabled environment. If the teams do have variant type systems or specific needs, consider defining separate reports. Balance between reuse and fit for purpose, team consistency vs team variance.

Practices related to Engineering Lifecycle Management capabilities