Concepts → Incorta Analyzer Table
About an Incorta Analyzer Table
An Incorta Analyzer table is an entity object in a physical schema. It is a derived table built using the Analyzer that queries in-memory data from a physical schema or a business schema. During a load or update job, an Incorta Analyzer table persists to shared storage as Apache Parquet files.
Incorta Analyzer table metadata
The Incorta Analyzer table’s metadata is derived from its data sources. Since the Incorta Analyzer table now exists as parquet files, the Loader and Analytics Services, Spark, and external tools (using SQL interface SQLi) can read the Incorta Analyzer table.
The following are the properties of an Incorta Analyzer table:
- Name
- Data Source(s)
- Columns
- Runtime Security Filters
- Parent Joins
- Child Joins
A Performance Optimized table is loaded into memory. An Incorta Analyzer table must exist in memory. As a result, Performance Optimization should not be disabled for an Incorta Analyzer table.
Name
An Incorta Analyzer table name must adhere to the following name validation rules:
- Must be unique to the physical schema
- Must be between 1 and 128 characters in length
- Must begin with an alpha character, lower or upper case
- After the first alpha character, can contain zero or more alphanumeric characters in lower, upper, or mixed case
- After the first alpha character, can contain zero or more underscore (
_) or Dollar sign ($) characters - Besides underscore (
_) and Dollar sign ($), cannot contain special characters, symbols, or spaces
The name for an Incorta Analyzer table is immutable.
Data Source(s)
Using the Analyzer, the user creates references to other schema entities and their metadata. These references form the data sources for an Incorta Analyzer table.
Type
The data source type should be set as Incorta Analyzer. Changing the data source type will change the type of table created.
Incorta Analyzer
By clicking the edit button (pen icon), you will open the Analyzer and select your data sources. The Incorta Analyzer table is completely constructed from within the Analyzer. A feature of an Incorta Analyzer table is that the table is built as a listing table or aggregated table.
When you build the derived Incorta Analyzer table as an aggregated table you are able to use data aggregation within the physical schema. You can also take advantage of aggregation functions in an Incorta Analyzer table formula column.
Columns
An Incorta Analyzer table can have two types of columns:
You create and identify the sources of these columns when constructing the Incorta Analyzer table in the Analyzer. In the Manage Data Sets panel, you can select data sources from either the Business Schema or Physical Schema. See Additional Considerations for Incorta Analyzer table column concerning interactions with the business schema.
Runtime security filters
As a schema developer, you can apply one or more runtime security filters to restrict row access to an Incorta Analyzer table. Any dependent object such as a runtime business view, dashboard insight, dashboard filter, or internal session variables will automatically apply the runtime security filter. To learn more about a physical schema table runtime security filter, see Concepts → Physical Schema Table Runtime Security Filter.
In the case of an Incorta Analyzer table, when selecting columns from the Analyzer your visibility of the source entity’s information will be appropriately restricted by any runtime security filters applied to the source entity. An Incorta Analyzer table’s runtime security filter should be considered a downstream runtime security filter to any source entity runtime security filters.
As an example, given a physical schema table, containing customer account information, that has a runtime security filter applied restricting which customer IDs are displayed. When you create an Incorta Analyzer table using the customer account information table, it will only have access to the source table’s information after the runtime security filter has been applied. The Incorta Analyzer table can then apply its own runtime security filter, further restricting its own data visibility.
Parent Joins
The Incorta Analyzer table is the parent table in a join relationship to another entity object in the physical schema. By default, the join condition represents a left outer join where the child is on the left side of the join and the parent is on the right side of the join.
An Incorta Analyzer table does not support self joins where the Incorta Analyzer table represents both the parent and child tables in the join.
A parent table typically has one key column or composite key columns. In both cases, the key column functions as a primary key. Where there is a key column, the join condition typically contains a join operator an = equals join operator.
In releases before 6.0.3, an Incorta Analyzer table would not allow the function of an Incorta Analyzer table column or Incorta Analyzer table formula column to be a key. Even if the source table had declared the column function as a key. Take this into account when using an Incorta Analyzer table as a parent within a join.
However, starting with 6.0.3, you can specify key columns for Incorta Analyzer and SQL tables. Adding, removing, or changing key columns does not require running a load job as derived tables are refreshed as part of schema update jobs. The derived table’s unique index is calculated and saved as a snapshot DDM file each time the key columns are updated or the schema or table is loaded.
Make sure that the column or columns that you designate as key maintain row uniqueness because no deduplication is performed for derived tables. If the selected key columns result in duplicate key values:
- During the schema update job, duplicate values are kept, and the Engine will return the first matching value whenever a single value of the key columns is required. The schema update logs will point out the unique index issue.
- During the schema or table load job, the unique index calculation will fail, resulting in a finished-with-error load job. No value is returned when the unique index is required. You must select the correct key columns to have the unique index calculated.
It is possible to create a join from a child to parent without using a defined key column in the parent table. A table without a key column allows for duplicate values. If the parent table contains duplicate non-key rows, the Loader Service randomly selects a parent table row based on the non-key join. To help simulate a key for the parent table, consider using one or more conditions in the join definition such as > greater than and < less than. Multiple join operators may significantly narrow join values from the parent table so as to create row uniqueness. A join condition with multiple join operators requires internal sorting which may require high calculation costs for the join path.
Whenever possible, create a key column or a composite key column for a parent table. Avoid making a join to a parent table that does not have a key column or composite key columns.
Avoid implicit casting or conversions for disparate data types in a join between a child and parent table. In other words, the child table column and the parent table column should be of the same data type.
Child Joins
The Incorta Analyzer table is the child table in a join relationship to another entity object in the physical schema or another physical schema within the tenant. A child table typically has a column that serves as a foreign key. By default, the join condition represents a left outer join where the child is on the left side of the join and the parent is on the right side of the join.
Avoid implicit casting or conversions for disparate data types in a join between a child and parent table. In other words, the child table column and the parent table column should be of the same data type.
Additional Considerations
Materializing Incorta Analyzer table to parquet files
In general, the Loader Service creates a new parquet file for an Incorta Analyzer table during a load job or during a schema update job that impacts the columns referenced in the Incorta Analyzer table. When the Loader Service fails to materialize an Incorta Analyzer table for any reason, it writes an empty parquet file for this object. There will be no compacted versions of the Incorta Analyzer table parquet files. Thus, Spark and SQLi will read from the original uncompacted parquet files.
The Loader Service creates a new parquet file for an Incorta Analyzer table in the following cases:
- When you create or update the Incorta Analyzer table
- When you update the source table column(s) that you reference in the Incorta Analyzer table in the same physical schema
- When you update the source table column(s) that you reference in the Incorta Analyzer table in another physical schema and load the Incorta Analyzer table
- When you delete the source table of the Incorta Analyzer table in the same physical schema (empty parquet file)
- When you delete the source table column(s) that you reference in the Incorta Analyzer table in another physical schema and load the Incorta Analyzer table (empty parquet file)
When you delete or update the source table that you reference in an Incorta Analyzer table in another physical schema, the Loader Service will not automatically update the Incorta Analyzer table. However, when you load the Incorta Analyzer table that references a deleted source table, whether in the same or another physical schema, the load fails and the Loader Service writes an empty parquet file for the Incorta Analyzer table.
Importing schemas
When importing physical schemas with Incorta Analyzer tables make sure that these tables do not have encrypted columns, key columns (supported starting with 6.0.3), load filters, or self joins to avoid rendering issues at the dashboard level or load failure.
Referencing Incorta Analyzer table columns in materialized views
Although you can reference Incorta Analyzer table columns in materialized views, you cannot reference columns in Incorta tables that exist in the same physical schema as the materialized view. In addition, a materialized view column cannot reference an Incorta Analyzer table column that references a business schema view.
Incremental load
When utilizing an incremental load, be aware that there are no configuration options for an Incorta Analyzer table. You cannot load Incorta Analyzer tables incrementally; only full load and from staging is supported.