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.

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.

If you cannot see your queries, you need to enable the readableQuery.enabled parameter as follows:

  1. As a user with the System Admin role, navigate to Admin > search for and select System Management > click Configuration.
  2. Click the Sisense logo 5 times.
  3. From the left pane, select Base Configuration.
  4. Either scroll down the list or use the search to locate ReadableQuery.
  5. Toggle the readableQuery.enabled parameter to on.

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 '***'.