Tools → Schema Designer

About the Schema Designer

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

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 View access rights to. A SuperUser that is a Tenant Administrator can also access the Schema Designer for a given physical schema. However, a user must own or have Edit access rights to the physical schema to use the Schema Designer to manage its entity objects, joins, and load jobs. Some functions require additional roles, such as the Explore Data function that 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 name of the physical schema.

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 for physical schema data
Perform an incremental load for physical schema data
Load physical schema data from staging
Schedule a physical schema load job
Perform a full load for 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 entity 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 with the 5.1.2 release, the Schema Designer supports two different modes:

  • Saved Version mode
  • Draft mode

Saved Version mode

This is the default mode when you access the Schema Designer. A saved version is created for a physical schema after a schema update job. You trigger a schema update job when you select Save Changes to promote a draft version or when you import a physical schema that overwrites an existing one.

Important

Creating a new physical schema or importing one does not lead to creating a draft version of the physical schema. This creates a saved version directly; however, the version will not appear in the Version History unless the physical schema overwrites an existing one.

With the proper access rights and permissions, you can do the following in this mode:

  • Start a new draft version or edit the existing draft to add, edit, and delete the physical schema objects and joins
  • Access the Load Job Viewer and Model Update Viewer to view data load jobs and schema update jobs respectively
  • Load data
  • Explore the physical schema data
  • Access the physical schema Settings menu (gear icon) and use available options including managing the physical schema draft and saved versions

If you have a draft version of the physical schema, when you access the Schema Designer, a banner message appears denoting that you can either overwrite the draft or open it to accumulate new updates to it.

Important

If another user is opening a physical schema in the draft mode and is previewing a valid draft of it (not an obsolete draft), you cannot make any updates to this physical schema, delete it, or overwrite it with another imported one. However, you can load its data, load one of its objects’ data, explore data, view the physical schema diagram, view object details, and view join details.

You may need to refresh the Schema Designer page to check if the physical schema is available to update or not.

Draft mode

You can access the draft mode for a physical schema when you do one of the following:

  • After updating the physical schema, such as creating a new object or deleting one
  • After saving updates in the Table Editor (after you select Done)
  • After saving updates in the Join Editor (after you select Done)
  • When you preview or edit a draft (Settings (gear icon) → Version HistoryDraft Versions tab → More Options (⋮ vertical ellipsis) → Preview)
  • When you select to edit your existing draft (select the link in the message banner)

With the first update you make, Incorta creates a draft version of the physical schema and accumulates to this draft any further updates you make. For example, when you create a formula column in the Table Editor and then select Done in the Action bar, this creates a new draft or updates an existing draft, whether this existing draft belongs to you or another user and you are editing it.

In addition, if the session times out while you have physical schema updates that you did not save, the system will keep these updates in the draft version for you.

Note

When you edit a physical schema in the draft mode, you block other users from updating the physical schema until any of the following happens:

  • You promote the draft changes to a saved version.
  • You keep the changes in the draft version.
  • You sign out from Incorta.
  • You close the browser (after 5 minutes from closing the browser).
  • The session ends.
  • The Analytics Service is down.

When you preview or edit a draft version, and then you try to exit the Schema Designer by selecting any tab on the navigation bar, the load job status, or the model update status, you have the following options:

  • Keep the changes in the draft version
  • Save the changes to a published version (that is, promote the draft changes to a saved version)
  • Discard the changes and delete the draft version
Important

When you promote a draft to a saved version, you invoke a schema update job that you can preview its status in the Model Update Viewer. During a schema update job, you and other users cannot do any of the following:

  • Run another update job, so when you select to promote a draft version to a saved one (select Save Changes), you get a message that another update job is running
  • Import a physical schema that overwrites this physical schema
  • Run a load job

Each user has only one draft version per physical schema. If you have a draft version for a given physical schema and you access the Schema Designer for this physical schema in the saved version mode, the Schema Designer shows a message to confirm that you already have a draft and if you want to make changes to this physical schema, you can either overwrite the existing draft with the new changes you will make or open the draft version and accumulate your changes to this draft.

In the draft mode, you cannot load or explore data. In addition, the Rows and Data Size properties for the physical schema and its objects are dimmed as they may show incorrect information.

When you create a saved version of a physical schema, you obsolete all existing drafts, including your draft and other users’ drafts. When you preview an obsolete draft, you cannot edit it or promote it to a saved version; however, you can discard this draft and start a new one from the saved version. When you discard an obsolete draft version, the Schema Designer opens the saved version mode.

As a schema manager user, you can preview a draft of a physical schema that you own or have Edit access rights to. You can also promote a draft to a saved version as long as this draft is not obsolete.

Important

When you create an MV in a draft version, MV validation is run against the draft version, not the saved version of the physical schema.

Schema Designer anatomy

The Schema Designer consists of the following:

  • Action bar
  • Summary/Header section
  • Message banner
  • Tables section
  • Joins section

Action bar

The Action bar contains the following options:

OptionDescriptionComments
SearchEnter a search term to search for a physical schema entity object or a column in the physical schema, if any. Select an object or column from the search result list to open the respective object in the Table Editor.
LoadSelect to open the Load menu. Available options are:
    •  Load now
    •  Schedule
Available only when you open a physical schema that has entity object(s) in the saved version mode
Load → Load nowSelect to load data for the physical schema objects. Available load strategies are:
    •  Full
    •  Incremental
    •  Staging
Load now → FullInstructs the Loader Service to:
    •  flush out all Direct Data Mapping files and Parquet files that exist in Shared Storage for the physical schema objects
    •  extract the data from data source(s)
    •  create Parquet files and Direct Data Mapping (DDM) files in Shared Storage
For objects that you enable for performance optimization, the Analytics Service will also load the snapshot (DDM) files from Shared Storage (Staging) into its in-memory Direct Data Mapping engine.
Although it is a Full Load, the Loader Service will run the load in incremental mode for entity objects with incremental load enabled and full load disabled.
Load now → IncrementalInstructs the Loader Service to run the load in incremental mode for only objects with incremental load enabled. The Loader Service will not extract data for other objects.Depending upon the data source properties, the Loader Service will use an Update Query SQL statement or an Update File to determine which data to extract from the data source.
Load now → StagingInstructs 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 snapshot (DDM) files from Shared Storage (Staging).
Load → ScheduleSelect to create a scheduled job for loading the physical schema data
StopSelect to stop or interrupt the load activities of a running load job. The status of the load job becomes Interrupted.Available only during a running load job
Explore DataSelect to access the Analyzer to explore the physical schema data.Available only when you open a physical schema that has entity object(s) in the saved version mode. It also requires a role that can manage the Catalog, that is, the Content Manager.
DiagramSelect 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
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 snapshot (DDM) files from Shared Storage (Staging) into its in-memory Direct Data Mapping engine.
For unselected objects, the Loader Service will load data only into Shared Storage, which can slow the speed of creating insights; however, this will reduce the load time.
Available only in the case of a physical schema that has entity object(s)
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 entity object(s)
Settings → Create NotificationSelect to schedule email notifications for the load jobs of the physical schema (and/or other physical schemas)Available only in the case of a physical schema that has entity object(s)
Settings → Version HistorySelect to manage and preview saved versions and draft versions of the physical schema
Settings → ShareSelect to view or manage the physical schema share options
+ NewSelect to open the Add New menu. Available options are:
    •  Schema Wizard
    •  Table
    •  Alias
    •  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 or connector
+ New → AliasSelect to create a new alias that references one of the existing objects in the same physical schema or another physical schema
+ New → Derived TableSelect to add a new derived table, that is, one of the following:
    •  Incorta Analyzer table
    •  Incorta SQL table
    •  Materialized View
The term “Derived Table” was introduced starting with release 5.0.
Derived Table → Incorta AnalyzerSelect to use the Analyzer to create a new Incorta Analyzer tablePrior to release 5.0, you can access this option from + NewTableIncorta
Derived Table → Incorta SQLSelect to create an Incorta SQL table using Incorta SQL engineAvailable as a lab feature as of release 5.0
Derived Table → Materialized ViewSelect to create a new materialized view using Apache SparkPrior to release 5.0, you can access this option from + NewMaterialized View
+ 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)
Save ChangesSelect to promote the current draft version of the physical schema to a saved oneAvailable only when you edit a valid draft version
Note

The Cluster Management Console (CMC) tenant or default tenant configurations has settings to define the maximum number of versions to maintain and how often a separate saved version is created when you select to promote a draft to a saved version (select Save Changes in the Action bar). These settings are Tenant Configurations / Default Tenant Configurations → Incorta Labs → Maximum number of versions per entity and Backup frequency.

If you set the Backup frequency option to a specific time period, you will append new changes to the current unnamed version each time you select Save Changes during this period. If you set it to Keep all versions, you will create a separate saved version each time you select Save Changes. This applies only to unnamed versions. Once you name a saved version, you cannot save changes to it. In this case, a new version is created.

Saving changes that require data load

Some updates you make to the physical schema objects require loading data, either load from source (full load) or load from staging, to ensure data consistency.

The following are the updates that require load from staging:

  • Adding or updating the load filter
  • Enabling performance optimization for non-optimized objects

The following are the updates that require full load:

  • Adding a new physical schema table or materialized view
  • Changing the data type of a physical schema table column or MV column
  • Changing the source of a materialized view or physical schema table, whether by selecting another source file in the Data Source properties dialog or editing the query
  • Adding or changing a key column (changing the column function from key to dimension or measure and vice versa) in a physical schema table or MV
  • Adding a new physical schema table column
  • Adding a new MV column
  • Changing the object type, for example, changing a physical schema table to an Incorta Analyzer table or MV
  • Removing a physical schema table column or a materialized view column that functions as a key

When you select Save Changes, and you have made updates that require full load or load from staging, a warning dialog appears denoting that you have to perform data load. The dialog lists the objects and the required load type for each.

  • Select Cancel to save the changes to the draft version.
  • Select Continue to save the changes to a saved version.
    • The object icon changes to an orange triangle with an exclamation mark if the object data still exists in the engine memory.
    • The object icon changes to a red triangle with an exclamation mark 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.

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 to open the draft and edit it. The message has a link 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.

Summary/Header section

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

PropertyDescriptionComments
IconRepresents the physical schema
NameThe physical schema name
DescriptionThe physical schema description, if any
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 of the last load job for the whole physical schema or for one of its entity objects, if any.
This can be one of the following:
    •  “Please load data” in the case that there are no previous or current load jobs for the physical schema
    •  The timestamp for a successfully completed load job
    •  Finished With Errors
    •  Failed
    •  Interrupted
During a load job, this property 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 or parent join relationships related to the physical schema objects, if any
RowsThe total number of loaded rows available for Incorta for all the physical schema objects, that is, the physical schema rows in the Analytics Service in-memory Direct Data Mapping engineNot 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

Tables section

In the case that there are no entity objects created for the physical schema, the Start adding tables to your schema: section is available with the options that you can access from the + New menu in the Action bar. Use the available options to add the first entity object(s) to your physical schema.

In the case that there are already entity objects created for the physical schema, the Tables section displays all the entity objects in the physical schema. Physical schema objects can be physical schema tables, aliases, Incorta Analyzer tables, Incorta SQL tables, or Materialized Views.

The section header includes the following:

  • The number of the physical schema objects
  • Sort type: descending (an arrow pointing downwards) or ascending (an arrow pointing upwards). The default option is descending.
  • Sort property: the property to use for sorting the tables list. The following is the available options:
    • Name: alphabetical order
    • Columns: the number of columns
    • Joins: the number of joins
    • Rows: the number of rows
    • Data Size: default option

For each physical schema object, the Tables section shows the following:

  • Action menu (v arrow): contains the following options:
    • Table Details: select to access the Table Editor to view or manage the properties of the physical schema object
    • Load Table: select to perform a full load for the object data. Not available in the case of an alias.
    • Load from Staging: select to perform a load for the object data from Shared Storage. Not available in the case of an alias
    • Remove Table: select to delete the physical schema object
  • The physical schema object properties
Note

The Load Table and Load from Staging options are available only when you open a physical schema in the saved version mode. They are not available in the draft mode. They do not apply to aliases.

The following table shows the properties of the physical schema object that the Tables section shows:

PropertyDescription
IconRepresents either the type of the physical schema entity object or the type of the dataset in the case of a physical schema table.
“Multiple” indicates that there are multiple data sources for the physical schema table.
If the object has changes that require loading data, the icon will be a triangle with an exclamation mark till the required load occurs, whether manually or through a scheduled job.
TableThe object name
Data Source/Source/LanguageOne of the following:
    •  The data source of a physical schema table
    •  The fully qualified name of the source object for an alias
    •  The code language for a materialized view
    •  The type of the entity 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 object performance optimization:
    •  Optimized
    •  Not optimized
Not available in the case of an alias.
ColumnsThe number of columns in the physical schema object including both data-backed columns and formula columns
JoinsThe number of both child or parent join relationships related to the physical schema object
RowsThe number of loaded rows for the physical schema object.
Not available in the draft mode.
Data SizeThe size of the object files on disk including the Direct Data Mapping (ddm) files and Parquet files.
Not available in the draft mode.
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..

Joins section

The Joins section shows all join relationships related to the physical schema entity objects, whether they are self join, schema join, or cross-schema join relationships.

Warning

Incorta Analyzer tables and Incorta SQL tables do not support self joins. When you try to load a table of these with a self join, it will throw errors.

The section header includes the following:

  • The number of join relationships
  • Sort type: descending (an arrow pointing downwards) or ascending (an arrow pointing upwards). The default option is descending.
  • Sort property: the table to use for sorting the joins list: Child Table or Parent Table. The default is to sort by the Child Table name.

For each join relationship, the Joins section shows the following:

  • Action menu (v arrow): this menu is available when the Child table in the join is in the current physical schema. The Action menu contains the following options:
    • Join Details: use to access the Join Editor to view or manage the join conditions and filters
    • Remove Join: use to remove the join relationship
  • Join conditions: each condition consists of three parts:
    • Child (the left side of the join): the physical schema, object, and the join column
    • Join operator
    • Parent (the right side of the join): the physical schema, object, and the join column
Note

Join filters do not appear in the Schema Designer.


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 Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • 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.

Search for a physical schema object

  • 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, in the Search box, enter a search term.
  • In the result list, under Table Name, select the object that you want to open in the Table Editor.

Search for a column in physical schema objects

  • 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, in the Search box, enter a search term.
  • In the result list, under Column Name, select the column to open the relative object in the Table Editor.

Optimize the performance for physical schema objects

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • 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.

Change the load order for physical schema objects

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, select Settings (gear icon).
  • Select Change Load Order.
  • In the Choose Load Order dialog, you can do the following to manage the table groups before changing their load order:
    • For each table (or materialized view) that you want to add to a new load group, select the check box, and then select Add to New Group.
    • For each table (or materialized view) that you want to add to an existing load group, select the check box, and then select Add to Existing Group.
    • To reset table grouping and order, select Revert to Default.
  • To set the order of a table group, select the up arrow (to the right of the group name) to move the group upwards, or select the down arrow (to the right of the group name) to move the group downwards. Repeat this action until you order the table groups as required.
  • Select Save.

Share the physical schema

  • 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 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 Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Action bar, select Explore Data.
  • Use the Analyzer to create insights referencing columns of the physical schema objects.

Preview the physical schema join diagram

  • 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 Diagram.
  • In the Schema Diagram Viewer, review the physical schema join relationships.

Sort physical schema objects list view

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Tables section, select the arrow to point downwards to sort descending or to point upwards to sort ascending.
  • Select the property to sort by. The following is the available options:
    • Name (alphabetical order)
    • Columns (the number of columns)
    • Joins (the number of joins)
    • Rows (the number of rows)
    • Data Size (default)

Review the status of the schema update jobs

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • 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.

Schema Designer actions for a physical schema object

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

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.

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 entity object

Important: Validation Rules for a physical schema entity object name

An entity 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
  • besides 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 Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Action bar, select + NewTable → the data source type.
  • In the Table Editor, in the Data Source dialog, specify the properties of the new physical schema table.
  • Select Add.
  • In the Table Editor, for Table Name, 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 Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Action bar, select + NewAlias.
  • In the Table Editor, for Table Name, enter the name of the new alias.
  • In the Table Editor, in the Data Source(s) section, for Schema Name, select the physical schema of the original entity object that this alias references. This can be the current physical schema or another physical schema. The default is the current physical schema.
  • For Select Table, select the original entity object that this alias references.
  • In the Action bar, select Done.

Create an Incorta Analyzer table

  • 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 + NewDerived TableIncorta Analyzer Table.
  • In the Table Editor, in the 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 Data Source dialog, review the sample data, and then select Add.
  • In the Table Editor, for Table Name, 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 Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Action bar, select + NewDerived TableIncorta SQL Table.
  • In the Table Editor, in the Data Source dialog, in the Query section, select the pen icon.
  • In the Query Editor, enter the SQL code for the new table, and then select Done.
  • In the Data Source dialog, select Add.
  • In the Table Editor, for Table Name, 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 Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Action bar, select + NewDerived TableMaterialized View.
  • In the Table Editor, in the Data Source dialog, specify the properties of the new object.
  • Select Add.
  • In the Table Editor, for Table Name, 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.

View physical schema object details

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Tables section, for a given physical schema object, do one of the following:
    • Select the physical schema object.
    • In the Action menu (v arrow), 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 Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Tables section, for a given physical schema object, do one of the following:
    • Select the physical schema object.
    • In the Action menu (v arrow), 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 Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Tables section, for a given physical schema object, in the Action menu (v arrow), select Remove Table.
  • In the dialog, clear or keep selected the Remove all joins related to this table checkbox, as appropriate, and then select Remove.

Schema Designer actions for a join relationship

Using the Schema Designer, you can perform the following actions for join relationships:

Note

You can edit a join relationship, view its properties, or remove it only when you use the Schema Designer for the physical schema of the Child table in the join.

Important

When you add, edit, or delete a join relationship, these updates are saved to the draft till you save the changes to a saved version.

Create a join relationship

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, do one of the following:
    • If there are no existing joins for the physical schema, in the Joins section, select Create new join.
    • In the Action bar, Select + NewJoin.
  • In the Join Editor, for the new join relationship, specify the join properties for the Child and Parent sides.
  • In the Join Editor, in the Action bar, select Done.

Sort joins list view

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Joins section, select the arrow to point downwards to sort descending or to point upwards to sort ascending.
  • Select whether to sort by the alphabetical order of the Child Table name, which is the default option, or the Parent Table name

View join relationship properties

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Joins section, for a given join relationship, do one of the following:
    • Select the join relationship.
    • In the Action menu (v arrow), select Join Details.
  • In the Join Editor, review the join conditions and filters, if any.
  • To exit the Join Editor without saving updates, if any, in the Action bar, select Cancel.

Edit a join relationship

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Joins section, for a given join relationship, do one of the following:
    • Select the join relationship.
    • In the Action menu (v arrow), select Join Details.
  • In the Join Editor, make the necessary updates.
  • To save updates, in the Join Editor, in the Action bar, select Done.

Remove a join relationship

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, for a given join relationship in the Joins section, in the Action menu (v arrow), select Remove Join.
  • In the dialog, select Remove.

Schema Designer actions for a draft

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

Create a physical schema draft

By default, when you make changes to the physical schema using the Table Editor or the Join Editor, you create or edit a draft version of the physical schema. In addition, when you add or delete a physical schema object or join relationship in the Schema Designer, you create or edit a draft.

Note

When you load data or edit the physical schema settings, such as the description or the load order, this does not create or edit a physical schema draft.

Here are the steps to create a physical schema draft:

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, make your changes to the physical schema.

When you try to exit the Schema Designer and when prompted to save your changes, select Keep As Draft.

Search or filter the draft version history

  • 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 Settings (gear icon), and then select Version History.
  • In the Version history drawer, select specify the filter criteria as required:
    • For Version Date, select a period to search for drafts created or updated within this period. You can select a predefined period, such as Yesterday and Last 7 days (including today), or select custom, and then specify the period’s start and end dates.
    • For Modified By, the name of the user who last updated the drafts you search for.
    • Enable the Only Show Named Versions toggle to show only named versions.
  • Select the Draft Versions tab to view drafts that match the filter options you specified.

Name a draft version

  • In the Schema Designer, in the Action bar, select Settings (gear icon), and then select Version History.
  • In the Version history drawer, optionally, search for the draft you want.
  • For a given draft, select the draft name (by default, the draft creation or update time is the draft name).
  • Specify a new name for the draft version, and then press ENTER or select any other place in the drawer or the Schema Designer.
Note

A draft name can have alphanumeric or special characters. The length of a draft name cannot exceed 50 characters including spaces.

Preview a draft

You can preview drafts created by other users.

  • In the Schema Designer, in the Action bar, select Settings (gear icon), and then select Version History.
  • In the Version history drawer, optionally, search for the draft you want.
  • For a given draft, select More Options (⋮ vertical ellipsis), and then select Preview.
Note

When you access the Schema Designer of a physical schema for which you have a draft, the message banner shows a message with a link to open this draft. Select the link to preview, and edit, the draft.

Promote a valid draft to a saved version

You can promote only a valid, non-obsolete draft after creating or editing it or while previewing it. Promoting a draft removes it from the draft versions list. When you promote a named draft, the saved version will have the name of this draft.

Here are the steps to promote a daft after creating it:

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • Use the Schema Designer, Table Editor, or Join Editor to make your updates.
  • In the Schema Designer, in the Action bar, select Save Changes.

Here are the steps to promote a valid daft while previewing it:

  • In the Schema Designer, in the Action bar, select Settings (gear icon) → Version History.
  • For a given draft, select More Options (⋮ vertical ellipsis) → Preview.
  • In the Action bar, select Save Changes.
Note

You can also preview a draft that you have of a physical schema using the link in the message banner.

The Save Changes option is available if the draft is still valid; otherwise, the message banner shows that the draft is obsolete, and you cannot edit or promote this draft.

Here are the steps to promote a valid daft after editing it:

  • In the Schema Designer, in the Action bar, select Settings (gear icon) → Version History.
  • For a given draft, select More Options (⋮ vertical ellipsis) → Preview.
  • Use the Schema Designer, Table Editor, or Join Editor to make the updates you want to append to the draft.
  • In the Action bar, select Save Changes.

Discard changes in a valid draft

  • After creating or editing a draft or while previewing a valid one, try to exit the Schema Designer by selecting any tab in the navigation bar or selecting the Load Status or Model Update Status link.
  • In the dialog, select Discard Changes.

Discard an obsolete or invalid draft

When you preview an obsolete draft, the message banner shows that you cannot edit or save this draft. To discard the changes in this obsolete draft and delete it, in the message banner, select Discard Draft. The draft is deleted from the draft versions list.


Schema Designer actions for a saved version

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

Search or filter the saved version history

  • 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 Settings (gear icon), and then select Version History.
  • In the Version history drawer, select specify the filter criteria as required:
    • For Version Date, select a period to search for saved versions created or restored within this period. You can select a predefined period, such as Yesterday and Last 7 days (including today), or select custom, and then specify the period’s start and end dates.
    • For Modified By, the name of the user who created the version you search for.
    • Enable the Only Show Named Versions toggle to show only named versions.
  • Select the Saved Versions tab to view saved versions that match the filter options you specified.

Name a saved version

  • In the Schema Designer, in the Action bar, select Settings (gear icon), and then select Version History.
  • In the Version history drawer, optionally, search for the saved version you want.
  • For a given saved version, select the version name (by default, the version creation or restore time is the version name).
  • Specify a new name for the saved version, and then press ENTER or select any other place in the drawer or the Schema Designer.
Note

A version name can have alphanumeric or special characters. The length of a version name cannot exceed 50 characters including spaces.

Export a saved version

  • In the Schema Designer, in the Action bar, select Settings (gear icon), and then select Version History.
  • In the Version history drawer, optionally, search for the saved version you want.
  • For a given saved version, select More Options (⋮ vertical ellipsis) → Export.
  • In the Export Schema(s) dialog, optionally select the Include scheduled jobs check box to include the scheduled jobs related to this physical schema.
  • Select Export. The exported physical schema .zip file is downloaded to the Downloads folder.

Restore a saved version

When you restore a saved version of a physical schema, a copy of this version becomes the current version. The default name of the copied version is its restore (creation) time. The original saved version remains as is with its name and creation date.

Here are the steps to restore a saved version of a physical schema:

  • In the Schema Designer, in the Action bar, select Settings (gear icon), and then select Version History.
  • In the Version history drawer, optionally, search for the saved version you want.
  • For a given saved version, select More Options (⋮ vertical ellipsis) → Restore.

Schema Designer actions for load jobs

With the Schema Designer, you can perform the following actions for data load jobs

Perform a full load for physical schema data

  • 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 LoadLoad NowFull.
  • In the dialog, select Load.

Perform an incremental load for physical schema data

  • 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 LoadLoad NowIncremental.
  • In the dialog, select Load.

Load physical schema data from staging

  • 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 LoadLoad NowStaging.
  • In the dialog, select Load.

Schedule a physical schema load job

  • 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 LoadSchedule.
  • In the Schedule Schema Load dialog, specify the scheduled load job properties.
  • In the Start On section, specify when the scheduled load job starts: select the date, time, and time zone.
  • In the Repeat section, specify the frequency of running the scheduled load job.
    • Select Daily, and then select how often this job runs: every number of minutes, hours, or days.
    • Select Weekly, and then select the week days to run the load job.
    • Select Monthly, and then select one of the following:
      • the day number every number of months (for example, the tenth day every 2 months)
      • a specific week day every number of months (for example, the second Monday every month)
    • Select the No Recurrence check box to run this job only once.
  • In the Repeat section, for repeated jobs, specify the end of the validity period of the scheduled load job:
    • Select No End to run it forever. This Is the default option.
    • Select End By, and then select a date to end the scheduled load job.
  • For Job Name, enter the name of the scheduled load job.
  • For Description, optionally, enter a description of the scheduled load job.
  • For Load Type, select the load type. The available options are:
    • Full
    • Incremental (default)
    • Staging
  • Select Schedule.
Note

The scheduled load jobs appear in the Scheduler, under Schema Loads. A user with the appropriate access rights to the Scheduler can manage these jobs, in addition to creating new jobs.

Perform a full load for physical schema object data

This option is not available for an alias.

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Tables section, for a given object, in the Action menu (v arrow), select Load Table.
  • In the dialog, select Load.

Load physical schema object data from staging

This option is not available for an alias.

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Tables section, for a given object, in the Action menu (v arrow), select Load from Staging.
  • In the dialog, select Load.

Review the status of a load job

  • In the Navigation bar, select Schema.
  • In the list view of schemas, select a given physical schema.
  • In the Schema Designer, in the Summary section, select the Last Load Status link.
  • In the Load Job Viewer, review the load job status.

Create a physical schema load email notification

Important

Notifications require a tenant email configuration for an outgoing email server using SMTP or EWS in the Cluster Management Console (CMC).

The notifications that you create appear on the Scheduler, under Schema Notifications. A user with the appropriate access rights to the Scheduler can manage these notifications, in addition to creating new notifications.

Here are the steps to create an email notification for a physical schema:

  • 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 Settings (gear icon).
  • Select Create Notification.
  • In the Create Email Notification dialog, enter the details of the email notification. For more information, see [Email notification details].
  • Select Done.

Email notification details

The following table shows the details you need to specify for a load email notification:

PropertyControlDescription
Notification Nametext boxEnter the name of the new email notification. The length of the notification name must be between 1 and 255 alphanumeric characters, including spaces and special characters.
Notify On → SuccesscheckboxSelect the checkbox to send the notification email when the load successfully completes
Notify On → FailurecheckboxSelect the checkbox to send the notification email when the load fails
Select Schema(s) → Searchtext boxEnter a search term to search for specific physical schemas to create load notification for.
Select Schema(s) → Select AllcheckboxSelect the checkbox to select all physical schemas you have access to or all physical schema(s) in the search result list
Select Schema(s) → Schemas listlist/checkboxSelect the checkbox for each physical schema you want to include in the load notification. Only physical schemas that you own or have access rights to appear on the list.
Recipients → Search Names, Emails, and Groupstext box
    •  Enter a search term (for example, an email address, user name, group name) to search for users or groups in the tenant.
    •  Enter the email address to select an external recipient.
Recipients → ListlistFor each recipient, select V (down arrow) to the right of the recipient name or email, and then select where the given recipient should go to: the To list, Cc list, or Bcc list.
Bodytext boxOptionally, enter the email body text. The body text you enter appears in the notification email before an auto-generated text. You can enter up to 4000 alphanumeric characters, including spaces and special characters.
Note

When you create an email notification that contains multiple physical schemas, the recipient(s) receives a separate email for each physical schema. Recipients on the Bcc list do not appear on the received email, while those on the To and Cc lists do appear.

The notification email contains the following:

  • the email body text you entered, if any
  • the notification name
  • the display name of the notification user creator
  • the schema name
  • the schema load status
  • the load date and time
  • the load duration
  • a link to the Load Job Viewer that contains the schema load job summary and details