Tools → Schema Wizard
About the Schema Wizard
The Schema Wizard allows a tenant user with the necessary access permissions to quickly and simply build a physical schema.
The Schema Wizard uses an existing data source to define one or more physical schema tables. Depending on the data source type, the Schema Wizard also detects foreign key-to-primary key table relationships in the data source, and in turn, defines these as child-to-parent join relationships between physical schema tables within the physical schema.
Schema Wizard Access Permissions
A user that belongs to a group with the Schema Manager or the SuperRole role can access the Schema Wizard. To access the Schema Wizard for a given tenant, in the Navigation bar, select Schema. In the Action bar, select +New→Schema Wizard.
Schema Wizard Anatomy
- Action Bar
- Data Source Canvas
- Manage Tables Canvas
- Finalize Canvas
- Footer Bar
Action Bar
The Action Bar is located at the top of the Schema Wizard for each step. Close the Schema Wizard by selecting the X in the Action Bar.
Data Source Canvas
The Data Source Canvas appears in the first step of the Schema Wizard. Enter schema and data source properties in the Data Source Canvas.
Manage Tables Canvas
The Manage Tables Canvas appears in the second step of the Schema Wizard. The Manage Tables Canvas consists of the Edit Panel and the Schema Wizard Table Editor.
Use the Edit Panel to:
- Search by any part of a table name in the search text box to filter a list of tables to those matching the search string.
- Select the tables to include in the schema.
- Switch between a view of All Tables or Selected tables, respectively. The number of selected tables will appear in parenthesis to the right of Selected.
- For Database and Kafka data sources, add a Custom SQL table.
For the table selected in the Edit Panel, use the Schema Wizard Table Editor to:
- Search by any part of a column name in the search text box to filter a list of columns to those matching the search string.
- Select the table columns to include in the schema.
- Edit the table column properties.
- View the number of columns in the table and the number selected in the upper right corner.
- Delete the table from the schema by selecting the delete (trash icon) button.
- For Database and Kafka data sources, customize the SQL for a table.
Finalize Canvas
The Finalize Canvas appears in the third step of the Schema Wizard. The Finalize Canvas consists of a checkbox to automate the creation of table joins by Incorta.
Footer Bar
The Footer Bar is located at the bottom of the Schema Wizard for each step. Use the Footer Bar to navigate from one step to the next, or to navigate back to a previous step.
Create a Schema with the Schema Wizard
- In the Navigation bar, select the Schema tab.
- In the Action bar, select +New→Schema Wizard.
There are 3 steps for creating a schema using the Schema Wizard:
- Step 1: Choose a Data Source
- Step 2: Manage Tables
- Step 3: Finalize
Step 1: Choose a Data Source
Before creating a schema using the Schema Wizard, review how to create an external data source with the Data Manager.
Validation rules for a physical schema name
A physical schema name…
- Must be unique.
- Must be between 1 and 250 characters in length.
- Must begin with an alpha character, lower or upper case.
- After the first alpha character, can contain zero or more alphanumeric characters in lower, upper, or mixed case.
- After the first alpha character, can contain zero or more underscore
(_)or Dollar sign($)characters. - Besides underscore
(_)and Dollar sign($), cannot contain special characters, symbols, or spaces. - Is not case-sensitive. (Starting with 2024.1.0)
- In the Schema Wizard, in (1) Choose a Source, specify the schema and data source properties.
- In the Schema Wizard footer, select Next.
Choose Source Properties
| Property | Control | Description |
|---|---|---|
| Enter a name | text box | Enter the schema name. |
| Select a Datasource | drop down list | Select an existing data source. |
| Description | text box | Optionally create a description of the schema. |
Step 2: Manage Tables
- In the Edit Panel, first select the name of the Data Source.
- To add tables to the schema, check the Select All checkbox or select individual tables.
- For Database and Kafka data sources, add a Custom SQL Table using a built-in SQL Editor by selecting the + Custom SQL Table button at the bottom of the Edit Panel.
- Select a table in the Edit Panel to use the Schema Wizard Table Editor to select and modify individual columns for the highlighted table.
- In the Schema Wizard Table Editor, edit the column properties for each table, as necessary.
- For Database and Kafka data sources, edit the table SQL query by selecting the Customize SQL button in the upper right corner of the Schema Wizard Table Editor.
- In the Schema Wizard footer, select Next.
The physical schema table automatically inherits the name of the data source table, file name, or worksheet you select in the Schema Wizard. You can change or specify the name in the Schema Wizard only when customizing the SQL query or creating a custom SQL table using a query-based data source.
Schema Wizard Table Editor Column Properties
| Property | Control | Description |
|---|---|---|
| Column Name | checkbox | View the name of a table column as it appears in the data source, and select or deselect it for inclusion or exclusion in the schema, respectively. |
| Source Type | text box | View the column data type in the data source. |
| Incorta Label | text box | Optionally edit a label for the column that you would like to show on dashboards by default, as part of the semantic layer. |
| Incorta Type | drop down list | Select the data type of the physical schema table column. |
| Incorta Function | drop down list | Select the function of the physical schema table column. |
Incorta column data types
- date
- double
- integer
- long
- string
- text
- timestamp
- null
Incorta column functions
- Key: Enforces a unique constraint and creates an internal index.
- Dimension: Describes a measure.
- Measure: Used in aggregations and calculations.
Add a Custom SQL Table
The Add Table dialog box displays when the + Custom SQL Table button is selected. Perform the following actions within Add Table:
- Specify a name for the new table in the Table Name field.
- Enter the SQL for the new table starting at line 1.
- Select the Format button to format the SQL statements.
- Select the Execute button to test the creation of the new table.
- View the output of the SQL test execution within Output V.
- Select the Save button to save the table and exit the Add Table dialog.
- Select the Cancel button to discard any changes and exit the Add Table dialog.
Starting with the 2022.9.0 release, you can test a portion of the SQL query. Select the portion you want, and then select Execute. However, you must execute the whole query to be able to save the table. The wizard uses the executed script to create the table.
Customize Table SQL Query
The Edit Query dialog box displays when the Customize SQL button is selected. Perform the following actions within Edit Query:
- Change the name of the table.
- Modify the SQL for the table. Remove columns, add column expressions, and specify a predicate WHERE clause.
- Select the Format button to format the SQL statements.
- Select the Execute button to test the SQL modifications.
- View the output of the SQL test execution within Output V.
- Select the Save button to save the modifications.
- The following message will be displayed: Customized tables will lose detected joins. Save? Select Yes to proceed with the save and exit the Edit Query dialog. Select No to return back to the Edit Query dialog.
- Select the Cancel button to discard any changes and exit the Edit Query dialog.
Step 3: Finalize
- Leave the Create joins between selected tables if foreign key relationships are detected checkbox checked to have Incorta automatically create table joins based on foreign key-to-primary table relationships. Uncheck this box to disable this feature for the current schema build.
- Select Create Schema.