Viewing Queries to Data Sources
  • 18 May 2022
  • 3 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

Viewing Queries to Data Sources

  • Dark
    Light
  • PDF

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. Sisense uses one of two analytical engines. For the following Live data sources, Sisense uses the Analytical Engine:

  • Snowflake
  • Redshift

For all other data sources, Sisense uses the previous analytical engine. If an error occurs, Sisense automatically reverts back to the previous analytical engine to complete your query.

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 in your own tools for validation, or find text in the query.

Viewing Your 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.

    8-6sqlcodethumb0300.png

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

Managing the New Analytical Engine

Sisense Administrators can switch between the new and old analytical engines. The new analytical engine is disabled by default, however, you can enable it for all or some of the supported data sources. Disabling it reverts Sisense back to the previous analytical engine.

To enable/disable the new analytical engine:

  1. In Sisense, open the Admin page and select System Management > Configuration .

  2. Access the Advanced Options page by clicking the Sisense logo in the top-left 5 times consecutively.

  3. In the Advanced Options page, open the TranslationStrategy section.

    8-6transservicethumb0300.png

  4. To use the new translation service for all your data sources, in the translationStrategy.value field, enter NEW_THEN_OLD to use the new translation service or click Revert to Default to return to the previous analytical engine.

  5. To use the new analytical engine for some of your data sources, open the newTranslatorSupportedProviders section, and in the newTranslatorSupportedProviders.value field, enter the names of the data sources where the new analytical engine should be used when translating queries. By default, Snowflake is the only value. You can add any of the supported providers by adding any of the following values separated by a comma:

    RedShift, SnowflakeJDBC

    8-6transprovthumb0300.png

  6. Click Save Changes.

  7. Republish your Live schemas for data sources using the new analytical engine after changing your settings.

Latest Improvements

Following the release of Sisense Linux V8.2.6, the following improvements have been implemented for Snowflake:

  • Improvements with path selection
  • 'Include all' filters now behave as expected
  • Differences appeared in widgets due to casting
  • Null results appear for aggregations
  • Sisense rejects JAQLs with invalid filters, meaning, a mismatch between the filter column definition and the data model column definition (data type change, rename column.. )
  • Using WHERE IN for filters can result in different, more accurate, query results

Was this article helpful?