Best practices for report creation

Report creation is quite a complex process: additionally to the understanding of the particular business case and reporting needs, you have to think about how to achieve that goal using a particular reporting tool, flex.bi, 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.

Guidelines for report building

Recommenced way how to build an optimal report.

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

• If you can not decide if dimension should be used in rows and columns, always use 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 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 whenever possible.

• Disable nonempty option only for reports where projecting data, with forecast calculations and timeline reports when you need to display all members in rows, including those without actual measures.

6. In Pages, add as many dimensions as needed, as fas 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 (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)

• 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] works 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. 

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 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, accordingly to the Page selection, are retrieved.
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 (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.

Most important conclusions

1. Use nonempty when possible, especially, when there is more than one dimension in rows or the used dimension is large (like Issues). 
2. Do not use complex measures for filtering rows.
3. Use Hide empty option only for visualization, not optimization.
4. Evaluate/experiment what would give the best optimization results in each particular situation: for instance, multiple page selection to filter out rows (to decrease the set of rows) or executing calculation on a larger set of data. Two or more dimensions in the report rows, disabled nonempty, custom calculations together with multiple page selection is definitely a red flag for report performance.
5. 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.