Tools → Schema Designer

About the Schema Designer

The Schema Designer allows you to manage objects and joins for a physical schema. You also use the Schema Designer to initiate a load job of an entire physical schema or a given object in the physical schema and to schedule a load job of the physical schema.

Note

This document and its sub-documents capture the new user interface (UI) of the Schema Designer introduced in the 5.2 release.

Schema Designer permissions and access rights

A user that belongs to a group with the Schema Manager or the SuperRole role can access the Schema Designer for a given physical schema that the user owns or has at least 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 Schema Designer for all physical schemas and manage them 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 use the Schema Designer to manage its objects, joins, and load jobs. Some functions require additional roles, such as the Explore Data function which requires a role that can manage the Catalog (Content Manager).

Access the Schema Designer

To access the Schema Designer, in the Navigation bar, select Schema, and then on the Schemas tab, select a given physical schema.

You can also access the Schema Designer for a given schema from the Load Job Viewer. In the Header bar, in the breadcrumb trail, select the physical schema name.

Schema Designer access rights matrix

The following table shows the required access rights to a given physical schema for each action that you can perform using the Schema Designer.

Action/Access RightsOwn/EditShareView
Add or edit the physical schema descriptionView onlyView only
Search for a physical schema object
Search for a column in physical schema objects
Optimize the performance for physical schema objectsView onlyView only
Change the load order for the physical schema objectsView onlyView only
Share the physical schemaView only
Explore physical schema data (Requires a role that can manage the Catalog (Content Manager)
Preview the physical schema join diagram
Sort physical schema objects list view
Perform a full load of the physical schema data
Perform an incremental load of the physical schema data
Load physical schema data from staging
Schedule a physical schema load job
Perform a full load of a physical schema object
Load physical schema object data from staging
Review the status of a load job
Review the status of a schema update job
Create a physical schema load email notification
Create one or more physical schema tables using the Schema Wizard
Create a physical schema object
View physical schema object details
Edit a physical schema object
Delete a physical schema object
Create a join relationship
Sort joins list view
View join relationship properties
Edit a join relationship
Remove a join relationship
Create a physical schema draft
Search draft version history
Name a draft version
Preview a draft
Promote a draft to a saved version
Discard changes in a valid draft
Discard an obsolete or invalid draft
Search saved version history
Name a saved version
Export a saved version
Restore a saved version

Schema Designer modes

Starting release 5.1.2, the Schema Designer supports two different modes:

  • Saved Version mode
  • Draft mode

For information about each mode, refer to Tools → Schema Designer → Modes and Versions.

Warning

The time zones of the Incorta server and the metadata database server should be the same to avoid errors when creating or editing schema versions.

Schema Designer anatomy

The Schema Designer consists of the following:

  • Action bar
  • Summary section
  • Message banner
  • Tables tab
  • Joins tab

Action bar

The following table shows the options available in the Action bar.

OptionDescriptionComments
NameThe physical schema name
DescriptionThe physical schema description, if any
LoadSelect to open the Load menu. Available options are:
  ●  Full Load
  ●  Incremental Load
  ●  Staging Load
  ●  Schedule Load Job
For information about managing load jobs, refer to Tools → Schema Designer → Load Jobs.
Available only when you open a physical schema that has object(s) in the saved version mode
Load → Full LoadInstructs the Loader Service to start a full load job of the physical schemaFor objects with incremental load enabled and full load disabled, the Loader Service may throw errors during the first full load job or it will skip these objects during the next full load jobs.
Load → Incremental LoadInstructs the Loader Service to start an incremental load job. Only objects with incremental load enabled will be loaded incrementally while other objects will be fully loaded.Depending upon the data source properties, the Loader Service will use the Update Query, Incremental Script, or an Update File to determine which data to extract from the data source.
Load → Staging LoadInstructs the Loader Service to not extract data from the data source for the physical schema objects while the Analytics Service will load into its in-memory Direct Data Mapping engine the related files from Shared Storage (Staging).
Load → Schedule Load JobSelect to create a scheduled job for loading the physical schema data
Stop LoadSelect to stop or interrupt the activities of the running load job. The status of the load job becomes either Interrupted or Aborted.
Aborted is the status of a load job that is automatically forced to stop after exceeding the configured interruption timeout.
Available only during a running load job
ExploreSelect to access the Analyzer to explore the physical schema data. You can create tabular insights and charts and save them to new or existing dashboards. You can also create listing tables and save them as business schema views and save them to new or existing business schemas.Available only when you open a physical schema that has one or more objects in the saved version mode. It also requires a role that can manage the Catalog, that is, the Content Manager.
Settings (gear icon)Select to open the Settings menu. Available options are:
  ●  Edit Description
  ●  Performance Optimization
  ●  Change Load Order
  ●  Create Notification
  ●  Share
Available only when you open a physical schema in the saved version mode; however, it is not available during a load job
Settings → Edit DescriptionSelect to add or edit the physical schema description
Settings → Performance OptimizationSelect to manage which physical schema objects you want to enable for performance optimization.
  ●  For objects that you enable for performance optimization, the Analytics Service will load the related files from Shared Storage (Staging) into its in-memory Direct Data Mapping engine.
  ●  For unselected objects, the Loader Service will skip them while loading data into the engine memory.
Disabling this feature can slow the speed of creating insights; however, it will reduce the load time.
Available only in the case of a physical schema that has one or more objects
Settings → Change Load OrderSelect to configure the load order of physical schema tables by one or more groups and materialized views by one or more groupsAvailable only in the case of a physical schema that has one or more objects
Settings → Create Email NotificationSelect to schedule email notifications for load jobs of the physical schema (and/or other physical schemas)Available only in the case of a physical schema that has one or more objects
Settings → Version HistorySelect to manage and preview saved versions and draft versions of the physical schema For information about managing physical schema versions, refer to Tools → Schema Designer → Modes and Versions.
Settings → ShareSelect to view or manage the physical schema share options
Save ChangesSelect to promote the current draft version of the physical schema to a saved oneAvailable only when you edit a valid draft version
+ NewSelect to open the New menu. Available options are:
  ●  Schema Wizard
  ●  Table
  ●  Alias
  ●  Materialized View
  ●  Derived Table
  ●  Join
+ New → Schema WizardSelect to add a new physical schema table using the Schema Wizard
+ New → TableSelect to add a physical schema table using a specific data source
+ New → AliasSelect to create a new alias that references one of the existing objects in the same physical schema or another physical schema
+ New → Materialized ViewSelect to create a new materialized view using Apache SparkBefore the 5.2 release, you can access this option from + New → Derived Table → Materialized View
+ New → Derived TableSelect to add a new derived table, that is, one of the following:
  ●  Incorta Analyzer
  ●  Incorta SQL
Derived Table → Incorta AnalyzerSelect to use the Analyzer to create a new Incorta Analyzer table
Derived Table → Incorta SQLSelect to create an Incorta SQL table using the Incorta SQL engineAvailable as a labs feature that you must enable in the CMC → Default / Tenant Configurations → Incorta Labs
+ New → JoinSelect to access the Join Editor to create a new Left-Outer join relationship for an object in the physical schema where this object is the Child table.The join relationship can be with the object itself (self-join), with another object in the same physical schema, or with another object in another physical schema (cross-schema join)

Saving changes that require data load

When you save changes and promote a version from a draft to a saved version, these changes may require loading the data of related objects, whether full load or load from staging. For information about the updates or changes that require loading data, refer to Tools → Schema Designer → Load Jobs→ Changes that require data load.

When you select Save Changes, and you made updates that require full load or load from staging, a warning dialog appears showing the following options:

  • Cancel: to save the changes to the draft version.
  • Save: to save the changes to a saved version.
    • An orange triangle with an exclamation mark appears before the object name if the object data still exists in the engine memory.
    • A red triangle with an exclamation mark appears before the object name if the object data is cleared or evicted from the engine memory.
    • When you hover over the triangle icon, a tooltip indicates the required load type.
    • The message banner shows a message with the objects that you must load.
    • The icons and message will remain till you or another user performs the required load or a scheduled load job runs the required load for the related object(s).
Note

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

Warning

Incorta does not recommend running concurrent schema model update jobs and load jobs on the same schema or dependent schemas as this may result in errors or inaccurate data.

Summary section

The Summary section shows the following information for the physical schema:

PropertyDescriptionComments
Model Update StatusThe status of the last or current schema update job. The model update status can be the final status of a completed job or a phase in a current job as follows:
  ●  In Queue
  ●  Updating
  ●  Post Update
  ●  Commit
  ●  Success
  ●  Finished With Errors
  ●  Failed
  ●  Syncing
For newly added physical schemas (whether created or imported without the overwrite option enabled), the model update status is N/A.
Select the status link to access the Model Update Viewer and review the job details if any.
When you select it, you exit the Schema Designer.
When you select it while previewing a valid physical schema draft, you are prompted to do one of the following:
  ●  Keep the changes in the draft version
  ●  Save the changes to a published version
  ●  Discard the changes and delete the draft version
Last Load StatusThe status and time of the last load job for the whole physical schema or one of its objects if any. The status can be one of the following:
  ●  “N/A”: if there are no previous or current load jobs for the physical schema
  ●  Success
  ●  Finished With Errors
  ●  Failed
  ●  Interrupted
  ●  Aborted
During a load job, the status shows the current state of the load job process:
  ●  In Queue
  ●  Extraction
  ●  Enrichment
  ●  Load
  ●  Post-load
Select the link to access the Load Job Viewer and review the load job details.
When you select it, you exit the Schema Designer.
When you select it while previewing a valid physical schema draft, you are prompted to do one of the following:
  ●  Keep the changes in the draft version
  ●  Save the changes to a published version
  ●  Discard the changes and delete the draft version
Loading TimeThe time taken to complete the last load job. During a load job, the elapsed time is displayed.
TablesThe number of the physical schema objects
JoinsThe number of both child and parent join relationships related to the physical schema objects if any
RowsThe total number of rows available for Incorta of all the physical schema objectsNot available in the Schema Designer draft mode
Data SizeThe total size of the physical schema files on disk including the Direct Data Mapping files and Parquet filesNot available in the Schema Designer draft mode
Diagram View (icon)Select to access the Schema Diagram Viewer to view the join paths among objects in the physical schema itself or join paths to or from other physical schemas if any
Note: Aborted load jobs

Aborted is the status of a load job that is automatically forced to stop after exceeding the configured interruption timeout. Starting the 5.2 release, Incorta automatically aborts any load job that is interrupted in the extraction phase and exceeds the configured interruption time interval. For more information, refer to References → Data Ingestion and Loading → Aborted load jobs.

Message banner

The Message banner displays messages as appropriate and appears only in the following cases:

  • When you open a physical schema that another user has opened in the draft mode, the message confirms that you cannot update the physical schema because another user is editing it.
  • When you open a physical schema of which you already have a draft version, the message confirms that you can either do changes that overwrite the existing draft or can open the draft and edit it. Select the message to open the draft version.
  • When you preview an obsolete draft, the message confirms that you cannot edit this draft or promote it to a saved version. The banner also contains the Discard Draft option to delete the obsolete draft, whether it is your draft or another user’s draft.
  • When you make some updates that require full load or load from staging and you select to save these changes, the message shows the objects that you must load their data.
    • The message will be displayed till you or another user performs the required load or a scheduled load job runs the required load for the related objects.

Tables tab

If there are already objects created for the physical schema, the Tables tab displays all the objects in the physical schema. Physical schema objects can be physical schema tables, aliases, Incorta Analyzer tables, Incorta SQL tables, or Materialized Views. Use the options in the + New or the + option in the Tables tab to add new objects to the physical schema.

For information about the available details and the actions that you can perform using the Tables tab, refer to Tools → Schema Designer → Tables.

Joins tab

The Joins tab shows all join relationships related to the physical schema objects, whether they are self-join, schema join, or cross-schema join relationships. Use the Join option in the + New or the + (Add New Join) option in the Joins tab to add new join relationships between objects in the same physical schema or other physical schemas.

For information about how to manage joins, refer to Tools → Schema Designer → Joins.


Schema Designer actions for a physical schema

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

Add or edit the physical schema description

  • In the Schema Designer, select Settings (gear icon).
  • Select Edit Description.
  • In the Edit Description dialog, enter or update the physical schema description. You can enter up to 500 alphanumeric characters, including spaces and special characters.
  • Select Save.

Optimize the performance for physical schema objects

  • In the Schema Designer, select Settings (gear icon).
  • Select Performance Optimization.
  • In the Optimize Table Performance dialog, in the objects list, for each object you want to optimize the performance, select the check box, and for each object you do not want to optimize the performance, clear the check box.
  • Select Save.
Warning

You must enable performance optimization for Incorta Analyzer tables and Incorta SQL tables to avoid load failure.

Share the physical schema

  • In the Schema Designer, in the Action bar, select Settings (gear icon).
  • Select Share.
  • In the Share dialog, you can do the following:
    • remove an existing user or group
    • modify the existing access rights for a user or group
    • add a new user or group with the required access rights

Explore physical schema data

  • In the Schema Designer, in the Action bar, select Explore Data.
  • Use the Analyzer to create insights referencing columns of the physical schema objects.

Review the status of the schema update jobs

  • In the Schema Designer, in the Summary section, select the Model Update Status link.
  • In the Model Update Viewer, review the schema update job status.