Performance Issues Troubleshooting

[intapiuser]

Overview:

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. 

Topics:

1. Maintaining a Clean Environment

2. Maximize Dashboard Performance

3. Optimize ElastiCube Performance:

A. Decreasing Build Time

B. Optimizing Queries

4. Getting Additional Assistance

Related Articles:

Minimum Requirements and Supported Platforms

Sisense Grafana

Sisense Performance: A Billion Records In A Single Server

Software Performance Issues? It Might be Your Architecture. 

Maintaining a Clean Environment

To maintain a clean and stable environment, we recommend the following best-practices:

End Users:

• 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

Dashboard Designers:

• 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

Data Designers: 

• 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

Administrators

• 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: Dashboard Performance Best Practices

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)

Many dates:

• 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

Optimizing Queries

In order to optimize the queries in the ElastiCube, do the following:

Consolidate:

• 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

Surrogate Keys:

• 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).