All eazyBI for Jira eazyBI for Confluence Private eazyBI

Best practices for report creation

Report creation is quite a complex process: additionally to understand the particular business case and reporting needs, you have to think about how to achieve that goal using a particular reporting tool, eazyBI, in this case. First, you have to figure out which dimensions and measures would display the needed information the best; also, the report should be created in the most optimal way to ensure it is executed in a short time period. While there is no one fault-free way how to create reports, there are some best practice steps to follow how technically the report should be created. Following those steps is more important if the report is created for sharing with other accounts (as template report), as there could be accounts with different volumes and end-users.

On this page:

Guidelines for report building

The recommended way how to build an optimal report.

1. In Rows, select one or two dimensions, avoid selecting more

  • If you can not decide if dimensions should be used in rows and columns, always use a dimension with more members in rows.
  • If you think you need more dimensions in the report, remember about option Drill across: it can be used at any time in the final report to analyze data by any other dimension you need, you do not need to add this dimension in the report structure "just for case".
  • If you need to split only one measure by some dimension, then better use the option Drill into dimension for this measure instead of selecting it in the report rows.
  • Time dimension usually goes on rows as it allows you to use the timeline chart as well.

2. Use particular level members in rows and avoid expanded All level members; it is because of performance.

  • Avoid Remove option if the report might be shared with other accounts: a name of each particular removed member is saved in the report definition and could behave unexpectedly when imported; also, you won't see from the report itself why you do not see some members. Use filtering instead.

3. If more than one dimension is selected in rows, avoid using calculated members. Use the same dimension as a Page filter instead.

  • Some dimensions contain the same data, for example, Project level in Issue, Fix version, Affects version dimensions contain all the projects as Project dimension; use, in Pages, Project level of the same dimension as in Rows instead of Project dimension

4. If Issue dimension [1] is used in the report rows (data from Jira):

  • Avoid using calculated members in this dimension.
  • Instead of adding other dimensions to show issue type, assignee, fix version, etc., use Issue properties [2] in columns.
  • Do not use All Issues member in rows to create an opportunity for report users to see report data on issue level: let them use Drill into Issues or Drill through issues options.

5. Enable option nonempty with large dimensions on rows or several large dimensions on rows. The nonempty option works best in cases if you can significantly reduce the number of rows. For example, in the account of many projects and many sprints you can significantly reduce the sprints on rows based on project selection.

6. In Pages, add as many dimensions as needed, as far as you select one member from each of them [2]. Multiple pages selection [1] from several dimensions in Pages could cause significant performance problems, as each report calculation are executed per cell as many times as many combinations of members are selected in Pages (for example, if there are 3 dimensions in Pages, and 4 members are selected from each of them, then each measure in each cell would be executed 4x4x4=64 times; in the example below, the count of combinations is 2x1x1x1=2, and if there are no other complexities in the report, this is also quite OK)

Multiple pages selection [1] impact on performance is minimized


  • Selection of calculated member in Pages with aggregated members is also a multiple page selection; the same goes for calculated members with Except() as iterating through all members, except some, is happening.

7. Add Measures in Columns [1]. You may add them also in Pages and Rows. The arithmetical result won't change, but:

  • Measures in Pages are executed slower
  • Option Drill into dimension [2] works only for measures in columns
  • Standard calculations using option Add calculation [3] work only for measures in columns
  • Row filter by a measure works only for measures in columns

8. Whenever possible, use default options for additional data analysis:

  • use option Drill into dimension [2] if only one measure, not all, should be split by another dimension
  • use option Add calculation (trend, cumulative, comparison by time periods, etc.) [3] instead of custom calculated measure as they are optimized; though, some of statistical calculations (avg, min, max) still could be slow.

9. Filter rows by simple measures (Issues created, Issues resolved) and properties.

10. While building a complex report, disable "Auto refresh" button to avoid refreshing a report after each step. 

You may want to watch a training video on how those suggestions are used in different reports: Smart Reports without MDX. 

Report execution sequence

It is clear that all recommendations can't be applied in all cases. To better understand the steps described in the previous section and how to balance out business needs with technical recommendations and limitations, the following are the sequence of back-end actions and how each report is executed. Based on them it would be easier to figure out how to build a more complex report or what would help to optimize a particular report.

Each of the following steps is executed on the data set that is fetched during the previous step (each step narrows down the data).

  1. Retrieving report Rows accordingly to the Page selection
    • if two or more dimensions are used in the report, then all combinations of all dimension members are retrieved
    • when nonempty option is selected, then only rows (members or their combinations) having any measure, according to the Page selection, are retrieved. However, the nonempty option itself could work slow. You would like to use it only to significantly reduce the number of rows based on Pages selection. eazyBI enables nonempty only for the dimension Issue on Rows by default. 
  2. Filtering rows by FIlter rows (set on columns)
    • filters by properties or default measures (like Issues created, Issues resolved, etc) are faster.
  3. Calculating values for all measures in the report
    • any measure is calculated as many times per each cell as many member combinations are selected in Pages (for example, if there are 3 dimensions in Pages, and 3 members are selected in each of them, then each measure in each cell is executed 3x3x3=27 times)
    • a custom calculation that works in a small account and simple report (single Page selection, one dimension in rows) could time out in a more complex report with more data.
  4. Applying Hide empty option, to hide rows/columns without values.

Conclusion

There are limitations on how far you can optimize the report using standard options. If this is not enough, think about precalculating values or importing fewer data into the account. Watch the video Behind complex filters and Managing eazyBI accounts.

Some guidelines on report creation and working on slow reports are discussed in the training video Help my reports are too slow. 

If you need some help to see if there is room for improvement in the reports reach out to support@eazybi.com and share the report definition.