Concepts → Incorta SQL View

About an Incorta SQL View

An Incorta SQL View is one of three types of views that you can create in a business schema. The other types of view are a business schema view and Incorta Analyzer View. Conceptually speaking, you can consider these types of views as being a runtime business view.

You can create an Incorta SQL View using the SQL editor. Here are some of the reasons why a schema developer will create an Incorta SQL View:

• Curate the columns exposed in one or more underlying physical schema tables
• Curate the columns exposed in one or more runtime business views
• Implement an individual filter for the view
• Implement a distinct filter for the view
• Implement an aggregated filter for the view
• Standardize business terminology
• Standardize row-level formulas and calculations
• Standardize aggregated formulas and calculations

About an applicable insight on a dashboard tab and an Incorta SQL View

A dashboard runtime filter applies the filter expression to all applicable insights on all tabs of a given dashboard.

For a filterable column, an applicable insight meets one of the following conditions:

• the filterable column is a dimension or measure column in the insight
• the filterable column has a sibling dimension column from the same physical schema table, and that sibling is a dimension for the insight
• the filterable column is a dimension that shares a measure on the insight from a common child table with other dashboard insights

Creating an Incorta SQL View

You can create an Incorta SQL view in the Business Schema Designer. From the Action bar, select +New → Add New View, and then select Create via SQL.

An Incorta SQL View only runs when it is referenced by an insight in a dashboard. Incorta SQL View is not run at startup time, or at a schema refresh time, instead, the view definition is saved, and the Incorta SQL View query runs when you open an insight that is using that view.

You can filter data in an Incorta SQL view using presentation variables in dashboards.

Supported SQL

The type of supported SQL queries depends on the tables that you are trying to access whether they are performance optimized or not. When an Incorta SQL View uses memory optimized tables, the SQL runs using the Incorta SQL engine.

If the Incorta SQL View uses non-optimized tables, the SQL runs using Spark. Refer to the Spark documentation for more information.

Referenceable objects

In the FROM clause, you can reference the following physical schema objects:

• Physical_Schema.Alias
• Physical_Schema.Table
• Physical_Schema.Materialized_View
• Physical_Schema.Incorta_Analyzer_Table
• Physical_Schema.Incorta_SQL_Table

It is not possible to reference the following objects:

• Business_Schema.Incorta_View
• Business_Schema.View

Join Types

Here are the join types for schema tables and SQL queries:

• CROSS JOIN
• INNER JOIN
• FULL OUTER
• LEFT OUTER
• RIGHT OUTER

Join Conditions

Here are the supported join conditions:

• table1.column1 (<), (<=), (>), (>=), (=) table2.column1
• table1.column1 (<), (<=), (>), (>=), (=), (<>) table1.column1
• table1.column (<), (<=), (>), (>=), (=), (<>) constant
• table1.column IN (constant1, … constantN)
• table1.column BETWEEN constant1 AND constant2
• table1.column LIKE ‘pattern’ [‘escape’]
• table1.column is [NOT] NULL
• startsWith(table1.column, ‘STRING’)
• endsWith(table1.column, ‘STRING’)
• contains(table1.column, ‘STRING’)

Supported predicates between join conditions and filters

The following are supported predicates between join condition and filter

• AND
• OR

Data types compatibility in JOIN constraints

The following conversion table describes how an Incorta SQL View handles a join constraint where the data types differ for the join columns.

Set operators

Here is a list of supported set operators:

• UNION
• UNION ALL

Group filters

Here is a list of supported Group filters:

• HAVING

Row filters

Here is a list of supported Row filters:

• column (<), (<=), (>), (>=), (=), (<>) constant
• column [NOT] IN (constant1, … constantN)
• column BETWEEN constant1 AND constant2
• column is [NOT] NULL
• column [NOT] LIKE

In addition to the standard row-level filters, you can specify in a WHERE clause the following:

• [NOT] EXISTS (SELECT… )
• [NOT] IN (SELECT… )
• WHERE column (<), (<=), (>), (>=), (=) ANY(SELECT… correlated subquery)
• WHERE column (<), (<=), (>), (>=), (=) ALL(SELECT… non-correlated subquery)

Aggregation functions

Here is a list of supported aggregation functions:

• AVG()
• COUNT()
• COUNT(DISTINCT)
• MIN()
• MAX()
• STDDEV()
• STDDEV_SAMP()
• STDDEV_POP()
• SUM()
• VARIANCE()
• VAR_SAMP()
• VAR_POP()

Referencing Incorta Variables

Inside the SQL, you can reference Incorta variables using the following functions:

These functions enable you to use dashboard prompt filters and create more dynamic queries based on Incorta variables.

Window functions

A window function performs a calculation across a set of table rows that are somehow related to the current row. An Incorta SQL View supports windows functions such as:

• OVER ()
• OVER (PARTITION BY column)
• OVER (ORDER BY column)
• OVER (PARTITION BY column ORDER BY column)

You use these aggregation functions in a window function:

• AVG(), MIN(), MAX(), SUM(), LEAD(), and LAG()

You can use the advanced analytics functions in a window function:

• DENSE_RANK(), RANK(), and INDEX()

Example of a Windows functions query

Here is an example of a windows function query using the HR physical schema found in the sample data provided by Incorta:

Copy
SELECT t2.DEPARTMENT_NAME, t1.salary, RANK() OVER (PARTITION BY t2.DEPARTMENT_NAME ORDER BY t1.salary DESC)
FROM tbl_Employee t1 INNER JOIN tbl_Department t2 ON t1.department_id = t2.department_id
GROUP BY t2.DEPARTMENT_NAME, t1.salary
ORDER BY  t2.DEPARTMENT_NAME,  t1.salary

Common Table Expressions

An Incorta SQL View supports a non-recursive Common Table Expressions (CTE) for a SELECT statement.

The WITH keyword signifies a CTE, followed by a CTE name and the body of the CTE, and a SELECT statement. Optionally, a list of column names can be specified in the CTE SELECT statement. A primary SELECT statement references the CTE by name in a FROM clause. Here is an example:

Copy
WITH cte_analysts AS
   ( SELECT * FROM tbl_Employees
     WHERE dept = 'Finance' )

SELECT * FROM cte_analysts
WHERE ...

A non-recursive CTE is more readable than a nested SELECT statement such as:

Copy
SELECT v1.* FROM (
  SELECT * FROM tbl_Employees
  WHERE dept = 'Finance') v1

A WITH query can only reference sibling WITH queries that are earlier in the WITH list. A WITH query evaluates only once per execution of the primary query, even if the primary query refers to WITH query more than once.

Examples of a CTE

Here is an example of a year-over-year comparison using the SALES physical schema:

Copy
WITH sales_product_year AS (
  SELECT t1.PROD_ID, YEAR(t1.TIME_ID) AS year,
  SUM(t1.AMOUNT_SOLD) AS total_amt
  FROM SALES.SALES t1
  GROUP BY t1.PROD_ID,  YEAR(t1.TIME_ID)
)

SELECT t2.PROD_ID, t2.total_amt as cur_total_amount, t2.year as cur_year, t3.total_amt as prev_total_amt, t3.year as prev_year
FROM sales_product_year t2 INNER JOIN
sales_product_year t3 ON t2.PROD_ID = t3.PROD_ID
AND  t2.year = t3.year + 1
ORDER BY t2.PROD_ID, t2.year

Insights over Incorta SQL Views

When you create insights referencing Incorta SQL Views, the insights are not rendered using the Incorta engine like other insights. Instead, the insights are converted to SQL, and then the engine is chosen based on what tables are referenced in the Incorta SQL Views.

If all of the tables being referenced in the Incorta SQL View are memory optimized, then Incorta uses the Incorta SQL engine to run the generated SQL. If any of the tables being referenced is not memory optimized, then Incorta uses Spark to run the generated SQL.

Incorta SQL Views supports the following for insights:

• Prompt Filter
• Applied Filter
• Filter Option
• Insight Filter
• Measure Filter

Incorta SQL Views appear in the Analyzer in the list of views in the Data panel.

Incorta SQL View limitations

• It is not recommended to use SELECT *. In case of changing the source tables definitions, the Incorta SQL View definition will not automatically update.
• You cannot use more than one Incorta SQL View in an insight.
• Dashboards with insights based on Incorta SQL Views do not support data alerts (data notifications).