Key Areas to Investigate

Performance issues often stem from one or more of the following areas:

Data Model
Dashboard Design
Server Configuration and Network
Environment Cleanup

Let’s explore each of these areas in detail and how you can address potential performance issues.

1. Data Model

Each widget on a Sisense dashboard generates a query to retrieve and display data. The speed of these queries can have a significant impact on dashboard performance. Slow queries often occur due to inefficient relationships or an excess of complex joins.

Many-to-Many Joins: When two tables are joined with duplicate keys, the result is unnecessary duplication of records, which consumes excessive computing resources and leads to slower performance. It's essential to verify the cardinality of relationships and adjust them to One-to-Many or One-to-One where applicable. This ensures more efficient querying and reduces the load on the system. Learn more about troubleshooting many-to-many relationships.

Example of Many to Many: We have two tables: Products and Customers. Both tables contain a Category field:

Products Table:

A table listing products by ID, name, and category. Items: Laptop and Smartphone in Electronics; T-shirt and Jeans in Clothing category.

Customers Table:

A table lists four customers by ID, name, and category. Alice and Bob are in Electronics; Carol and Dave are in Clothing.

If we attempt to join the Products and Customers tables on the Category field, we create a Many-to-Many relationship. This occurs because:

Multiple products belong to the same category (e.g., Electronics has both Laptop and Smartphone).

Multiple customers are associated with the same category (e.g., both Alice and Bob prefer Electronics).

Two tables show products and customers linked by colored lines. The left table lists ProductID, ProductName, and Category; the right shows CustomerID, CustomerName, and Category.

Excessive Joins: When large fact tables are joined with multiple dimension or lookup tables, the resulting queries can become very complex and resource-intensive. These complex queries require significant processing power to merge large datasets from different tables, which can lead to slower performance and longer load times, especially with large volumes of data. The more joins you have, the more SQL operations need to be executed, consuming more memory and resources, which impacts dashboard performance. To reduce the impact of excessive joins, one solution is to reorganize your data model into a more efficient structure.

A common approach is to use a flat table schema, where all the relevant data is denormalized into one single table. This eliminates the need for joins, improving query performance but increases the size of the database due to data redundancy. Alternatively, a star schema is often used, where a central fact table (e.g., Sales Transactions) is linked to multiple dimension tables (e.g., Products, Customers). This reduces the number of joins, making queries less complex and more efficient while maintaining a manageable database structure. Using a star schema, for example, allows the fact table to quickly reference the dimension tables, resulting in faster queries and better overall performance in dashboards. Check out these resources: Flat Table Schema, Star Schema.

Data Types of Key Fields: Using numeric fields for joins instead of lengthy string fields can significantly improve query performance, especially when dealing with large datasets. Numeric fields are processed more efficiently because they require less memory and CPU to compare, as they are stored in a compact, fixed-size format. In contrast, string fields, especially long or variable-length strings, take up more memory and require additional processing to match during joins. This difference becomes especially noticeable when scaling dashboards or working with complex data models, as numeric joins can reduce query execution time and prevent performance bottlenecks. To optimize performance, consider using integers or other numeric data types as key fields in joins whenever possible.

Relevance of Imported Data: The size of the data directly affects query performance. Import only the relevant data required for analysis and avoid including unnecessary columns or historical data. In some cases, importing aggregated data instead of raw data can also reduce query time and storage requirements.

For instance, if you only need to analyze total sales by customer for the past month, importing raw data with every transaction or irrelevant details like shipping addresses can create unnecessary duplication. Instead, importing just the relevant columns or even pre-aggregated data, such as the total amount spent by each customer, can drastically improve query efficiency and reduce system load.

Field Indexing: For live models, it is crucial to ensure that key fields are properly indexed. Field indexing significantly enhances query performance by reducing the time required to retrieve data. When fields are indexed, the system can quickly locate the necessary data, leading to faster query results and an overall improved dashboard experience.

Using Views vs. Tables: When working with live models, it’s important to understand the performance impact of using views versus tables. A view is essentially a stored query that retrieves data from one or more tables, but every time a query is executed on a view, it triggers an additional query behind the scenes to fetch the data. This extra layer of query execution can significantly slow down performance, especially when dealing with large datasets or complex joins. Since views are essentially virtual tables, each time you query a view, the system needs to process the underlying query, which can increase query execution times and result in slower dashboard performance.

On the other hand, using tables provides direct access to data, which allows queries to be executed more efficiently. When you query a table, you're accessing the data directly without any intermediary steps, leading to faster query execution times. For optimal performance, it’s recommended to use tables whenever possible, as they minimize the additional overhead involved in querying. This approach is especially helpful in situations where speed is critical, such as when real-time or near-real-time data retrieval is necessary. By using tables, you can ensure that queries are processed more quickly, improving overall dashboard performance.

2. Dashboard Design

Even with an optimized data model, dashboard design plays a critical role in performance. Poor design can lead to slow query execution and long loading times.

Complex Calculations: Complex calculations within widgets generate large, resource-intensive SQL queries. These queries are executed every time a filter is applied or the dashboard is refreshed, which increases loading times. To mitigate this, consider pre-calculating complex formulas in the data model rather than in the dashboard, so the system retrieves values directly from the table without having to execute complex queries each time.

Imagine a dashboard that tracks employee performance, and one of the metrics is the Employee Efficiency Ratio, calculated as:
(Total Tasks Completed / Total Hours Worked) * 100.

If this calculation is performed within the widget, the widget has to perform this calculation each time it refreshes, or when filters such as team or month are changed. This can slow down performance, especially when dealing with large datasets. A more efficient approach would be to pre-calculate the Employee Efficiency Ratio in the data model, creating a new column for it. This way, the dashboard can simply retrieve the pre-calculated value, improving load times and overall performance.

Displaying Large Volumes of Non-Aggregated Data: Fetching large datasets without aggregation can severely slow down performance, especially when showing row-level data in pivot tables or charts. Sisense has to process and store the data on the web server before it can be displayed visually, which can significantly increase loading times. Aggregating data in the model before displaying it on the dashboard helps reduce the amount of data being processed. Additionally, consider using Sisense add-ons like Accordion and Jump-to Dashboard to allow users to access detailed data on demand without burdening the main dashboard. Accordion Add-on, Jump-to Dashboard Add-on.

Number of Widgets: Having more widgets on a dashboard means more SQL queries are executed, leading to greater resource consumption and longer loading times. Sisense recommends limiting dashboards to 6-8 widgets for optimal performance. If you need more, consider using the Lazy Loading plugin, which ensures that only visible widgets execute queries, while non-visible widgets wait until they are scrolled into view. This reduces unnecessary resource usage and improves dashboard speed.
Lazy Loading Plugin.
For live models, using the Lazy Loading plugin will also help reduce cloud DWH costs by minimizing the number of queries sent to the database.

Query Optimization: Sisense offers a Query Optimizer plugin that dynamically restructures queries, improving execution time and reducing load on the system. This is especially helpful when dealing with complex or large queries. Query Optimizer Plugin.

Default Filters: Setting default filters is a simple but effective way to improve dashboard performance. By pre-setting filters to display only relevant data—such as showing only the current month’s data by default—you limit the amount of data loaded when the dashboard is first accessed. This reduces the processing time needed to display the data, ensuring that the dashboard loads faster and more efficiently.

Since each filter added to a dashboard and every user selection generates a new query to the database, it's important to use filters strategically. Exploring different filtering solutions can help optimize performance.

For example, if your dashboard contains data for multiple months, applying a default filter that limits the view to the current month ensures that only a smaller subset of data is queried and displayed. Not only does this improve the performance of the dashboard, but it also helps users focus on the most relevant and meaningful insights right away, without having to sift through large volumes of irrelevant information.

To support this, you can use the free Adaptive Default Filters plugin to set smart default filters based on user or date logic, and the free Hide Filters plugin to keep your dashboards clean while maintaining filter functionality in the background.

3. Server Configuration and Network

In addition to optimizing the data model and dashboard design, your server and network infrastructure also play a critical role in Sisense's performance.

System Requirements: Sisense has specific hardware and software requirements to ensure optimal performance. If your system doesn't meet these minimum requirements, performance may suffer. Refer to Sisense’s system requirements on Windows or Linux to ensure that your infrastructure is appropriately configured.

Network for Live Models: If you are using a live model that queries data directly from the source, the speed of your network connection to the data source is crucial. A slow network can significantly delay query execution. To improve performance, ensure that the network connection to your data source is fast and stable.

Database Performance: The efficiency of the database system, especially when using a live connection, directly affects query processing time. Ensure that the database is properly optimized and capable of handling large volumes of data and concurrent user requests. A slow or poorly configured database will lead to slower query execution, negatively impacting dashboard performance.

4. Environment Cleanup for Improved Performance

Regular maintenance of your Sisense environment is essential to sustain good performance. Over time, unnecessary data, dashboards, and users can accumulate, putting unnecessary strain on system resources.

Some best practices for maintaining a clean environment include:

Remove Test Dashboards: Delete any test dashboards that are no longer needed.
Delete Unused Elasticubes: Regularly remove old or unused Elasticubes to free up system resources.
Remove Inactive Users: Ensure that only active users are listed in your Sisense environment.
Turn Off Unused Plugins: Disable any plugins that are no longer in use to save resources.
Disable Irrelevant Email Reports: Turn off any scheduled email reports that are no longer necessary.

By regularly cleaning up your environment, you can ensure optimal performance and avoid unnecessary resource consumption.

Conclusion

Optimizing Sisense dashboard performance requires attention to detail across multiple areas, including data model optimization, dashboard design, and server/network configuration. By following the best practices outlined above, you can significantly improve performance, ensuring that your dashboards load quickly and provide a seamless user experience.