1. Home
  2. On-premises 5.2
  3. Guides
  4. Tours
  5. Concepts
    1. Aggregate filter
    2. Alias
    3. Apache Avro™
    4. Apache Kafka™
    5. Applied filter
    6. Base table
    7. Bookmark
    8. Business schema
    9. Business schema view
    10. Business schema view column
    11. Business schema view formula column
    12. Column
    13. Connector
    14. Dashboard filter
    15. Dashboard folder
    16. Dashboard runtime filter
    17. Data agent
    18. Data alert
    19. Data destination
    20. Date system variable
    21. Distinct filter
    22. Expression
    23. External data source
    24. External session variable
    25. Filter expression
    26. Filter expression session variable
    27. Filter option
    28. Formula column
    29. Formula expression
    30. Full load
    31. Global Variable
    32. Incorta Analyzer Table
    33. Incorta Analyzer Table column
    34. Incorta Analyzer Table formula column
    35. Incorta Analyzer View
    36. Incorta Analyzer View formula column
    37. Incorta SQL Table
    38. Incorta SQL View
    39. Individual filter
    40. Insight filter
    41. Internal query expression
    42. Internal session variable
    43. Join
    44. Join condition
    45. Join condition column
    46. Join filter
    47. Join filter column
    48. Level based measure
    49. Load filter
    50. Local data file
    51. Local data folder
    52. Materialized View
    53. Materialized View Column
    54. Materialized View Formula Column
    55. Measure filter
    56. Miscellaneous system variable
    57. Multi-Source table
    58. Physical schema
    59. Physical schema table
    60. Physical schema table column
    61. Physical schema table formula column
    62. Physical schema table runtime security filter
    63. Presentation variable
    64. Prompt
    65. Query system variable
    66. Runtime business view
    67. Runtime business view formula column
    68. Runtime security filter
    69. Session variable
    70. Shared Storage
    71. SQL Interface
    72. Upgrade Readiness
    73. Variable
    74. Visualization
  6. Tools
  7. References
  8. Integrations
  9. Data applications
  10. Releases

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 5.1.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: Key columns in Analyzer Tables

In releases before 5.2.11, 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 5.2.11, 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, the Engine returns the first matching value whenever a single value of the key columns is required.

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, make sure that these tables do not have encrypted columns, key columns (supported starting with 5.2.11), 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.