This article aims to cover preventing, diagnosing, and resolving performance issues in Sisense.
In Sisense, performance is generally referred to as the time it takes to load a dashboard or build a cube. The end goal for many organizations is for users to have a smooth and enjoyable experience when working with their data.
The primary method for avoiding performance issues is maintaining a clean environment and and ensuring that your servers meet the minimum requirements for working with Sisense.
Not all performance issues can be prevented with a clean environment alone, so this article also covers some of the basics of maximizing and optimizing Sisense performance.
Maintaining a Clean Environment
To maintain a clean and stable environment, we recommend the following best-practices:
- Inform end users of new features and any errors you see within the logs related to their dashboards or Elasticubes
- Hold end users accountable for removing old and unused dashboards if they have dashboard creation abilities
- Organize dashboards into folders to more easily manage dashboards
- Remove test dashboards when you're done using them
- Turn off scheduled email reports that are no longer relevant
- Remove old Elasticubes that are not in use from the Sisense Server Console
- Space out builds so there are no more than 4 concurrent builds at one time
- When adding data to the cube only import the tables and columns you expect to use in the data model and remove tables you no longer need
- Remove users who no longer work at your organization or should no longer have access to Sisense
- Power down and remove Sisense from servers that you are no longer using in your deployment
- Turn off and remove any plugins that your organization is not using. Also test plugins in non-production environments when possible
Clean old Elasticube version C:\ProgramData\Sisense\PrismServer\ElastiCubeData
Manage your plugins
Maximizing Dashboard Performance
Please refer to the following associated article that takes an in-depth look at dashboard performance: Maximize Dashboard Performance
Optimize ElastiCube Performance
To optimize Elasticube performance there are two focus areas:
- Decreasing Build Times
- Optimize Queries
Decreasing Build Time
In order to optimize the build time, reduce:
Many fields, long strings:
- Don't import long string fields if they will not be used in the model
- Always question the need for columns with long strings before adding them to the model (URLs, very long comments)
- Removing time-based data that is not needed will reduce build time (ie. don't import old data if you don't need to)
- Consider the date range in the requirements of the dashboard and data model
- Import a Dates File instead of a Custom table to create a date dimension
Use the source database when possible:
- Create views to replace custom tables and import the View
- Filter out irrelevant data (history, in-active, etc.)
- Customize the query when adding the data to the Elasticube
Optimize custom tables
Avoid Processing Power and Time Expensive Operations:
- Replace UNION with UNION ALL when possible
- Left and Right joins
- Consider lookups
- Filter data within the table
Avoid Redundant Operations:
- Consider sub queries
- Avoid SELECT *
- Order by
In order to optimize the queries in the ElastiCube, do the following:
- Look up “translation” tables
- Avoid unnecessary joins
- Consolidate Facts
Calculate custom columns:
- In large data sets it may be significant
Sum and DUPCOUNT are faster than count
Joins on indexed fields:
- Check for casting in custom tables
- Cast fields in source tables instead of using casting functions
Join on numeric fields:
- Numeric dates
- Join on Date with no Time component
- When possible, create in the DB
- Avoid surrogate keys when big data
(consider numeric using this article)
Getting Additional Assistance
If you are still not able to resolve the issue with the information above, please make sure to submit a support ticket clarifying the following points:
- When did the issue first begin? Has it ever worked as expected in the past?
- Have you conducted any recent changes, such as system changes or Sisense version upgrade?
- Does the issue reproduce at all times, or only in a specific scenario?
- Does the issue occur across the board, or only on specific dashboards/cubes/users?
- During the time of the issue, do you find the system under high RAM/CPU utilization?
It would be very helpful to receive step by step screenshots, or a short video of the occurring issue, in order to make sure we fully understand the scenario. You can use Windows steps recorder in order to do so (press WIN+R,then type psr and press enter).