Assuming your data environment is well designed, there is a lot you can and should do to optimize your content to ensure fast reports and dashboards. We break this down into 4 steps:

1. Managing your Metadata Layer
2. Enabling Caching
3. Dashboard Design
4. Report Design

Ensuring that you have a well-designed meta-data layer is a critical best practice.

The meta-data layer sites between the UI that end-users use to create reports and the query that is sent to the database. If well designed, your meta-data layer can support a huge amount of self-service queries and dashboards with high performance.

There are a number of recommended strategies for improving metadata performance:

Structure: Ensure the data model you design in the meta-data layer is well structured. If the model is too complex, consider redesigning your database layer. A meta-data layer does not fix poor database design. This includes table structure as well as complex calculations that might be better pre-calculated than having to run on the fly for every query made.

Virtual tables: Replace complex joins or logic with virtual tables that can call specific database functions to ensure the query created is optimized for the type of database you are querying. This level of SQL control enables you to manually push down the best query possible.

Query: Ensure query optimization features are turned on. This means that the query generated will only query the fields and tables selected in the query, rather than querying every table and join in the model.

Hardcode filter: Hardcode filter conditions into the model to limit the amount of data that can be brought back. If the data you have in your database is far larger than your end-users need to query, limit their queries by filtering out data from your view.

Row limits: A row limit restricts the amount of data that will be returned in the query. Also known as a fetch limit, this stops your end users from creating queries that return millions of rows to the browser, but will allow them to query that data and bring back aggregated results.

TBA

The performance of a dashboard is directly correlated to the number and complexity of the queries needed to run against a database to support the tables and charts on that dashboard.

Follow this simple rule: The more reports you have on a dashboard, the more queries that have to occur. As a result, when you design your dashboard, you should always consider the performance at the same time.

The best practices for optimizing your dashboard design include:

Use subtabs: If you have many reports that you want to add to a dashboard consider the use of subtabs. This enables you to split what would be one big dashboard into multiple parts. Create smaller subject areas so that the dashboards make logical sense. Super complex dashboards are hard to use so when using subtabs, not only do you improve performance but you also make your dashboards easier to consume.

Use composite reports: A very good way to reduce the number of queries is to create multiple visualizations off a single query and then place those onto your dashboard. Given that most dashboards are designed around a specific functional area, the benefit of this is that only a single query is run, but the results can be used in many ways.

Cache filter values: Use these to limit the initial data set to be returned to the dashboard.

Default filter values: Use these to limit the initial data set to be returned to the dashboard. Allow the end user to manually, via filter widgets, extend the scope of analysis if they need to. This makes the initial load fast and caters to the most common use case for the end user.

Use drill through for detailed reports: Reports that contain thousands of rows can slow down your dashboard. Often your end users may not initially need access to row level data so use drill through functionality to allow your users to access the smaller slices of detail as needed.

Plan the reports you will need to run your dashboard, using the following best practices.

Using advanced visualization features, like variance and set analysis: This helps you to create simpler queries that reduce the workload on the database. For example comparative analysis such as comparing sales this year to last year is better done at the visualization layer rather than creating complex subqueries that go to the database. .

Hardcode filters to limit the amount of data returned: If your dashboard is operationally focused then you may only ever need 30 days or less of data. Therefore, filter all your reports to only ever return a maximum days worth of data you need.

Create multiple visualizations off a single report: As mentioned above this is one of the best and simplest ways to optimize your queries.

By following these recommendations, you ensure performance of content in your dashboards and reports is properly optimized.