Concepts → 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.
An Incorta Labs feature is experimental and functionality may produce unexpected results. For this reason, an Incorta Lab feature is not ready for use in a production environment. Incorta Support will investigate issues with an Incorta Labs feature. In a future release, an Incorta Lab feature may be either promoted to a product feature ready for use in a production environment or be deprecated without notice.
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
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
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.
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.
In the FROM clause, you can reference the following physical schema objects:
It is not possible to reference the following objects:
Here are the join types for schema tables and SQL queries:
- CROSS JOIN
- INNER JOIN
- FULL OUTER
- LEFT OUTER
- RIGHT OUTER
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’)
The following are supported predicates between join condition and filter
The following conversion table describes how an Incorta SQL View handles a join constraint where the data types differ for the join columns.
For a join between a string that stores a numeric value and a numeric column, the
string converts into a
Here is a list of supported set operators:
- UNION ALL
Here is a list of supported Group 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)
Here is a list of supported aggregation functions:
Inside the SQL, you can reference Incorta variables using the following functions:
|Uses the System Variable |
|Uses the Session Variable |
|Uses the Presentation Variable |
|Uses the Presentation Variable |
These functions enable you to use dashboard prompt filters and create more dynamic queries based on Incorta variables.
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 (PARTITION BY column)
OVER (ORDER BY column)
OVER (PARTITION BY column ORDER BY column)
You use these aggregation functions in a window function:
You can use the advanced analytics functions in a window function:
Here is an example of a windows function query using the HR physical schema found in the sample data provided by Incorta:
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_idGROUP BY t2.DEPARTMENT_NAME, t1.salaryORDER BY t2.DEPARTMENT_NAME, t1.salary
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:
WITH cte_analysts AS( SELECT * FROM tbl_EmployeesWHERE dept = 'Finance' )SELECT * FROM cte_analystsWHERE ...
A non-recursive CTE is more readable than a nested SELECT statement such as:
SELECT v1.* FROM (SELECT * FROM tbl_EmployeesWHERE 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.
Here is an example of a year-over-year comparison using the SALES physical schema:
WITH sales_product_year AS (SELECT t1.PROD_ID, YEAR(t1.TIME_ID) AS year,SUM(t1.AMOUNT_SOLD) AS total_amtFROM SALES.SALES t1GROUP 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_yearFROM sales_product_year t2 INNER JOINsales_product_year t3 ON t2.PROD_ID = t3.PROD_IDAND t2.year = t3.year + 1ORDER BY t2.PROD_ID, t2.year
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.
- 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.