Report Builder Best Practices

Authors: KevinCornell, ErnestMah, TimFeeney, KathrynFryer
Build basis: IBM Engineering Lifecycle Management 6.x and later

Page contents

This page describes best practices for building reports with the Jazz Reporting Service and Report Builder (RB) - although some are more general practices applicable beyond these specific tools.

General practices

Make each report "fit for purpose". Clearly define who will consume the report, and what they will use it for. What decision or action do they need to take? What information is required? 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; those 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.

Manage by exception. An extension to the previous practice, focus on the data that's important. Structure reports to highlight where action is needed. For example, if you need to tackle outstanding defects, do you really need data on all the closed defects? As mentioned above, including more data than necessary can slow both the report and consumer performance.

Consider all the reporting technologies at your disposal, and which is most appropriate for your needs. Reports can be tables, graphs, documents, combinations of all of those things. Consider what format is appropriate for consuming the data produced. Consider whether or how the report must be shared or stored. Report Builder generates interactive tables and graphs, which can be exported or saved in different formats. Other tools such as IBM Engineering Lifecycle Optimization Publishing (PUB) can generate formatted documents, and provides some capabilities different from Report Builder; you can also export results from Report Builder into PUB. PUB also includes a web-based Document Builder interface where users can generate, schedule, and share document-based reports.

Manage what you include on dashboards Minimize the number of reports and widgets on common "landing" dashboard pages, or those with high or frequent use, 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 a small number of widgets on each tab; to help users, you can group them by consumer or purpose. Ideally 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 via the Report Builder interface - or schedule them. can you put a widget that shows results from a scheduled report?

General practices related to ELM capabilities

Consider scheduling and sharing reports Both Report Builder and Document Builder provide the capability to schedule reports and store the generated output. Schedule reports that consume more resources (due to large result sets, complexity, and so on) to run during times of lower system demands. One example is "the Monday morning status report" that everyone on the team wants to see; schedule it to run Sunday night so it is both ready and the team all see the same results from the same point in time. Note: The Lifecycle Query Engine (LQE) server does need some occasional quiet times where no queries are running, so it can complete all write-to-disk operations.

Define your type system consistently (with URIs) to facilitate reuse You likely 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 as well as reusing across teams. (See also link to articles on managing type systems. If 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. Note: as of 7.0, you can define special LQE data sources based on a particular configuration definition. If you do have teams using divergent type definitions, you can define a special data source to use for building reports specifically for their use. For more information, see KC topic.

Practices for building reports

Choose the right data source The first step in Report Builder is selecting your data source; depending 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 NOT using global configuration management), the DW provides a richer set of time-based metrics. For more information on which data source to choose, see LQE vs DW blog post? better?

Use filters to narrow 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.

Where appropriate, set the project area scope.
If you are scoping results based on a module or test plan, consider starting from that "aggregate" artifact. TRUE??
Set and save conditions, or require the user to set them at run time.
Minimize exclusion conditions (where the value "is not" or "none of"), which take longer to process; where possible, reframe to search for the values that you DO want or that exist. * 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.
For versioned artifacts (using global configuration management), to scope results to a specific component or set of components, set a condition for the artifact type based on its Component value. Note that the generic OSLC artifact type (for example, "Requirement") does not include a Component attribute.

Keep traceability paths as simple as possible Large, complex traceability paths can increase the time and resources required to run a report. In general, wherever possible:

Minimize optional relationships and relationships that do "not exist".
Minimize many-to-many relationships, and one-to-many relationships.
Append results instead of merging.

When you do need to use more complex traceability, test your reports for performance. Some reports might take longer to run, in which case you might consider scheduling them to run during times of low server usage.

Eliminate data columns 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.

To build a graph, get the table right first Use the table view to ensure you have the right data and any necessary calculations, before you switch to the graph design view. It is usually easier to debug data issues from the table view.

Use meaningful labels For both tables and graphs, the default labels 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 colours are easy to differentiate (and remember those who are colour-blind).

If you need to modify the generated query In some cases, you might want to further manipulate the data 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; however, be aware that this means you can no longer use the Report Builder UI to modify that report, filtering options for the report will be constrained, and you won't be able to drill down within the report results. If you must edit the generated query directly:

Do as much 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 you test your query thoroughly, with representative result size, and optimize for performance.

Test report performance Especially for reports with large result sets or time series, complex traceability or calculations, test the performance with a representative sample size. 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).

For LQE 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 as well as indexing data updates. See link to LQE sizing for details on sizing your LQE server.

Monitor LQE performance Use the LQE Health page and the provided MBeans to monitor the health and performance of the LQE server. You can identify reports and queries that are consuming system resources, and in some cases, stop them. Capabilities vary depending on the ELM version you are using. See BEST REFERENCE HERE?? for more details.

Set property to optimize for large project areas (6.0.6 and later) When reporting against very large project areas, Report Builder (LQE?) can optimize the query differently to improve performance. To enable this run-time 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. Note: This property is supported as of 6.0.6 iFix15, 6.0.6.1 iFix9, 7.0, or later. It is not available in earlier versions.

Tips for reporting on configurations

Reports for multiple configurations on 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.

OLD CONTENT (reference only - to be removed):

Optimize and tailor the queries you run most often. If you require reports that return a large number of results, run them during off-hours or when server usage is light.

When users create reports (and Report Builder generates the SPARQL), Report Builder attempts to optimize the generated query based on the selected types used in the report and the complexity of the filters. However, a user can still generate long running reports.

Data aggregation reports and some time series or trend reports can take longer to run, depending on the amount of historical data in the data source. Using larger time intervals increases the load on the server, although repository size can also be a factor. Limit the time interval for trend reports, and ensure you test the performance in a “worst-case scenario”.

Tips on Report Builder (RB) queries/filters:

Use project scoping or filters to limit amount of data returned. For example writing a report to trace all work items to test cases to requirements will take a long time if the user does not use project scoping or does not define any filters (such as work item type).
Traceability reports that use optional instead of required relationships will take substantially longer to run.
In addition, if the reports do sorting on a column, the generated query uses the ORDER BY statement. If there are a lot of resources to consider in the query (e.g., more than the maximum number of allowed results), the query engine in LQE has to get all of the possible results, sort them and then apply the limit restriction. Thus sorting in queries can take substantial time.