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.
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 Rights | Own/Edit | Share | View |
|---|---|---|---|
| Add or edit the physical schema description | ✔ | View only | View only |
| Search for a physical schema object | ✔ | ✔ | ✔ |
| Search for a column in physical schema objects | ✔ | ✔ | ✔ |
| Optimize the performance for physical schema objects | ✔ | View only | View only |
| Change the load order for the physical schema objects | ✔ | View only | View only |
| Share the physical schema | ✔ | ✔ | View 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.
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.
| Option | Description | Comments |
|---|---|---|
| Name | The physical schema name | |
| Description | The physical schema description, if any | |
| Load | Select 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 Load | Instructs the Loader Service to start a full load job of the physical schema | For 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 Load | Instructs 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 Load | Instructs 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 Job | Select to create a scheduled job for loading the physical schema data | |
| Stop Load | Select 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 |
| Explore | Select 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 Description | Select to add or edit the physical schema description | |
| Settings → Performance Optimization | Select 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 Order | Select to configure the load order of physical schema tables by one or more groups and materialized views by one or more groups | Available only in the case of a physical schema that has one or more objects |
| Settings → Create Email Notification | Select 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 History | Select 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 → Share | Select to view or manage the physical schema share options | |
| Save Changes | Select to promote the current draft version of the physical schema to a saved one | Available only when you edit a valid draft version |
| + New | Select to open the New menu. Available options are: ● Schema Wizard ● Table ● Alias ● Materialized View ● Derived Table ● Join | |
| + New → Schema Wizard | Select to add a new physical schema table using the Schema Wizard | |
| + New → Table | Select to add a physical schema table using a specific data source | |
| + New → Alias | Select to create a new alias that references one of the existing objects in the same physical schema or another physical schema | |
| + New → Materialized View | Select to create a new materialized view using Apache Spark | Before the 5.2 release, you can access this option from + New → Derived Table → Materialized View |
| + New → Derived Table | Select to add a new derived table, that is, one of the following: ● Incorta Analyzer ● Incorta SQL | |
| Derived Table → Incorta Analyzer | Select to use the Analyzer to create a new Incorta Analyzer table | |
| Derived Table → Incorta SQL | Select to create an Incorta SQL table using the Incorta SQL engine | Available as a labs feature that you must enable in the CMC → Default / Tenant Configurations → Incorta Labs |
| + New → Join | Select 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).
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.
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:
| Property | Description | Comments |
|---|---|---|
| Model Update Status | The 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 Status | The 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 Time | The time taken to complete the last load job. During a load job, the elapsed time is displayed. | |
| Tables | The number of the physical schema objects | |
| Joins | The number of both child and parent join relationships related to the physical schema objects if any | |
| Rows | The total number of rows available for Incorta of all the physical schema objects | Not available in the Schema Designer draft mode |
| Data Size | The total size of the physical schema files on disk including the Direct Data Mapping files and Parquet files | Not 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 |
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
- Optimize the performance for physical schema objects
- Share the physical schema
- Explore physical schema data
- Review the status of the schema update jobs
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.
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.