Best Practices for Performance Optimization of Dashboards in Dundas BI

Performance optimization of dashboards has got to be one of the most discussed topics between myself and my customers. This is the typical flow of questions I receive surrounding this topic:

• Why does my dashboard take so long to load?
• Have I been doing something wrong?
• What are the best practices for performance optimization in Dundas BI?

Well, in this blog, I am going to try and address most of the common mistakes made by developers in regard to dashboard performance, and how to mitigate them.

In order to understand how to optimize your dashboard, you need to first be familiar with the data flow in Dundas BI. The data flow is as follows:

1. Connect to your database using the Data Connector
2. That Data Connector is then used to build different Data Cubes. Data Cubes are the Multi-Dimensional cubes in Dundas BI. You can perform ETL tasks on these and use different storage types. This is one of the most powerful features, however, it is best to talk about cubes in a different blog.
3. Metric Sets are one way of analyzing your data in Dundas BI. You can think of each Metric Set as a KPI on your dashboard. You can create multiple Metric Sets from each Data Cube, however, please note that one Metric Set can have only 1 Data Cube in the background.
4. Different Metric Sets can be combined to build dashboards and the same Metric Set can be REUSED on the same dashboard or different dashboards.

I have tried to visualize what I am saying in the diagram below:

You’re probably wondering, “Data Connector -> Data Cube -> Metric Set -> Dashboard! Wow, that is a cumbersome way to get my data visualized on a dashboard. Is there a way around?” The answer is, yes, there is. You can simply drag-and-drop fields directly from your Data Connector onto your Dashboard.

What happens then? Well, a Data Cube and a Metric Set are auto-generated in the background.

This is much simpler and does the job. But imagine this; each Metric Set is a query to your database. So, each auto-generated Metric Set is also a query to your database. This starts having an adverse effect once you start adding multiple Metric Sets.

So, my first rule – or my first Best Practice tip – would be:

1) Create a Data Cube and Re-Usable Metric Sets (if you have a lot of Metric Sets)

Now that we have a basic idea of the data flow, let’s dive into each of the individual components and see how each of these contributes to the performance of your dashboard!

Data Cubes

A Data Cube in Dundas BI is an ETL like process that allows you to join and manipulate data into a cube:

There are a couple of ways to optimize the cubes to increase the query performance. One of the most important, is to only use the columns you need! I cannot stress this enough and I see many people ignoring this basic rule. If I need just one column from a table, I should only select the 1 column in my transform.

This brings me to my second point:

2. Only Choose the Columns you Need

Storage Type

When you create a cube, by default, the storage type is None, which essentially means that any time someone accesses a dashboard that is linked to this cube, the underlying database will be queried.

While this is a good solution for those wanting to monitor real-time data, a lot of dashboards will only contain metrics that do not change frequently, due to database design. A lot of companies do not want queries hitting their live databases, since that could reduce overall system performance.

In that case, you can choose a storage type to possibly increase data retrieval performance and reduce the load on your database server. There are two storage types available from the toolbar when editing a Data Cube:

• The warehouse storage type lets you store or cache data in the Dundas BI Warehouse database.
• The in-memory storage type stores data in server memory for even greater performance gain potential.

You can create a schedule for the Data Cube to be built outside office hours if required. You can have the job running every week, every day, every hour or even every few seconds. So, even if you need frequent updates to your Data Cube and do not want the dashboards hitting the live database with a million queries, go for either of these.

3. Change the Storage Type if Needed

Sub-query Optimization

Select transforms against a source allow multiple transforms downstream from them to be executed as a single query.

In Manual Select transforms, Dundas BI will try to do the same thing. This is called Sub-query Optimization. However, this capability is lost if Sub-query Optimization is disabled. So, you see that little checkbox that says, “Enable Sub-query Optimization”. Keep it checked!

Now just an important piece of advice. DO NOT put a semi colon at the end of your Manual Select statement, because if you do, then you’ll not be able to use Sub-query Optimization. This leads us to rule number 4.

4. Enable Sub-query Optimization when Using Manual Select Transform

Where to Place Which Transform

As I mentioned in the third rule, Select transforms against a source allow multiple transforms downstream from them to be executed as a single query. As soon as Dundas BI reaches a transform that cannot be executed in the query, it will load the entire data set and process it in the server’s memory. So, you would want to keep these transforms closer to the process result.

The wrong way:

The right way:

Examples of transforms that cannot be executed in the query include:

• Calculated element transform.
• Join transform that joins branches who’s select transforms pull from different sources.
• Filter transform that uses the Contains Any operator (operators such as Equals or Not Equals can be executed as a query).

5. Place Your Transforms Properly

That being said, if you have a simple calculated element (such as Sales*1.13 or String concatenation), try and have those in your database query rather than on Dundas BI because Dundas BI will load the whole data set and calculate each row!

6. Limit the Use of Calculated Elements on the Data Cube to Complex Calculations (those involving scripts)

Metric Sets

This is probably the most important rule of them all. Imagine the following use case: You have three charts that you want to visualize on a dashboard. Each chart shows sales by time. The only difference is that each of these charts is filtered to a different country.

Most people would presumably create three Metric Sets and filter each of them individually. Even though that is one way of doing it, please note that you are essentially querying your database three times.

The best way to do this (and the way I’d recommend doing) is to create one Metric Set, add it three times to your dashboard, create three parameters, and filter each of them individually. This way you can significantly reduce the number of data calls to your database.

Moreover, you can re-visualize each of these to different visualizations. So, you can have a table, a Pie Chart, and a Line Chart (for example) all using just one Metric Set. That is the power of Dundas BI!

7.Re-Use Your Metric Sets

That’s it! That’s the final rule. Fairly self-explanatory if I say so myself.

This brings me to the end of my blog. I hope this helps everyone who reads this as much as it has helped the customers that I have worked with. To summarize this blog, below are my golden rules for performance optimization of dashboards:

1. Create Data Cubes and Metric Sets where possible. Try not to use auto-generated ones.
2. When using Select transforms, uncheck the columns that you do not need. Not only will those columns be added in your query, but they will also be loaded and processed by Dundas BI if you have transforms that cannot be processed in a query.
3. Use warehouse or in-memory storage options instead of real-time querying when needed.
4. Enable Sub-query Optimization when using manual selects.
5. Place transforms that cannot be executed in a single query by the Data Cube closer to the process result.
6. If you have simple calculations like Sales*1.13 or String concatenations, try and run those on the database rather than on the Dundas BI Data Cube. This is an unnecessary load on the server.
7. Re-use your Metric Sets.

Apart from these rules, I highly recommend checking out this link on the support site.

There you go! That’s my time folks! Enjoy optimizing your dashboards with this new-found knowledge.

About the Author

Vidur Khanna is a Partner Advisor at Dundas Data Visualization. Vidur works with Dundas’ partners from an engagement and consultative standpoint, to ensure they’re able to achieve and surpass all their goals with Dundas BI. Vidur focuses on solution-level decisions and analysis of their impact on the overall business goals and outcomes.