1. Home
  2. On-premises 5.2
  3. Guides
  4. Tours
  5. Concepts
  6. Tools
    1. Alert Editor
    2. Alias Sync Tool
    3. Analyzer
    4. Apache Parquet Merge Tool
    5. Avro Extractor Tool
    6. Business Schema Designer
    7. Business Schema Manager
    8. Cluster Management Console
    9. Command Line Interface
    10. Content Manager
    11. Dashboard Filters Manager
    12. Data Agent
    13. Data APIs for External Notebooks
    14. Data Manager
    15. Formula Builder
    16. Formula Validation Tool
    17. Inspector Tool
    18. Join Editor
    19. Load Job Viewer
    20. Model Update Viewer
    21. Notebook Editor
    22. Profile Manager
    23. Public API
    24. Query Plan Viewer
    25. Scheduler
    26. Schema Designer
      1. Schema Designer Joins
      2. Schema Designer Load Jobs
      3. Schema Designer Modes and Versions
      4. Schema Designer Tables
    27. Schema Diagram Viewer
    28. Schema Manager
    29. Schema Wizard
    30. Security Manager
    31. Table Editor
    32. Tenant Management Tool
    33. Versioning Migration Tool
  7. References
  8. Integrations
  9. Data applications
  10. Releases

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 → 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

You cannot control the name of a physical schema table that you create using the Schema Wizard. The physical schema table automatically inherits the name of the data source table, file name, or worksheet name.

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

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

Starting with 5.2.9, the Referenced Schema and Referenced Table lists are alphabetically sorted and searchable by the schema or object name respectively.

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.