Introducing Analytical Engine
  • 18 May 2022
  • 3 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

Introducing Analytical Engine

  • Dark
    Light
  • PDF

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

Note:

Analytical Engine only works in Linux environments.

Analytical Engine supports the following live providers:

  • Snowflake
  • Amazon Redshift
  • Google BigQuery

In future, more Live data sources will be added. See Live Data Source Connectors in Introduction to Data Sources.

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. Analytical Engine joins dimension tables when selecting entries for top ranking filters, meaning that null can be a top ranking 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.

Many-to-Many Relationship Handling

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.

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. The biggest 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

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, Analytical Engine ignores "Include All" filters when selecting the join path.
Now, 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 Filters):

ForceScope
This enforces adding the table to the join path.

Performance Improvements of SQL Queries

Analytical Engine improves the performance of SQL queries by:

  • Minimizing the use of nested queries
  • 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

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

  • Minimizes the use of nested 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.

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


Was this article helpful?