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.

Property	Description
Table	The 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.
Type	The type of the physical schema object, and each with a specific color. “Multisource” indicates a multi-source physical schema table.
Data Source	This 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.
Performance	The status of performance optimization: ● Optimized ● Not optimized This property does not apply to aliases.
Columns	The number of columns in the physical schema object including both data-backed columns and formula columns
Joins	The number of both child and parent join relationships related to the physical schema object
Rows	The number of rows of the physical schema object. Not available in the schema draft mode.
Data Size	The 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 Details	Select to access the Table Editor to view or manage the properties of the physical schema object
More Options → Load Table	Select 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 Staging	Select 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 → Remove Table	Select 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:

Create one or more physical schema tables using the Schema Wizard
Create a physical schema object
Search for a physical schema object or column
View physical schema object details
Edit a physical schema object
Delete a physical schema object
Specify and arrange the columns of the object list
Sort the object list
Filter the object list
Start a load job of an 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

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

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 + New → Table.
- 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 + New → Derived Table → Incorta 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 + New → Derived Table → Incorta 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 + New → Materialized 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.

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.