Analyzing Queries to Data Sources

When the widgets in your Sisense dashboard are displayed, Sisense queries your data sources based on the widget type, filters, and other conditions relevant to your widget. Each data source is different, so Sisense translates the query to SQL that your data source understands.

The advantage of the Analytical Engine is that your queries are optimized and Sisense exposes the SQL query to your data source. As you create or edit widgets, you can view a translated SQL query that Sisense runs against your data source. This allows you to check the query, copy it to your own tools for validation, or find text in the query.

Prerequisites

Some Live connectors may have the Analytical Engine toggled off by default due to partial support, which can prevent the Analyze SQL Query functionality from working correctly. Therefore, before using the Analyze SQL Query functionality, ensure that the Analytical Engine is activated based on your data model (ElastiCube or Live). For detailed instructions, see Activating the Analytical Engine.

Analyzing Your SQL Queries

Sisense enables you to view the translated SQL query sent to your data source when editing or creating a widget. When viewing the SQL code sample, you can click Copy Code to add it to your clipboard and test it in your own application.

To view your translated SQL query:

1. Create a new widget or edit an existing widget connected to a supported Live data source.
2. In the widget's menu, select Analyze SQL Query. The SQL query is displayed.
   

Understanding Your Queries

Queries generated through the new analytical engine have been optimized to improve performance. This section explains some of the optimizations made so you can understand your queries when you read them, for example:

• Provider syntax: Sisense uses the provider's syntax for example, date formats.
• Fewer nested queries: Sisense reduced the number of nested queries simplifying queries.
• Meaningful aliases: Sisense displays readable column and table names.
• Grouping measures: Measures are selected in one query instead of one query per measure.
• Filters using WHERE IN instead of joins: Many-to-many scenarios are reduced by using WHERE IN statements instead of joins.
• Data security: Data security rules are applied to your queries so data that should not be displayed to users is obfuscated with '***'.