Tools → Schema Designer → Tables

About the Schema Designer Tables

The Tables tab in the Schema Designer shows all objects created in the current physical schema if any. You can use the options available in this tab to add and manage all the physical schema objects. In addition, you can also use the options in the + New menu in the Action bar to add different types of objects to the physical schema. Physical schema objects can be physical schema tables, aliases, Incorta Analyzer tables, Incorta SQL tables, or Materialized Views (MV).

The Tables tab includes the following:

  • Search box: to search for a physical schema object or a column in the physical schema. The Object list is filtered to show objects whose name matches the search term and objects that have one or more columns that match the search term.
  • Show/Hide Columns: to specify the columns you want to show in the Object list. The following are the available columns to select from
    • Type
    • Data Source
    • Performance
    • Columns
    • Joins
    • Rows
    • Data Size
  • +: to open the Table Data Source dialog to create a new object in the physical schema
  • Object list: shows all objects available in the current physical schema. You can sort or filter this list using one of the available columns.

The following table shows a description of the columns and the options available in the Tables tab.

PropertyDescription
TableThe object name. If the object has changes that require loading data, a triangle with an exclamation mark icon continues to appear before the object name till the required load occurs, whether manually or through a scheduled job.
TypeThe type of the physical schema object, and each with a specific color. “Multisource” indicates a multi-source physical schema table.
Data SourceThis column shows any of the following:
  ●  The data source of a physical schema table
  ●  The fully qualified name of the source object for an alias
  ●  The script language of a materialized view
  ●  The type of the object: Incorta Analyzer or Incorta SQL table
In the case of a physical schema table or MV, if you edit the object data source and select to save the MV script only or to save the table updates without discovery, this property shows that the object is not validated, and this will continue to appear till you validate your updates.
PerformanceThe status of performance optimization:
  ●  Optimized
  ●  Not optimized
This property does not apply to aliases.
ColumnsThe number of columns in the physical schema object including both data-backed columns and formula columns
JoinsThe number of both child and parent join relationships related to the physical schema object
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.
More Options (⋮ vertical ellipsis)Contains some options to manage or load the object. The available options vary according to the object type and the mode.
More Options → Table DetailsSelect to access the Table Editor to view or manage the properties of the physical schema object
More Options → Load TableSelect to perform a full load of the object data. This option does not apply to aliases and is available in saved version mode only.
More Options → Load from StagingSelect to perform a load of the object data from Shared Storage. This option does not apply to aliases and is available in saved version mode only.
More Options → Open LineageAvailable starting with 2023.7.0.
Select to open the Data Lineage Viewer v2 that displays a diagram with the entities (variables, other schema objects, or business schema objects) referenced in the respective object (upstream lineage) and entities where the respective object is referenced (downstream lineage).
More Options → Remove TableSelect to delete the physical schema object
Note

During the load and post-load phase in a load job for a physical schema or the commit process, a schema update job cannot start. Although you can make changes (draft version) and can select to promote the draft to a saved version (select Save Changes), the update job will be in queue till the load job is completed.


Schema Designer actions for a physical schema object

Using the Schema Designer, you can perform the following actions for a physical schema object:

Important

When you add, edit, or delete an object, these updates are saved to the draft till you promote the changes to a saved version.

Note

When you exit the Physical Schema Designer, the default settings of the object list are restored including the object order, filter options, and column arrangement.

Create one or more physical schema tables using the Schema Wizard

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Action bar, select + New → Schema Wizard.
  • In (1) Choose a Source, for Select a Datasource, select the external data source for your new physical schema table(s).
  • In the Schema Wizard footer, select Next.
  • In (2) Manage Tables, in the Data Panel, specify the table(s) or file(s) you want to use to create the new physical schema tables. You can also select the Select All checkbox.
  • In the Schema Wizard footer, select Next.
  • In (3) Finalize, in the Schema Wizard footer, select Update Schema.

To learn more about using the Schema Wizard, review Tools → Schema Wizard.

Note

The physical schema table automatically inherits the name of the data source table, file name, or worksheet you select in the Schema Wizard. You can change or specify the name in the Schema Wizard only when customizing the SQL query or creating a custom SQL table using a query-based data source.

Warning

Starting the 2022.9.0 release, you can test a portion of the SQL query. Select the portion you want, and then select Execute. However, you must execute the whole query to be able to save the table. The wizard uses the executed script to create the table.

Create a physical schema object

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
  • except for underscore (_) and Dollar sign ($) characters, cannot contain special characters, symbols, or spaces
  • must be unique at the schema level
  • is case-sensitive
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.

Create a physical schema table

  • In the Schema Designer, do one of the following:
    • In the Action bar, select + NewTable.
    • In the Tables tab, select +.
  • In the Table Data Source dialog, for Type, select the data source type and specify the properties of the new physical schema table.
  • Select Add.
  • In the Table Editor, enter the name of the new physical schema table.
  • In the Table Editor, in the Action bar, select Done.

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

Create an alias

  • In the Schema Designer, in the Action bar, select + New → Alias.
  • In the Alias Table Source dialog, for Referenced Schema, select the physical schema of the original object that this alias references. This can be the current physical schema or another physical schema. The default is the current physical schema.
  • For Referenced Table, select the original object that this alias references.
  • Select OK.
  • In the Table Editor, enter the name of the new alias.
  • In the Action bar, select Done.

Create an Incorta Analyzer table

  • In the Schema Designer, do one of the following:
    • In the Action bar, select + NewDerived TableIncorta Analyzer.
    • In the Tables tab, select +, and then in the Table Data Source dialog, for Type, select Incorta Analyzer.
  • In the Table Data Source dialog, select the pen icon.
  • In the Analyzer, select the columns in the new Incorta Analyzer table and specify their properties.
  • In the Analyzer, select Save.
  • In the Table Data Source dialog, review the sample data, and then select Add.
  • In the Table Editor, enter the name of the new Incorta Analyzer table.
  • In the Table Editor, in the Action bar, select Done.

To learn more about the Analyzer, review Tools → Analyzer.

Create an Incorta SQL table

  • In the Schema Designer, do one of the following:
    • In the Action bar, select + NewDerived TableIncorta SQL.
    • In the Tables tab, select +, and then in the Table Data Source dialog, for Type, select Incorta SQL.
  • In the Table Data Source dialog, select the Query box or the pen icon.
  • In the Query Editor, enter the SQL code for the new Incorta SQL table, and then select Done.
  • In the Table Data Source dialog, select Add.
  • In the Table Editor, enter the name of the new Incorta SQL table.
  • In the Table Editor, in the Action bar, select Done.

Create a materialized view

  • In the Schema Designer, do one of the following:
    • In the Action bar, select + NewMaterialized View.
    • In the Tables tab, select +, and then in the Table Data Source dialog, for Type, select Materialized View.
  • In the Table Data Source dialog, for Language, select the script language of the new materialized view.
  • In the Script section, do one of the following:
    • To add the script code using the Query Builder, select the Edit Query.
    • To add the script code using the Notebook Editor, select Edit in Notebook.
  • Specify the MV properties as appropriate, and then select Add.
  • In the Table Editor, enter the name of the new materialized view.
  • In the Table Editor, in the Action bar, select Done.

To discover more about the properties of a materialized view, see Concepts → Materialized View.

Search for a physical schema object or column

  • In the Schema Designer, in the Tables tab, in the Search box, enter a search term.
  • The object list is filtered to show objects whose name matches the search term and objects that have one or more columns that match the search term.

View physical schema object details

  • In the Schema Designer, in the Tables tab, for a given physical schema object, do one of the following:
    • Select the physical schema object.
    • Select More Options (⋮ vertical ellipsis), and then select Table Details.
  • In the Table Editor, review the object properties.
  • To exit the Table Editor without saving updates, if any, in the Action bar, select Cancel.

Edit a physical schema object

  • In the Schema Designer, in the Tables tab, for a given physical schema object, do one of the following:
    • Select the physical schema object.
    • Select More Options (⋮ vertical ellipsis), and then select Table Details.
  • In the Table Editor, make the necessary updates.
  • To save updates, in the Table Editor, in the Action bar, select Done.

To learn more about using the Table Editor, review Tools → Table Editor.

View the data lineage of a physical schema object

This feature is available starting with 2023.7.0. You can view the upstream data lineage, which are the entities that this object references, whether other schema objects, variables, or business schema objects. Additionally, you can view the downstream data lineage, which are the entities that reference this object, whether other schema objects, variables, business schema objects, or dashboards.

Here are the steps to view the data lineage of an object.

  • In the Schema Designer, in the Tables tab, for a given physical schema object, select More Options (⋮ vertical ellipsis).
  • Select Open Lineage.
  • In the Data Lineage Viewer v2, review the upstream and downstream lineage of the respective object.

Delete a physical schema object

  • In the Schema Designer, in the Tables tab, for a given physical schema object, select More Options (⋮ vertical ellipsis), and then select Remove Table.
  • In the dialog, clear or keep selected the Remove all joins related to this table checkbox, as appropriate, and then select Delete.

Specify and arrange the columns of the object list

  • In the Schema Designer, in the Tables tab, select Show/Hide Columns.
  • By default, all columns are selected. Clear the selection of the respective checkbox or keep it to specify the columns you want to show in the object list.
  • Drag or move the columns to specify their order.
  • To restore the default column order and select to show all columns, select Reset.

Sort the object list

By default, the physical schema objects are sorted in ascending order by the object name. You can sort the object list in ascending or descending order by any of the following columns:

  • Table Name: alphabetical order
  • Data Source: alphabetical order
  • Columns: the number of columns
  • Joins: the number of joins
  • Rows: the number of rows
  • Data Size: the data size on disk

Here are the steps to sort the object list.

  • In the Schema Designer, in the Tables tab, in the object list, select the header of the column you want to sort by. The default is to sort in ascending order.
  • Select the same column header to sort the list in descending order by the respective column.

Filter the object list

You can filter the object list by the object type and the performance optimization status.

  • In the Schema Designer, in the Tables tab, in the object list, select the filter icon next to the header of the column you want to filter by.
  • Select the appropriate filter options, and then select OK. For example, select the filter icon in the Type column header, and then select the types of objects you want to show. Select OK to apply the filter.
  • To remove an applied filter, select the filter icon of the applied filter, and then select Reset.

Start a load job of an object

You can start a full load or a load from staging of a specific physical schema object instead of loading all objects in the physical schema. This option is not available for an alias.

Here are the steps to start a load job of an object.

  • In the Schema Designer, in the Tables tab, for a given physical schema object, select More Options (⋮ vertical ellipsis).
  • Do one of the following:
    • Select Load Table to start a full load job.
    • Select Load from Staging to start a load from staging job.
  • In the dialog, select Load.

For information about how to check the load job status, refer to Review the status of a load job.