You are viewing content for 5.0 | 4.9 | 4.8 | 4.7 | 4.6 | 4.5 | 4.4 | 4.3 | Previous Releases


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 schema in Incorta. It works with existing data sources to define tables and relationships between tables. Depending on the data source type, the Schema Wizard detects foreign key-to-primary table relationships and defines these as child-to-parent relationships in Incorta.

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.

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.

Important

Validation Rules for a Schema name:
A schema name…

  • must be unique, and cannot have the same name as an existing schema
  • must be between 1 and 255 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 (_) characters
  • besides underscore (_), cannot contain special characters, symbols, and spaces

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.

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 Incorta table column.
Incorta Function drop down list Select the function of the Incorta 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.

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.

© Incorta, Inc. All Rights Reserved.