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.

Note

Prior to Release 2021.3.2, an Incorta Analyzer table persisted to shared storage as Direct Data Mapping (DDM) files, but not as Apache Parquet files. Materializing Incorta tables to parquet files leads to a longer load time than in older releases.

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
Important

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
Important

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.

Important

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.

Important

An Incorta Analyzer table will 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 has declared the column function as a key. Take this into account when using an Incorta Analyzer table as a parent within a join.

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.

Recommendation

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.

Warning

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.

Warning

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)
Note

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 from older releases

When importing physical schemas with Incorta Analyzer tables from older releases (before 2021.3.2), make sure that these tables do not have encrypted columns, key columns, 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.