Analytical Engine Overview

Overview

Sisense's modeling framework is a set of modeling languages and algorithms for BI analysis. Sisense's Analytical Engine is an algorithm that enables you to generate your query in the SQL dialect of the specific data-source with low-no code. The query is generated according to the widget elements (for example, measures and filters) and the user's data security role. It then executes the SQL query on the data source.

The Analytical Engine is based on the Dimensional Modeling methodology, which is a logical design technique that presents the data in a standard, intuitive framework that allows high query execution performance at scale.

In addition to supporting ElastiCube connectors, the Analytical Engine also supports live providers. For the list of live data sources supported by Analytical Engine see Live Data Source Connectors in Data Source Connectors.

Analytical Engine:

• Enables customers to perform real-time analytics, directly over the data provider (Live).
• Leverages the provider's syntax via the open source Apache Calcite (https://calcite.apache.org/).
• Improves the performance and comprehensibility of the SQL queries.
• Defines the query in a very structured way, for improved readability and comprehensibility.
• Validates the query components on widget load and provides actionable error messages when necessary.
• Returns a null value if the query is invalid (due to filters or measures).
• Queries table values (not keys), for better null handling and advanced calculations.
• Exposes the generated SQL query to the dashboard designer, while respecting data security rules.
• Calculates top ranking filters per widget context, simplifying the implementation of the top ranking functionality for dashboard designers.

Analytical Engine's Extended Query Capabilities

Null Handling

By default, Null values are now displayed, and can be filtered out by the user. Null can also be used as a key. The Analytical Engine joins dimension tables when selecting entries for top ranking filters, meaning that null can be a top ranking result.

Top/Bottom Ranking Filter Handling

Analytical Engine handles top/bottom ranking requests as follows:

• When each item has a unique rank, the results are ordered by the rank.
• When multiple items share a rank, those items are further sorted according to the other dimensions used for the presentation of the results (within the rank).

Filtering

The Analytical Engine inserts filtering clauses into the innermost SQL statement. This means that filtering is the first operation performed, minimizing the number of rows that need to be scanned in order to process the query. Complex filters, such as top ranking and filter on measure are significantly improved as a result.

Original Data Types

Casting

Casting is only applied when necessary, such as when calculating an average or handling division of integers, to maintain the original data type wherever possible. This improves performance and the accuracy of results.

Better Precision for Float Numbers

Float numbers inherit the original datatype from the live source, yielding more accurate results.

Query JOIN Path

Sisense Low-No Code approach for mashing up data and dashboard design can sometimes result in a query that is generated by the Analytical Engine that is not optimized to the specific analytical use case. A big impact on both query execution time and result correctness is the Join Path that is determined by the Analytical Engine, to calculate filters and measures that correspond with the widgets elements.

System

The Analytical Engine uses Dimensional Modeling concepts to generate queries, resulting in improved and consistent path selection - only schema changes affect the JOIN path in the generated query.

User Impact on the Query JOIN Path

ForceScope Filter

By default, the Analytical Engine ignores "Include All" filters when selecting the join path. If a dashboard designer wants to control the join path and use the Include All functionality, the following must be added to the filter's Advanced tab (see Creating Dashboard Legacy Filters):

This enforces adding the table to the join path.

Performance Improvements of SQL Queries

The Analytical Engine improves the performance of SQL queries by:

• Calculating measures from the same table simultaneously in one scan.
• Performing caching per security role, and can be used by all users with the same data security role.
• Applying filters once for all widget measures.

Comprehensibility of SQL Queries

The Analytical Engine displays the generated SQL query to the dashboard designer in a more comprehensible way. It:

• Generates shorter queries.
• Applies data security rules to your queries so data that must not be displayed to users is obfuscated with asterisks (***).
• Uses WHERE IN statements for filters, improving the accuracy of query results.
• Returns more accurate and predictable results for past period functions, such as Past X, DiffPast X, Growth, Growth Rate, GrowthPast X.

This greatly simplifies query debugging, and ensures that it yields the desired result.

Many-to-Many Relationship Handling

The Analytical Engine uses WHERE IN statements to:

• Reduce many-to-many scenarios. It is used in filters, to select data from several tables with higher performance.
• Maintain the integrity of the original data set total, for pivot table subtotals and grand totals. This improves the performance and simplicity of the SQL query calculations.

 SELECT "dTable_aCATALOG_agenre"."value" AS "genre_res"
     ,"median__duration_res"
     FROM (
     SELECT "t2"."genre"
     ,MEDIAN("t2"."duration") AS "median__duration_res"
     FROM (
     SELECT "t1"."genre"
     ,"t"."duration"
     FROM (
     SELECT "aFILMS"."aid" AS "id"
     ,"aFILMS"."aduration" AS "duration"
     FROM "aBLOCKFLIX"."aFILMS"
     ) AS "t"
     INNER JOIN (
     SELECT "aCATALOG"."afilmXwAaid" AS "film_id"
     ,"aCATALOG"."agenre" AS "genre"
     FROM "aBLOCKFLIX"."aCATALOG"
     WHERE "aCATALOG"."agenre" IN (
     SELECT "dTable_aCATALOG_agenre"."key"
     FROM "aBLOCKFLIX"."dTable_aCATALOG_agenre"
     WHERE "dTable_aCATALOG_agenre"."value" IN ('drama')
     )
     ) AS "t1" ON "t"."id" = "t1"."film_id"
     ) AS "t2"
     GROUP BY "t2"."genre"
     ) AS "it"
     INNER JOIN "aBLOCKFLIX"."dTable_aCATALOG_agenre" ON "it"."genre" = "dTable_aCATALOG_agenre"."key"
    ORDER BY "dTable_aCATALOG_agenre"."value"

 SELECT MEDIAN("t4"."duration") AS "median__duration_res"
     FROM (
     SELECT "aFILMS"."aduration" AS "duration"
     FROM "aBLOCKFLIX"."aFILMS"
     WHERE "aFILMS"."aid" IN (
     SELECT "film_id"
     FROM (
     SELECT "aCATALOG"."afilmXwAaid" AS "film_id"
     ,"aCATALOG"."agenre" AS "genre"
     FROM "aBLOCKFLIX"."aCATALOG"
     ) AS "t"
     WHERE "t"."genre" IN (
     SELECT "dTable_aCATALOG_agenre"."key"
     FROM "aBLOCKFLIX"."dTable_aCATALOG_agenre"
     WHERE "dTable_aCATALOG_agenre"."value" IN ('drama')
     )
     )
    ) AS "t4"</pre>

Data Source Function Requirements

The Analytical Engine commonly uses the following analytical functionality. This functionality is translated into the listed dependent SQL elements when sent to the data source, so the data source can run the query and retrieve the required result set. If your data source does not support these SQL elements, you will get an error if the SQL element is used. Therefore, it is important that you check whether your data source supports these SQL elements.