Building dashboards with performance in mind
When designing a complex dashboard or view, it can be 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.
2. Data storage
By using data storage, you can reduce querying times. Typically, most of the time during loading is spent on querying data. Note that if you are displaying "live" or real-time data, you should not use data warehousing.
Dundas BI supports two types of data storage:
- Warehouse - stores data in a database
- In-memory - stores data in server memory
Using the Warehouse option, your data cubes are queried on a schedule of your choosing. The data cube is queried and the result is kept in the Dundas BI Warehouse database. Any requests for the data cube's data then comes directly from this database. This option is particularly useful for data cubes that need to use expensive queries (for example, with many large joins).
You can check the performance measurements if you want to determine how long it took a query to complete, or if it's pulling data from your original data source or from the warehouse.
3. Data management
3.1. Remove unused columns
When creating your data cube, un-select the columns that you do not need as early as possible.
3.2. Aggregate early
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.
3.3. Filter early
Filter data as early as possible. For example, if your dashboard only ever shows data for a certain time range, a specific set of regions, etc., using the Filter transform as early as possible in the data cube can help performance, since it may avoid unnecessary and costly joins.
3.4. Location of data, server, and client
Try to keep your data sources, Dundas BI'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. Warehouse and in-memory storage can help reduce this impact.
4. Developing dashboards and views
More data visualizations on the dashboard do not always mean better. Each needs time to query data and to render, so find the right balance between the number of data visualizations and acceptable dashboard performance.
4.2. Limit the initial amount of data
Consider using an initial filter setting other than All, especially if there's a lot of data involved, or displaying the top 10.
More data points make each phase of loading take longer. For large datasets, grouping and aggregating into fewer rows/points and columns/series may also help with readability.