Tools → Table Editor

About the Table Editor

The Table Editor allows you to define the properties of physical schema objects. The physical schema objects include the following:

Note

The Incorta SQL table is available starting from release 5.0 as a labs feature.

You must have proper permissions and access rights to access the Table Editor and edit the physical schema objects. You can also access the Table Editor to view the object details.

Note

This document captures the new user interface (UI) of the Table Editor introduced in the 2022.3.0 release.

Table Editor permissions and access rights

A user that belongs to a group with the Schema Manager or the SuperRole role can access the Table Editor for a given object in a physical schema that this user owns or has at least View access rights to.

A user that belongs to a group with the Schema Manager or the SuperRole role can access the Table Editor for a given object in a physical schema that this user owns or has View access rights to. If the Enable Super User Mode option is enabled in the Cluster Management Console (CMC) → Tenant Configurations → Security, the Super User that is a Tenant Administrator and any user with the SuperRole can access the Table Editor for a given object and edit it regardless of the access rights.

A user with only the Schema Manager role must own or have Edit access rights to the physical schema to be able to use the Table Editor to edit its objects.

You can access the Table Editor for an object in a physical schema using the:

Access the Table Editor using the Schema Designer

There are multiple options or paths that you have to access the Table Editor for a given object using the Schema Designer for a physical schema that you own or have access rights to:

  • Create a new object, by selecting either + in the Tables tab or + New and an object type in the Action bar.
  • In the Tables tab, for a given object, select More Options (⋮ vertical ellipsis), and then select Table Details.
  • In the Tables tab, select a given object.

Access the Table Editor using the Schema Diagram Viewer

You can access the Table Editor for a given object in a physical schema that you own or have access rights to by using the Schema Diagram Viewer of:

  • the same physical schema
  • another physical schema that this object has a join relationship with one of its objects

To access the Table Editor using the Schema Diagram Viewer, select an object in the diagram. In the object details drawer, select the square with arrow icon next to the object name.

Access the Table Editor using the Load Job Viewer

You can access the Table Editor for a given object in a physical schema that you own or have access rights to by using the Load Job Viewer of the physical schema.

Here are the steps to access the Table Editor using the Load Job Viewer:

  • In the Jobs list view, if required, select a load job.
  • In the Load Job Details list view, in the Name column, select the name of an object.

Table Editor anatomy

The Table Editor consists of the following:

  • Action bar
  • Table Summary section
  • Columns tab
  • Data Sources tab
  • Joins tab
  • Filters tab
Important

The availability of sections and options in the Table Editor is subject to both the user access rights to the physical schema and the type of the physical schema object. For more details, see Table Editor access rights matrix.

In releases before 2022.6.0, make sure that you save the updates you do on the Columns tab (select Done) before making changes on the Joins tab to avoid losing your updates.

Action bar

The Action bar shows the following, as applicable:

  • Breadcrumbs trail for the Tables Editor as follows: schema_manager_link / physical_schema_link / current_object_name
  • Name: The name of the object. You cannot change this name once saved.
  • + New: select to open the New menu. The following are the available options depending on the object type.
    • Data Sources: select to add a new data source to the current physical schema table to create a multi-source table.
    • Formula Column: select to create a new formula column, available only for physical schema tables and materialized views.
    • Regular Security Filter: select to add a new regular runtime security filter
    • Formula Security Filter: select to add a new formula runtime security filter
    • Join (as Parent): select to add a new join relationship for the object as a parent
    • Join (as Child): select to add a new join relationship for the object as a child
  • Done: select to save the changes you made and exit the Table Editor.
  • Cancel: select to cancel the changes you made, if any, and exit the Table Editor.
Note

The Formula Column, Join (as Parent), and Join (as Child) options will not be available in the + New menu for a new unsaved object that you create without using the Schema Wizard. You must save the object first, and then re-open it for editing to use these options.

Warning

Starting the 2021.4.1 release, changes that you make using the Table Editor, such as updating the object properties or adding formula columns, are saved to a draft version and will not be published till you promote the changes to a saved version, that is, select Save Changes in the Schema Designer. For more details, refer to the Schema Designer modes.

Table Summary section

The following table shows the details and options available in the Table Summary section.

PropertyDescription
ColumnsThe number of columns in the physical schema object including both data-backed columns and formula columns
JoinsThe number of joins where the physical schema object is the Child or Parent table
RowsThe number of rows of the physical schema object. Not available in the schema draft mode.
Data SizeThe size of the object files on disk including the Direct Data Mapping (ddm) files and Parquet files. Not available in draft mode.
IncrementalThe status of the incremental feature: On or Off. Only physical schema tables and MVs can have this feature on.
Performance OptimizedEnabled by default.
The Analytics Service will load performance-optimized objects into the Analytics Service in-memory Direct Data Mapping engine. Toggle this option off to save the object data into Shared Storage only.
You cannot disable performance optimization for Incorta SQL tables and Incorta Analyzer tables from the Table Editor.
Enabling performance optimization for non-optimized objects requires loading the object data from staging.
Disable Full LoadThis option is available only for physical schema tables and MVs enabled for incremental loads. Enabling this toggle will prevent the accidental deletion of data from a snapshot table, where the data in Incorta is more granular than the source data.
In the case of physical schema tables and MVs that have incremental load enabled and full load disabled, the Loader Service will skip them during the full load jobs. Typically, a schema developer performs a full load of an object at least once before enabling this property.
Enforce Primary Key ConstraintThis option is available starting with release 2021.3.1. It is available in the case of a physical schema table or MV with a key column. It is disabled by default starting from the 2022.4.0 release.
You can enable it to enforce the calculation of the primary key index or disable it to skip this calculation to optimize data load time and performance.
  ●  When enabled, during a full load job, the Loader Service calculates the Primary key index to enforce record uniqueness.
  ●  When disabled, the Loader Service skips this calculation. Disable it only if you are sure your dataset has unique records.
This scenario applies to full loads only. In incremental load jobs and when key columns are defined, the Loader Service must compare existing and new data to avoid data duplication.
Note: Enforce Primary Key Constraint

In releases before 2023.7.0, when the Enforce Primary Key Constraint option was disabled for physical tables or MVs, and the selected key columns resulted in duplicate key values, unique index calculations would not fail, the first matching value was returned whenever a single value of the key columns was required.

Starting with release 2023.7.0, in such a case, the unique index calculation will fail, and the load job will finish with errors. You must either select key columns that ensure row uniqueness and perform a full load or enable the Enforce Primary Key Constraint option and load tables from staging to have the unique index correctly calculated.

Message banner

The message banner shows messages as appropriate. For example, the Table Editor shows a message if the object is in a physical schema of which you already have a draft version and the Schema Designer is in the saved version mode. The message denotes that if you make new changes, you will overwrite the existing draft and lose related changes. Selecting the message opens the draft so that you can edit it and make additional changes. If the current object has related changes in the draft version, these changes are loaded.

In addition, when accessing the Table Editor for an Incorta Table, SQL Table, or MV while in draft mode, a message appears confirming that you should publish your changes if the current object is based on another object that is newly created in the current draft.

Columns tab

The Columns tab consists of two sections: the Columns section and the Formula Columns section. You can collapse and expand each section as appropriate.

Columns section

The Columns section shows the following:

  • The number of data-backed columns in the physical schema object
  • Manage Output: This option is available only for a physical schema table with multiple data sources. Select to manage the mapping between columns in the different datasets and select the data type for each output column in the multi-source table.
  • Search box: search for object columns.
  • Preview Data: select to preview sample data of the object. Available only in the case of a loaded physical schema table or materialized view.
  • Columns in the physical schema object. For a loaded object, you can preview samples of the column data. You can also view a list of column dependencies, that is, entities where this column or one of its dependent entities is referenced. The 2023.7.0 release introduced additional enhancements to this feature, displaying not only dependent entities (downstream lineage) but also entities that this column references (upstream lineage).
  • Column properties: determine how the Loader Service extracts and stores data as well as how the Analytics Service loads data. Some properties determine how Incorta stores the column data; other properties influence the column’s representation and default behavior in tools such as the Analyzer and Formula Builder. You cannot change the column properties for an alias.

The Table Editor shows the properties of a column as follows:

PropertyControlDescription
Column Nameread-onlyThe name of the column; used in the column fully qualified name.
You cannot change the name of a column in the Table Editor; however, for a materialized view or an Incorta SQL table, you can edit the data source and use the Query Editor to add an alias name to the column using an AS clause for example.
Hover over the column name and select the DNA icon to access the Data Lineage Viewer for the respective column. This behavior has changed starting with 2023.7.0 as the Lineage property is now available on the columns list.
Column Labeltext boxThe column’s user-friendly name that appears in the Analyzer and on insights. You can edit it in the Columns tab only for a physical schema table or MV. However, you can use the Analyzer or the Query Builder to change it for Incorta Analyzer Tables and Incorta SQL Tables respectively. You cannot change it for aliases as it is inherited from the source object.
Column Typedrop down listThe Incorta Data Type of the column:
  ●  date
  ●  double
  ●  geo
  ●  integer
  ●  long
  ●  null
  ●  string
  ●  text
  ●  timestamp
You can change the type of columns in a physical schema table and MVs only.
Functiondrop down listThe Incorta function of the column:
  ●  key
  ●  dimension
  ●  measure
You can specify the key function for a column in a physical schema table or a materialized view only. A column with a data type of double cannot be a key column.
Descriptiontext boxEnter a description of the column
EncrypttoggleSpecifies whether or not to encrypt the column data in Shared Storage.
  ●  This property is available only for physical schema tables and materialized views.
  ●  A column in an alias inherits this property from the original source column, and you cannot change it.
  ●   Incorta Analyzer tables and Incorta SQL tables do not support encrypted columns.
  ●   Key columns cannot be encrypted.
Show in AnalyzercheckboxSpecifies whether or not to show the column as a selectable column in the Data Panel in the Analyzer
Auto Detect Data TypecheckboxAvailable only in the case of a multi-source table starting from the 2022.1.0 release.
  ●  Select the checkbox to enable the auto-update of the column data type every time the data sources are validated.
  ●  Clear the checkbox to lock the data type of the respective output column.
Data LineagelinkAvailable starting with 2023.7.0.
Select the Lineage link to open the Data Lineage Viewer v2 that displays a diagram with the entities referenced in the respective column (upstream lineage) and entities where the respective column is referenced (downstream lineage).
Note

Incorta infers the data type of a column in an Incorta SQL table, Incorta Analyzer table, or alias depending upon the source column. Only for an Incorta SQL table can you use a SQL built-in function to specify a different type for a column. For a physical schema table and a materialized view, Incorta initially infers the data type of a column depending upon various options; however, you can modify the type.

Some databases, such as Oracle and SQL Server, store a time component for a column of the type DATE. For this reason, Incorta will infer a Timestamp data type for a database DATE column.

Important

A key column cannot be encrypted and you cannot define the encryption status for a key column. If you change the function of an encrypted column to key, you have to set the encryption status to false.

Warning

If you add, change, or remove a key column or a composite key for a physical schema object (table or materialized view), you must perform a full load of this object. If the object is part of a join, you must perform a full load of the physical schema.

To learn about a column in a physical schema table, review Concepts → Physical Schema Table Column.

To learn about a column in a materialized view, review Concepts → Materialized View Column.

To learn about a column in an Incorta Analyzer Table, review Concepts → Incorta Analyzer Table Column.

Column dependencies (2022.4.0-2023.4.1)

The 2022.4.0 release introduced this new beta feature to help you, as a schema developer, better manage schema updates and load jobs. You can use the Data Lineage Viewer to preview a list of entities where a column is referenced, whether directly or via another dependent entity. Various enhancements have been introduced in the following releases including the data lineage diagram, besides the tabular view, and the tracking of dependencies of formula columns in physical schema tables and MVs. This feature was also extended to track the dependencies of columns and formula columns in business views.

Dependent entities can be columns and formula columns in physical schema objects or business views, joins, filters, and dashboard insights. The list of dependent entities is not limited to the entities where the column is directly referenced. All entities that can be impacted by the update or deletion of this column are listed. You can download the dependency list to a CSV file. For information about how to preview the dependencies of a column in the Data Lineage Viewer, see View column dependencies

Important

When you create or update objects in a physical schema, you must save the changes to a published version of the physical schema to have the dependency lists of referenced columns and their source columns updated.

After you successfully create an MV that references an object and save the changes, the dependency list of the referenced columns or their dependent entities will be automatically updated to show the dependent MV.

Column upstream and downstream data lineage

A new version of the Data Lineage Viewer is available as a preview feature starting with 2023.7.0. This version shows both the source (upstream lineage) and destination (downstream lineage) of data. Upstream data lineage refers to entities that the current entity references and gets impacted by while downstream data lineage refers to entities where the current entity is referenced. The new version also tracks the lineage of tables, views, dashboards, insights, session variables and global variables. You can use the Data Lineage Viewer v2 to view the upstream and downstream lineage of columns and formula columns in physical schema objects.

For more details, refer to Data Lineage Viewer v2.

Formula Columns section

The Formula Columns section is available only in the case of physical schema tables and materialized views to create and manage formula columns for the physical schema object. An alias inherits formula columns from the original physical schema table or materialized view; you cannot manage them for the alias itself.

Note

You cannot add a formula column to a new unsaved object that you create without using the Schema Wizard. You must save the object first, and then re-open it for editing to add a formula column.

This Formula Columns section shows the following:

  • The number of formula columns in the physical schema object, if any
  • + Add Formula Column: available in the case of a saved object when there are no existing formula columns in this object
  • The formula columns in the physical schema object, if any. You can view the list of dependent entities of a formula column starting with the 2022.9.0 release while you can preview both the upstream and downstream data lineage of a formula column starting with 2023.7.0.

For each formula column, the Formula Columns section shows the following:

PropertyControlDescription
Nametext boxThe name of the formula column; used in the column fully qualified name. Hover over the column name and select the DNA icon to access the Data Lineage Viewer for the respective formula column. This behavior has changed starting with 2023.7.0 as the Lineage property is now available on the formula columns list.
Labeltext boxThe user-friendly name of the formula column
Typeread onlyThe Incorta Data Type of the formula column that the formula output determined automatically:
  ●  date
  ●  double
  ●  integer
  ●  long
  ●  null
  ●  string
  ●  text
  ●  timestamp
Functiondrop down listThe Incorta function of the formula column:
  ●  dimension
  ●  measure
Column Formulatext boxThe formula expression of the formula column, if any. Select the text box to launch the Formula Builder to add or edit the column formula expression.
ShowcheckboxSpecifies whether or not to show the formula column as a selectable column in the Data Panel in the Analyzer
Data LineagelinkAvailable starting with 2023.7.0.
Select the Lineage link to open the Data Lineage Viewer v2 that displays a diagram with the entities referenced in the respective formula column (upstream lineage) and entities where the respective formula column is referenced (downstream lineage).
Deletetrash can iconSelect to remove the formula column

Hover over the column name and select the DNA icon to access the Data Lineage Viewer for the respective column.

To learn more about formula columns in a physical schema table, review Concepts → Physical Schema Table Formula Column.

To learn more about formula columns in a materialized view, review Concepts → Materialized View Formula Column.

Note

Although you cannot use the Table Editor to create or manage formula columns for an Incorta Analyzer table, you can use the Analyzer to create and manage them. To learn more about Incorta Analyzer Table formula columns, review Concepts → Incorta Analyzer Table Formula Column.

Important: Validation Rules for a formula column name

A formula column name…

  • must begin with an alpha character (lower or upper case), underscore (_), or Dollar sign ($) character
  • after the first character, can contain zero or more alphanumeric characters in lower, upper, or mixed case
  • besides underscore (_) and Dollar sign ($) characters, cannot contain special characters, symbols, or spaces
Formula column type inference

Based on the output of the formula expression, the Table Editor will automatically infer the data type of a formula column. You can modify the formula expression to explicitly cast the data type of the return value. For example, you can use a built-in function, such as int(), to specify that the formula expression returns an integer value.

Data Sources tab

The Data Sources section contains the following:

  • The number of data sources.
  • + (addition icon): select to add a new data source. This option is available only in the case of a physical schema table.
  • The data source(s) of the physical schema object. There can be one or more data sources depending upon the physical schema object type.
    • Only a physical schema table can be single-source or multi-source depending upon the connector you use for the data source.
    • Besides physical schema tables, all other objects cannot be multi-source. An alias, Incorta Analyzer table, Incorta SQL table, and materialized view must have one and only one data source; they are single-source objects.
    • An alias does not actually have an external data source. It rather references another object in the same physical schema or another physical schema.
  • For each data source, the list shows the following:
    • Type: this property displays different information depending on the object type.
      • In the case of a physical schema table, this property displays the type of the dataset whether File System, SQL Database, XML, Salesforce, …etc.
      • In the case of other physical schema objects, this property shows the object type: Alias, Incorta Analyzer, Incorta SQL, or Materialized View.
    • Data Source/Language: this property can display one of the following:
      • The data source in the case of physical schema tables
      • The fully qualified name of the source object in the case of aliases
      • The script language in the case of MVs
      • The type of the object: Incorta Analyzer or Incorta SQL
    • Incremental: this property shows whether the data source has incremental load enabled or not.
    • Delete (trash can icon): when there are multiple data sources, you can delete any of them. It is not available when only one data source is available.
  • Select a data source to access the Table Data Source dialog to view or edit the data source properties.
    • In the case of an Incorta Analyzer table, you can view a sample of the object data in the Table Data Source dialog.
    • When you edit the data source properties of a physical schema table and select to save without discovery or when you select to save only the script of the MV in the data source properties, a note appears in the Data Source/Language column confirming that the object is not validated. This note continues to appear till you validate your updates.
Note

The details and properties of a data source vary according to the type of the physical schema object, the connector used to access the external data source, and the type of the dataset (SQL database, file system, data lake, JSON, Kafka, etc.). To learn how to create and manage an external data source, see Tools → Data Manager. To discover the various connectors, see Connectors → All.

Note

Starting the 2022.9.0 release, you can test a portion of the query of Incorta SQL tables and SQL-based data sources, such as IBM DB2, MySQL, Oracle, NetSuite Suite Analytics, Apache Hive, MongoDB BI, and Microsoft SQL Server. Select the portion you want, and then select Execute. However, the Table Editor uses the whole script to create the table.

Joins tab

The Joins tab shows all join relationships where the current object is the Parent table or the Child table.

Parent Joins section

The Parent Joins section shows all the join relationships, if any, where the current physical schema object is the Parent table (the object on the right side of the join). With the suitable access rights and permissions, you can add, edit, and delete join relationships in the Parent Joins section if both the Parent and Child objects exist in the same physical schema. However, you cannot edit or remove parent joins with objects in another physical schema. You can access and manage them from the Table Editor of the Child object.

To learn more about joins, review Concepts → Join and Tools → Join Editor.

Note

You cannot add a parent join for a new unsaved object that you create without using the Schema Wizard. You must save the object first, and then re-open it for editing to add a Parent join.

The Parent Joins section shows the following:

  • The number of join relationships, if any, where this object is the Parent table in the join
  • + (Add New Join): select to add a new join relationship for the object as a Parent table
  • The condition(s) of each existing join relationship; join filters do not show in the Table Editor. Each join condition defines the following:
    • The physical schema, object, and column of the Child side in the join (the object on the left side)
    • Join operator
    • The physical schema, object, and column of the Parent side in the join (the object on the right side). Typically, this is the current physical schema object.
  • More Options (⋮ vertical ellipsis): available only in the case that both the Parent and Child objects in the join relationship exist in the same physical schema. Available options are the following:
    • Join Details: select to access the Join Editor to manage the join conditions and filters
    • Remove Join: select to delete the join relationship
Warning

Incorta Analyzer tables and Incorta SQL tables do not support self joins. When you try to load a table of these with a self join, it will throw errors.

Child Joins section

The Child Joins section shows all the join relationships, if any, where the current physical schema object is the Child table (the object on the left side of the join). With the suitable access rights and permissions, you can add, edit, and delete join relationships in the Child Joins section whether both the Parent and Child objects exist in the same physical schema or in different physical schemas.

Note

You cannot add a Child join for a new unsaved object that you create without using the Schema Wizard. You must save the object first, and then re-open it for editing to add a Child join.

The Child Joins section shows the following:

  • The number of join relationships, if any, where this object is the Child table in the join
  • + (Add New Join): select to add a new join relationship for the object as a Parent table
  • The condition(s) of each existing join relationship; join filters do not show in the Table Editor. Each join condition defines the following:
    • The physical schema, object, and column of the Child side in the join (the object on the left side). Typically, this is the current physical schema object.
    • Join operator
    • The physical schema, object, and column of the Parent side in the join (the object on the right side)
  • More Options (⋮ vertical ellipsis): available options are the following:
    • Join Details: select to access the Join Editor to manage the join conditions and filters
    • Remove Join: select to delete the join relationship

Filters tab

The Filters tab has two sections: the Runtime Security Filters section and the Load Filter section. The Load Filter section is available only in the case of a physical schema table or MV. Both sections are not available for an alias.

Runtime Security Filters section

A runtime security filter is a filter expression that the Analytics Service applies when a user accesses an in-memory physical schema table, materialized view, Incorta Analyzer Table, or Incorta SQL Table. As an alias only references another physical schema object, you cannot create security filters for it. There are two types of runtime security filter: Regular and Formula.

The Runtime Security Filters section shows the following:

  • The number of runtime security filters specified for the physical schema object, if any
  • All the runtime security filters specified for the physical schema object, whether regular filters or formula filters.
  • For a formula security filter, the Runtime Security Filters section shows the filter expression for the security filter, if any. Select the text box to invoke the Formula Builder to create or edit the filter expression
  • For a regular security filter, the Runtime Security Filters section shows the following:
    • Table: the physical schema object to use one of its data-backed or formula columns. This can be the object itself or another object in the same physical schema that has a join relationship as a Parent table with the current object as the Child table.
    • Column: the filterable column in the object you select for the Table. This can be a data-backed column or a formula column.
    • Operator: the operator in the filter expression. This can be one of the following:
      • <: less than
      • : less than or equal to
      • =: equal to
      • or <>: not equal to
      • >: greater than
      • : greater than or equal to
      • IN
    • Value: the value in the filter expression. This can be a filter expression session variable, internal session variable, external session variable, or a global variable.
  • Delete (trash can icon): select to delete the runtime security filter

To learn more about runtime security filters, review Concepts → Runtime Security Filter.

Load Filter section

The Load Filter section is available only for a physical schema table or materialized view. This section shows the filter expression, if any, that the Analytics Service applies when loading the object data to the Engine memory from the extracted data on Share Storage. This filter restricts the rows available to the Analytics Service. Load filters do not affect data that the Loader Service extracts from the data source.

Select the Load Filter text box to invoke the Formula Builder to create, edit, or clear the load filter expression. To learn more about load filters, review Concepts → Load Filter.

Table Editor actions for a physical schema object

With the Table Editor, you can perform the following actions for a physical schema object, as applicable:

Updates that require data load

Some updates you make to the physical schema objects require loading data, either load from source (full load) or load from staging.

The following are the updates that require load from staging:

  • Adding, updating, or removing the load filter
  • Enabling performance optimization for non-optimized objects

The following are the updates that require full load:

  • Adding a new physical schema table or materialized view
  • Changing the data type of a physical schema table column or MV column
  • Changing the source of a materialized view or physical schema table, whether by selecting another source file in the Data Source properties dialog or editing the query
  • Adding or changing a key column (changing the column function from key to dimension or measure and vice versa) in a physical schema table or MV
  • Adding a new physical schema table column
  • Adding a new MV column
  • Changing the object type, for example, changing a physical schema table to an Incorta Analyzer table or MV
  • Removing a physical schema table column or a materialized view column that functions as a key
  • Changing the encryption status of one or more columns in a physical schema table or materialized view

In this case, when you select Done in the Table Editor, and then select Save Changes in the Schema Designer, the following occurs:

  • A warning dialog appears denoting that you have to perform data load. The dialog lists the objects and the required load type for each.
  • The message banner in the Schema Designer will keep showing the objects that require data load till you or another user performs the required load or a scheduled load job runs the required load for the related object(s).
  • The Model Status for this object in the Schema Manager and the object record in the Schema Designer will show a triangle with an exclamation mark.
Note

If you make changes to a single object while these changes require both a full load and a load from staging, the warning dialog will show that the required load type is full load.

Table Editor access rights matrix

The availability of sections and options in the Table Editor is subject to both the user access rights to the physical schema and the type of the physical schema object.

The following table shows the options and sections available for each object type and the least access rights required to the related physical schema to perform the action:

Option/Object TypePhysical Schema TableIncorta Analyzer TableMaterialized ViewIncorta SQL TableAliasLeast Access Rights
Define object propertiesEdit
Save updatesEdit
Cancel and exitView
+New → Formula Column___Edit
+New → Security Filter_Edit
+New → Join (as parent)Edit
+New → Join (as child)Edit
Manage Output and lock output column data types____Edit
Enable/Disable Optimized Performance_Edit
Enable/Disable PK index calculation___Edit
Enable/Disable Full Load___Edit
Add multi-source____Edit
Edit/delete data sourceThe reference to the source table and its physical schemaEdit
View data source detailsThe source table and its physical schemaView
Preview DataIn the Table Data Source dialog and Analyzer (the Analyzer requires Edit access rights)In the Query Editor only (requires Edit access rights)_View
Preview column dataView
View column dependenciesView
View column data lineageView
Search columnsEdit
Show column in Analyzer_Edit
Edit column propertiesShow in Analyzer and Function properties onlyShow in Analyzer and Function properties only_Edit
Edit/remove formula columns___Edit
Edit/remove runtime security filter_Edit
Edit/remove parent joinsEdit
Edit/remove child joinsEdit
View join propertiesView
Add/edit/remove a load filter___Edit
Warning

Although in releases before 2021.3.3 you can edit the performance optimization configuration for Incorta Analyzer tables and Incorta SQL tables, you must keep this feature enabled for these tables to avoid load failure.

Define a physical schema object

To start creating a physical scheme object, you need to use the + New menu in the Schema Designer or the + option in the Tables tab in the Schema Designer. Then, you specify the appropriate properties of the data source of the new object. After completing the data source definition, the Table Editor is shown to manage other properties as applicable and define the object name.

Defining one or more physical schema tables using the Schema Wizard does not require defining the object names in the Table Editor as the new objects inherit the data source name, whether this data source is a file, sheet in a spreadsheet file, a table or view in a database, or any other source. However, you can change or specify the table name in the Schema Wizard only when customizing the SQL query or creating a custom SQL table using a query-based data source.

For information about the different types of objects that you can create, refer to Tools → Schema Designer → Tables.

To discover more about the properties of different data sources, see Connectors → All, and refer to the suitable connector.

Important: Validation Rules for a physical schema object name

An object name…

  • must be between 1 and 128 characters in length
  • must begin with an alpha character (lower or upper case), underscore (_), or Dollar sign ($) character
  • after the first character, can contain zero or more alphanumeric characters in lower, upper, or mixed case
  • besides underscore (_) and Dollar sign ($) characters, cannot contain special characters, symbols, or spaces
  • must be unique at the schema level
  • is case-sensitive

Update a physical schema object

  • In the Table Editor, make the necessary updates.
  • To save your changes, in the Action bar, select Done.

Enable or disable performance optimization

The performance optimization is enabled by default for all the physical schema objects. Here are the steps to disable it or enable it again:

  • Access the Table Editor for a given physical schema object.
  • In the Table Editor, in the Table Summary section, disable the Performance Optimized toggle to disable the Performance optimization, or enable the toggle to enable performance optimization.
  • In the Action bar, select Done.
Note

You can also use the Schema Manager or Schema Designer to enable or disable the performance optimization feature for multiple objects at the same time. For more information, refer to Optimize the performance for physical schema objects. Enabling performance optimization for non-optimized objects requires load from staging.

Enable or disable enforcing primary key index calculation

As for the 2022.4.0 release, the primary key (PK) index calculation is disabled by default for all new physical schema tables and materialized views with key columns. Here are the steps to enable it or disable it again:

  • Access the Table Editor for a given physical schema object.
  • In the Table Editor, in the Table Summary section, turn the Enforce Primary Key Constraint toggle on to enable the PK index calculation, or turn the toggle off to disable the PK index calculation.
  • In the Action bar, select Done.

Enable or disable full load

Here are the steps to disable full load for an object with incremental load enabled:

  • Access the Table Editor for a given physical schema object with incremental load enabled.
  • In the Table Editor, in the Table Summary section, enable the Disable Full Load toggle to disable full load, or disable the toggle to enable full load for the physical schema object.
  • In the Action bar, select Done.

Manage data sources

Only a physical schema table can have one or more data sources. Creating a multi-source table automatically results in validating the data sources and inferring the data types of the output columns. Incorta will automatically perform data type casting to avoid type conflicts. You can also perform manual type-casting but you need to ensure that data can be cast into the new data type.

All of the following actions also result in inferring the output column data types and casting these data types if required. However, starting with the 2022.1.0 release, you can lock the data type of one or more output columns in a multi-source physical schema table to avoid the data type being automatically cast in the following cases:

  • Accessing the respective multi-source table in the Table Editor
  • Adding a new data source to a multi-source table
  • Editing one of the data sources in the Table Data Source dialog and validating all data sources
  • Deleting one data source from a multi-source table with more than two data sources
Note

Deleting all additional data sources and keeping only one source releases the data type lock. In this case, adding a second data source will automatically enforce validating all remaining data sources and inferring and casting the data type of the output columns as required.

Create a multi-source physical schema table

You can add a data source to physical schema tables only. Adding more than one data source creates a multi-source table.

  • In the Table Editor, do one of the following:
    • In the Data Source(s) tab, select + (addition icon).
    • In the Action bar, Select + NewData Sources.
  • In the Table Data Source dialog, for Type, select the type of the data source.
  • For Data Source, select the data source.
  • Specify the required data source configuration according to the type and data source you select.
  • Select Add to check the data source properties and save the new data source.
  • In the Action bar, select Done.
Important

When enabling incremental load, you must enable the Incremental property for all the data sources in the multi-source table; otherwise you cannot save your changes.
In addition, if you use the Maximum Value of a Column for the Incremental Extract Using property, the column you specify for the corresponding Incremental Column must have the same range for all data sources. If this is not the case, use the Last Successful Extract Time method.
For more details, refer to Concepts → Physical Schema Table and Concepts → Multi-Source Table.

View data source details

  • In the Table Editor, in the Data Sources tab, select a data source.
  • In the Table Data Source dialog, review the data source properties.
  • Select x in the upper-right corner of the dialog or select Cancel to exit it.

Edit a data source

  • Access the Table Editor for a given physical schema object.
  • In the Table Editor, in the Data Sources tab, select a data source.
  • In the Table Data Source dialog, make the necessary configuration updates as applicable.
  • Select Validate to check the data source properties and save the updates.
  • In the Action bar, select Done.
Note

In the case that you edit a multi-source physical schema table, the Validate All option will be available instead of Validate to validate all data sources of the physical schema table. If you edit an alias, no validation is required.

Manage output columns for a multi-source physical schema table

  • In the Table Editor, in the Columns tab, select Manage Output.
  • In the Manage Output dialog, for each output column, do the following as required:
    • For Output, define the output column data type.
    • For Auto-detect data type, select the checkbox to enable the auto-update of the output column data type or clear the checkbox to lock the output column data type.
  • Select Ok.
  • Optionally, in the Columns section, update the column properties as required.
  • In the Action bar, select Done.

Remove a data source

You can remove a data source only from a multi-source table.

  • Access the Table Editor for a given physical schema table with multiple data sources.
  • In the Table Editor, in the Data Sources tab, for a given data source, select Delete (trash can icon).
  • In the dialog, select Delete.
  • In the Action bar, select Done.

Preview object sample data

You can preview sample data for loaded objects only.

  • In the Table Editor, in the Columns tab, select Preview Data.
  • In the Preview dialog, review the sample data. Select x in the upper-right corner of the dialog or select any other place in the Table Editor to exit the dialog.

Manage columns

Search for columns

  • In the Table Editor, in the Columns tab, in the Search box, enter a search term.
  • Review the list of columns.

Select columns to show in Analyzer

By default, all columns in a physical schema object appear in the Data panel in the Analyzer. However, you can specify which columns to show.

Here are the steps to specify which columns to show or hide in the Analyzer:

  • In the Table Editor, in the Columns tab, clear the checkbox for each column you want to hide in the Data panel; select the checkbox for the columns you want to show.
  • In the Action bar, select Done.

Preview column sample data

you can preview sample data for a column that is loaded.

  • In the Table Editor, in the Columns tab, in the Columns section, hover over the column you want.
  • Select the eye icon.
  • Review the column fully qualified name and sample data.
  • Select any other place in the Table Editor to exit the column data preview.

View column dependencies (2022.4.0-2023.4.1)

Here are the steps to view the dependencies of a column.

  • In the Table Editor, on the Columns tab, in the Columns section, hover over the column you want.
  • Select the DNA icon.
  • Use the Data Lineage Viewer to review the list and diagram of dependent entities.
  • Optionally, select the download icon to save the dependency list to a CSV file.

View column data lineage (2023.7.0+)

  • In the Table Editor, on the Columns tab, in the Columns section, for the column you want, select Lineage.
  • Use the Data Lineage Viewer v2 to review the upstream and downstream data lineage of the column.

Specify column properties

  • In the Table Editor, in the Columns tab, edit the properties of the column you want as applicable depending upon the object type.
    • You cannot change the column name in the Table Editor.
    • For Label, enter a user-friendly name for the column. This is not available for an Incorta Analyzer table nor an Incorta SQL table. However, you can use the Analyzer to specify the column label for an Incorta Analyzer table column, and use the Query Editor to specify another name and label for an Incorta SQL table column.
    • For Type, select the data type of the column.
    • For Function, specify the function of the column in Incorta.
    • In Encrypt, specify whether or not you want to encrypt the column data in Shared Storage.
    • For Auto-detect data type, select the checkbox to enable the auto-update of the output column data type or clear the checkbox to lock the data type of the respective output column.
  • In the Action bar, select Done.

Lock the data type of one or more output columns in a multi-source table

You can lock the data type of one or more output columns in a multi-source table either in the Columns tab or the Manage Output dialog.

Here are the steps to lock the output column data type in the the Columns tab:

  • Access the Table Editor for a given physical schema table with multiple data sources.
  • In the Table Editor, in the Columns tab, optionally, change the data type of the output columns.
  • For each output column that you want to lock its data type, clear the Auto-detect data type checkbox.
  • In the Action bar, select Done.

Here are the steps to lock the output column data type in the Manage Output dialog:

  • Access the Table Editor for a given physical schema table with multiple data sources.
  • In the Table Editor, in the Columns tab, in the Columns section, select Manage Output.
  • In the Manage Output dialog, optionally, define the data type for the output columns.
  • For each output column that you want to lock its selected data type, clear the Auto-detect data type checkbox.
  • Select Ok.
  • In the Action bar, select Done.

Manage formula columns

Create a formula column

You can add a formula column for a saved physical schema table or materialized view.

  • Access the Table Editor for a given physical schema table or materialized view.
  • In the Table Editor, do one of the following:
    • In the Columns tab, in the Formula Columns section, select +.
    • In the Action bar, Select + NewFormula Column.
  • For the new formula column, specify the following:
    • For Name, enter the name of the formula column used in the formula column fully qualified name.
    • For Label, enter a friendly-user name for the formula column.
    • The Type box shows the Incorta Data Type of the formula column that the formula output determines automatically.
    • For Function, specify the Incorta function of the formula column: dimension or measure.
    • Select the Column Formula box to show the Formula Builder to create the formula expression for the formula column. In the Formula Builder, select Validate & Save.
    • Select the Show checkbox to show the formula column as a selectable column in the Data Panel in the Analyzer.
  • In the Table Editor, in the Action bar, select Done.

Edit a formula column

  • In the Table Editor, in the Columns tab, for a formula column in the Formula Columns section, make the necessary updates.
  • In the Action bar, select Done.

Select formula columns to show in Analyzer

By default, all formula columns in a physical schema object appear in the Data panel in the Analyzer. However, you can specify which columns to show.

Here are the steps to specify which formula columns to show or hide in the Analyzer:

  • In the Table Editor, in the Columns tab, in the Formula Columns section, clear the Show checkbox for each formula column you want to hide in the Data panel; select the Show checkbox for the formula columns you want to show.
  • In the Action bar, select Done.

View formula column dependencies (2022.9.0-2023.4.1)

Here are the steps to view the dependencies of a formula column.

  • In the Table Editor, on the Columns tab, in the Formula Columns section, hover over the formula column you want.
  • Select the DNA icon.
  • Use the Data Lineage Viewer to review the list and diagram of dependent entities.
  • Optionally, select the download icon to save the dependency list to a CSV file.

View formula column data lineage (2023.7.0+)

  • In the Table Editor, on the Columns tab, in the Formula Columns section, for the formula column you want, select Lineage.
  • Use the Data Lineage Viewer v2 to review the upstream and downstream data lineage of the formula column.

Delete a formula column

  • In the Table Editor, in the Columns tab, in the Formula Columns section, for the formula column you want to delete, select Delete (trash can icon).
  • In the dialog, select Delete.

Manage joins

You can manage the joins related to an object when both the Parent table and the Child table are in the same physical schema or when this object is the Child table in a cross-schema join relationship.

Create a join as parent

You can add a join for saved physical schema objects only.

  • In the Table Editor, do one of the following:
    • Select the Joins tab, and then in the Parent Joins section, select +.
    • In the Action bar, Select + NewJoin (as Parent).
  • In the Join Editor, for the new join relationship, specify the join properties for the Child and Parent sides.
  • In the Join Editor, in the Action bar, select Done.

Create a join as child

You can add a join for saved physical schema objects only.

  • In the Table Editor, do one of the following:
    • Select the Joins tab, and then in the Child Joins section, select +.
    • In the Action bar, Select + NewJoin (as Child).
  • In the Join Editor, for the new join relationship, specify the join properties for the Child and Parent sides.
  • In the Join Editor, in the Action bar, select Done.

View join relationship properties

  • In the Table Editor, select the Joins tab, and then for a join relationship in the Parent Joins or Child Joins sections, do one of the following (if applicable):
    • Select the join relationship.
    • Select More Options (⋮ vertical ellipsis), and then select Join Details.
  • In the Join Editor, review the join conditions and filters, if any.
  • To exit the Join Editor, in the Action bar, select Cancel.

Edit a join relationship

  • In the Table Editor, select the Joins tab, and then for a join relationship in the Parent Joins or Child Joins sections, do one of the following (if applicable):
    • Select the join relationship.
    • Select More Options (⋮ vertical ellipsis), and then select Join Details.
  • In the Join Editor, make the necessary updates.
  • In the Join Editor, in the Action bar, select Done.

Remove a join relationship

  • In the Table Editor, select the Joins tab.
  • For a join relationship in the Parent Joins or Child Joins sections, if applicable, select More Options (⋮ vertical ellipsis), and then select Remove Join.
  • In the dialog, select Delete.
  • In the Action bar, select Done.

Manage runtime security filters

You can add and manage runtime security filters for all physical schema objects, except for an alias as it inherits the runtime security filters of the original object.

Add a regular runtime security filter

  • In the Table Editor, do one of the following:
    • Select the Filters tab, and then in the Runtime Security Filters section, select +Regular Security Filter.
    • In the Action bar, Select + NewRegular Security Filter.
  • For the new runtime security filter, specify the following:
    • For Table, select the physical schema object to use one of its data-backed or formula columns for the filter expression. This can be the object itself or another object in the same physical schema that has a join relationship (as a Parent table) with the current object (as the Child table).
    • For Column, select the filterable column in the object you selected for the Table. This can be a data-backed column or a formula column.
    • For Operator, select the operator in the filter expression.
    • For Value, select the value in the filter expression. This can be a filter expression session variable, internal session variable, external session variable, or a global variable.
  • In the Action bar, select Done.

Add a formula runtime security filter

  • In the Table Editor, do one of the following:
    • Select the Filters tab, and then in the Runtime Security Filters section, select +Formula Security Filter.
    • In the Action bar, Select + NewFormula Security Filter.
  • For the new runtime security filter, select the text box to invoke the Formula Builder and create the filter expression.
  • In the Formula Builder, select Validate & Save.
  • In the Table Editor, in the Action bar, select Done.

Edit a runtime security filter

  • In the Table Editor, select the Filters tab, and then in the Runtime Security Filters section, do one of the following depending upon the security filter type:
    • For a regular security filter, make the necessary configuration updates.
    • In the case of a formula security filter, select the formula text box to access the Formula Builder and edit the filter expression. In the Formula Builder, select Validate & Save.
  • In the Table Editor, in the Action bar, select Done.

Remove a runtime security filter

  • In the Table Editor, select the Filters tab.
  • In the Runtime Security Filters section, for a security filter, select Delete (trash can icon).
  • In the dialog, select Delete.
  • In the Action bar, select Done.

Manage the load filter

You can create load filters only for physical schema tables and materialized views. As a load filter determines which rows of an object to be loaded into the Engine memory, load filters will be effective only when the object has performance optimization enabled. Adding, editing, or removing a load filter requires loading the object data from staging.

Add a load filter

  • Access the Table Editor for a given physical schema table or materialized view.
  • In the Table Editor, select Filters, and then in the Load Filter section, select the text box to invoke the Formula Builder to create the load filter expression.
  • In the Formula Builder, select Validate & Save.
  • In the Table Editor, in the Action bar, select Done.

Edit the load filter

  • Access the Table Editor for a given physical schema table or materialized view.
  • In the Table Editor, select Filters, and then in the Load Filter section, select the text box to invoke the Formula Builder and update the load filter expression.
  • In the Formula Builder, select Validate & Save.
  • In the Table Editor, in the Action bar, select Done.

Remove the load filter

  • Access the Table Editor for a given physical schema table or materialized view.
  • In the Table Editor, select Filters, and then in the Load Filter section, select the text box to invoke the Formula Builder and clear the load filter expression.
  • In the Formula Builder, select Validate & Save.
  • In the Table Editor, in the Action bar, select Done.