When designing a dashboard, it's important to keep performance concerns in mind. Many simple steps taken during the design phase will dramatically improve performance and reduce the overall time to completion. A well-designed dashboard will not only run smoothly for end users, but will also minimize server load. This article provides a set of guidelines and tips for making high performance dashboards.
By using data caching you can dramatically reduce querying times. Normally most of the time during loading is spent on querying data. Dundas Dashboard supports two types of caching:
- Sync database caching
- In-memory caching
Sync database caching is where virtual tables are queried on a schedule of your choosing. The virtual table is queried and the result is kept in our Sync database. Any requests for the virtual table's data then come directly from our cache. This is particularly useful for virtual tables that need to use expensive queries (for example, with many large joins).
Not all virtual tables can be cached in our sync database. For example, virtual tables from some data connectors can't be cached. Other things on a virtual table, such as public parameters or user-specific filters, will also prevent sync caching.
When using the sync caching, it is extremely important to make sure the correct indexes are built. These indexes can be set during flat file import operations or in the Virtual Table Designer itself. A lack of indexes or poorly chosen ones may actually lead to a decrease of performance instead of the expected increase. See Defining Columns for a Virtual Table - Cache indexes and primary keys
for more details.
Both these caching options and any limitations are documented in the Caching Data
When creating your Virtual Table it may initially make sense to grab as much data as you can, and then later filter and refine the data at the KPI/Dataset level. However, it can improve performance to break up this Virtual Table into multiple Virtual Tables where appropriate. For example, if you are creating a Standard Dimension, the Virtual Table that this Dimension is based on should remain separate from the Virtual Table(s) used to build your KPIs and Datasets.
If the granularity of your raw data is higher than the data you intend to display, aggregate it to the smallest granularity you need. For example, you may have data down to the second but only intend to show data by day, month, and year. Aggregate the raw data into daily data in this case. This can reduce thousands of rows of data into a single row. Normally this is done as part of the ETL layer. Although not ideal it can also be hanlded in the following ways:
- Create a materialized view in your database (called indexed views in SQL Server). A materialized view is a essentially the cached result of a query.
- Benefits: The view's contents are always in sync with the underlying data.
- Drawbacks: Keeping the view in sync comes with a price. If the underlying data is constantly updated, this maintenance cost may exceed the value of having the view.
- Edit the virtual table and set the aggregation and grouping setting for columns. As long as the table doesn't have any public filters, this table can be sync database cached.
- Benefits: The virtual table's contents are only updated on a schedule you determine.
- Drawbacks: The underlying data may not be in sync with the cached virtual table.
More information on indexed views in SQL Server is available from MSDN
Filter data in the virtual table as early as possible. For example, if a virtual table only ever shows data for a certain time range, a specific set of regions, etc., filtering these earlier can help performance since it may avoid unnecessary and costly joins.
Remove unused columns¶
When designing virtual tables, avoid selecting columns that aren't used in your KPIs/datasets. In a well-designed database, a table may have an index that covers all the columns in the SELECT, JOIN, and WHERE clause of a query (called a covering index). In these cases, performance can be improved because all the data for the query is pulled directly from the index itself. If unused columns are included in the query, data will need to be pulled from the table resulting in slower performance.
To remove columns that you don't need in a Standard Virtual Table or Stored Procedure-Based Virtual Table:
- Edit the virtual table
- Go to the Columns tab
- Check the "Hidden" setting for any columns you don't need
Location of data, server, and client¶
Try to keep your data sources, Dundas Dashboard's server, and the client as close to each other as possible. Network latency can have a major impact on performance if the data you need is on the opposite side of the planet. Sync and in-memory data caching can help reduce this impact.
Rendering performance is affected by several factors:
- Number and complexity of visualizations
- Amount of data to render
- Complexity of scripts to run
The first two points are closely related. All controls have a certain amount of overhead, and different controls are faster at displaying data than others. For example a data grid is generally much faster than a chart when it comes to displaying large amounts of data. However a well-designed dashboard that follows data visualization best practices will minimize this potential problem.
Dashboard's Silverlight viewer renders everything on the client, so rendering performance is based solely on the speed of the client machine. For the HTML5 viewer, the server's hardware will also matter since it renders the dashboard on the page.
From both a readability standpoint as well as performance, you may want to consider breaking up the contents of the dashboard over multiple dashboards if it contains a lot of KPIs/datasets.
Load only necessary data
If you don't need to use the annotations feature
, disabling it can help performance. Annotations require extra requests to be made for the KPIs/datasets on the dashboard. Disabling annotations will stop these unnecessary requests. This is controlled with the advanced property Disable Annotations
on the dashboard.
Disable loading data
If your dashboard has controls which are commonly hidden, avoid loading the data for this control until it's needed. This will postpone a costly load until the user needs it. This lets the main dashboard load faster so the user can interact with it sooner. See the documentation for the Disable Loading Data property
for more details.
Default parameter values
The most common default parameter value used is All values
but often all the data isn't needed. This is very typical with date ranges. Optimizing the amount of data you retrieve can make a significant change in loading time.
When the default value needs to be determined dynamically, most users will set up the parameters in the Load Interaction
without using the Disable Loading Data
property. This will result in poor performance because the KPI/dataset loads using the default parameter values first, then the values set programmatically in the Load Interaction
Another option for setting default parameter values dynamically is to use a custom token
Parameter Update Button
When a dashboard has multiple parameters a user will change, consider setting the Update Type
and adding a Parameter Update Button
to the dashboard. This way a set of parameters can be changed together without loading new data each time one of the parameters changes.
The chart control is optimized for displaying hundreds of thousands of data points for some chart types, provided some features are not needed. See the Large datasets
section of the chart documentation
for more details.
The following sections describe ways to improve the performance of analytical dashboards (besides using memory caching).
While editing a virtual cube report
, click Paging
in the toolbar to configure settings for paging.
Select the Don't Query CellSet Size
option to eliminate the second query that is issued by the analytical dashboard when it tries to get the page count for the pager control. This can help to improve performance and should generally be used when you are displaying analytical charts which do not require the page count.
While editing a virtual cube report
, click Options
in the toolbar to configure various options for the report.
One of these options is Slicer As Subquery
, which can help to improve performance but note that you may get different results depending on whether you are using hierarchies from the same dimension.