What is good dashboard performance?
Dashboard performance is the time it takes to load a SiSense dashboard. We want our users to have a good experience when working with our data. The time taken to load the dashboard is the first step.
Each individual widget in a dashboard is a separate query. The dashboard needs to send these queries through the Microsoft IIS web server to the ElastiCube, pull back the results and populate the data visualizations. The question we will address in this post is what is a reasonable dashboard load time and how can we enhance performance.
So, what is good dashboard performance? This is a slightly subjective question, as users always want the fastest load time. However, we must think about load time relative to the data we are processing. If the dashboard is crunching billions of rows of data it is unrealistic to expect two second load times. The opposite is true, if a dashboard is only working with hundreds of thousands of rows, the dashboard should not take five minutes to load. The goal, to put it simply, is to optimize the load time relative to the amount of data the dashboard is processing. SiSense’s architecture is designed to return queries very quickly, and there are ways to help it.
How to monitor performance
First and foremost, how long does the dashboard take to load after the user navigates to the URL? Does the load time inhibit the user experience? This is a subjective question. However, as a general guideline we want the load time to be within a minute or two if the dashboard is not crunching enormous amounts of data.
Monitoring system resources during dashboard load is the first step to identifying possible issues. These can be monitored live by opening the Task Manager > Performance tab. RAM and CPU should not be maxed out during the dashboard load. If they are, it could be hindering the process. More system resources might be necessary.
ElastiCube.exe – if this process is taking CPU and RAM then the query is too heavy. This could be a possible many to many occurrence (covered below)
ElastiCubemanagment service – if this process is taking CPU and RAM, you most likely have a build occurring at the same time
w3wp / iisexpress – if this process is taking CPU and RAM you are overloading your IIS server. This is mostly likely when presenting too much data (covered below)
Another way of monitoring system resources is by using the Windows Performance monitor. This will help analyze system resources over time. The post here should help: https://support.sisense.com/entries/52024704-Setting-up-Windows-Performance-Monitor
Timing vs other dashboards
Using other dashboards that use similar data sets as a baseline can also be useful. For example, two dashboards are using the same ElastiCube. Dashboard-A takes 30 seconds to load. Dashboard-B takes five minutes. Knowing that Dashboard-A loads normally, we can tell that Dashboard-B has some kind of an issue. Possible issues could be a bad join, many to many issue, or specific troublesome widget that Dashboard-A did not have. Compare the differences to narrow down possible issues.
Web Developer Console
Causes of slowness
A reasonable query time depends on a few factors. First and foremost, system resources. The recommended system resources for SiSense can be found here: http://www.sisense.com/documentation/v5/#cap_hard.
Amount of data
The amount of data crunched/returned in a query has an impact on performance. Logically, this makes sense. Crunching one million rows will, of course, return faster than one billion rows. The way to affect this is by adjusting filters to limit the data returned. Furthermore, limiting the amount of data in the ElastiCube is useful. This can be done by editing either the tables that SiSense is connected to, or by editing the SQL statement that is used to query the original data source.
Schema design is a very important factor in query performance. Each data connection within the ElastiCube will become an inner join when the dashboard queries this data. Joins are costly when it comes to query time. When designing the schema for performance, the ElastiCube designer should de-normalize the schema as much as possible. De-normalization means having a fewer amount of joins, and including redundant information in one table. For more on schema de-normalization, see here: https://support.sisense.com/entries/69750994-Data-Denormalization-for-Faster-Dashboard-Query-Response
Custom Calculations in the Dashboard
Custom calculations are often useful when manipulating data to answer business questions. They allow users to aggregate data in various ways to present visually. However, they do take time to process. Each custom calculation is a separate query that is processed by Sisense. To enhance query performance we can cut down the number of queries generated by the dashboard. This is done by moving some calculations to the ElastiCube, thus moving that process time to the build time instead of dashboard load. The best example is a row calculation; that is the addition of two separate columns. (FieldA + FieldB) this type of calculation can be easily added to a table using the same exact formula as a custom field. If it is from a different table, we can easily use the lookup function as mentioned previously.
Presenting too much data
Presenting large amounts of data is costly on dashboard load time as well. More specifically this causes slowness when using a pivot or table widget with many rows. The web server has to process the large amount of data and store it to allow the tool to present it visually. If it is possible, limit the number of dimensions that break the data to such a granular form.
Other causes of query slowness
Many to Many
If RAM grows exponentially, and the dashboard takes a very long time to load, you could have a possible many to many relationship. A many to many relationship (M2M) can cause extreme slowness in dashboard load time. A M2M is a relationship in the ElastiCube where neither side of the relationship is unique. This creates a Cartesian product, thus consuming all of the machines resources. For more on causes and fixes of M2M, please see this link: https://support.sisense.com/entries/41829190-Many-to-Many-Relationships-Types-Testing-Scenarios-and-Resolutions