Create and Edit an Logical Unit Table

LU tables are the basic building blocks for creating Logical Units. There are several ways to create a new LU table.

There are 3 main methods for creating a new LU table:

  1. Based on a Data Source table
  2. Manually
  3. By duplicating an existing table

Create a Table Based on Data Source

From the LU schema:

  • Right-click on the working area and select either the New Table From SQL Based DB Query, the New Table From SQL Based Root Function or the New Table From SQL Based Broadway Flow option.

  • From the Project Tree:

    • Use the Auto Discovery Wizard to either create or edit the LU. The tables and their populations are automatically created and added to the LU schema. If a table already exists, it is possible to override the existing implementation.

    • Create a new table manually. In this option, the table population should be created separately. Once the population has been created, add the table to the LU schema. Note that if the table is used as the master data, there is no need to create the table population.

  • Create a table based on data source table, using the DB Interface Explorer:

    • Switch from the Project Tree to the DB Interface Explorer by clicking the icon on the left Activity Bar.

    • Click on the relevant interface data source and select the required table.

    • Right-click on the table and choose "Add Tables to Schema". The table will be created in Fabric according to the selected table definitions, along with its population, and will be added to the schema.

    Note that you can expand the table in the DB Interface Explorer and select specific columns. By doing so, the created Fabric table will contain only the columns that were selected.

  • Create a table based on data source tables, using the Query Builder:

    • In the schema window's top bar, click the icon to open the Add New Table pop-up window
    • Choose "Create New from source with SQL query".
    • Name the table and click on Create.
    • A Query Builder pop-up window appears.
    • In the Query Builder window, select the required interface. Then either write the SQL query in the upper part of the Query Builder or expand the interface schema and its table list to find the relevant table and select it.
    • Once a query exists in the Query Builder, you can test it by clicking on the Execute button. When done, click on the Create button.

Click for More Information about Adding a Table to a Schema.

Create a Table Manually

  1. Go to Project Tree > Logical Units > [LU Name], right-click Tables > New Table to display the Columns tab in the Table Schema window.
  2. Define the Table Schema:
    • Complete the settings of each table column, such as the Name or Data Type.
    • Optional: Define the table Indexes.
    • Optional: Define the CDC and the table’s Search indexes.
  3. Define the Table Properties.
  4. Save the table.

Duplicate an existing table

From the Project Tree:

  • Copy an existing LU table from one LU to another. In this option, if there is a table population, it is copied as well.
  • Use the Save As option. Note that this option does not copy the table population.

From the Project Tree:

  1. Right-click on a table and choose Duplicate from the context menu.
  2. A dialog box opens, where you insert a name to the duplicated table and click OK.

A new table is created along with a duplicated table population flow.

Edit an LU Table

  1. Go to Project Tree > Logical Units > [LU Name] > Tables > [Table Name] to display the Columns tab in the Table Schema window.
  2. Optional: Edit the table’s Columns, Indexes, Search Indexes or Table Properties.
  3. Save the table.

Note that in specific cases, the Data Type of a column - that has been automatically created based on a DB table - may need to be manually edited. For example, a Number column in Oracle is created in Fabric with Data Type = Real. This column may need to be modified to either an Integer or a Text in the LU table.

Table Definition

While creating or editing an LU table, you can set the columns and their types, mark mandatory fields and set the default values.

Name

Column name.

Data Type

The Data Type of the values in the column. The following types are supported:

  • Integer
  • Real
  • Text
  • Datetime, saved as java.sql.Timestamp class (supported up to nanoseconds level)
  • Date, saved as java.sql.Date class
  • Time, saved as java.sql.Time class
  • Blob

Note that if the Oracle field type is Number, Fabric sets the Data Type of this field to Real in the LU table and the number in Fabric has a decimal point. Therefore, if a column in the LU table does not need a decimal point (for example, for the CUSTOMER ID), change the Data Type of the LU Table column to either an Integer or a Text.

Mandatory

When checked, this column must be populated (not null); otherwise, leave it unchecked.

Default Value

The value set in this column is used for populating the table when there is no input for this column.

Comment

Additional information.

Previous

Create and Edit an Logical Unit Table

LU tables are the basic building blocks for creating Logical Units. There are several ways to create a new LU table.

There are 3 main methods for creating a new LU table:

  1. Based on a Data Source table
  2. Manually
  3. By duplicating an existing table

Create a Table Based on Data Source

From the LU schema:

  • Right-click on the working area and select either the New Table From SQL Based DB Query, the New Table From SQL Based Root Function or the New Table From SQL Based Broadway Flow option.

  • From the Project Tree:

    • Use the Auto Discovery Wizard to either create or edit the LU. The tables and their populations are automatically created and added to the LU schema. If a table already exists, it is possible to override the existing implementation.

    • Create a new table manually. In this option, the table population should be created separately. Once the population has been created, add the table to the LU schema. Note that if the table is used as the master data, there is no need to create the table population.

  • Create a table based on data source table, using the DB Interface Explorer:

    • Switch from the Project Tree to the DB Interface Explorer by clicking the icon on the left Activity Bar.

    • Click on the relevant interface data source and select the required table.

    • Right-click on the table and choose "Add Tables to Schema". The table will be created in Fabric according to the selected table definitions, along with its population, and will be added to the schema.

    Note that you can expand the table in the DB Interface Explorer and select specific columns. By doing so, the created Fabric table will contain only the columns that were selected.

  • Create a table based on data source tables, using the Query Builder:

    • In the schema window's top bar, click the icon to open the Add New Table pop-up window
    • Choose "Create New from source with SQL query".
    • Name the table and click on Create.
    • A Query Builder pop-up window appears.
    • In the Query Builder window, select the required interface. Then either write the SQL query in the upper part of the Query Builder or expand the interface schema and its table list to find the relevant table and select it.
    • Once a query exists in the Query Builder, you can test it by clicking on the Execute button. When done, click on the Create button.

Click for More Information about Adding a Table to a Schema.

Create a Table Manually

  1. Go to Project Tree > Logical Units > [LU Name], right-click Tables > New Table to display the Columns tab in the Table Schema window.
  2. Define the Table Schema:
    • Complete the settings of each table column, such as the Name or Data Type.
    • Optional: Define the table Indexes.
    • Optional: Define the CDC and the table’s Search indexes.
  3. Define the Table Properties.
  4. Save the table.

Duplicate an existing table

From the Project Tree:

  • Copy an existing LU table from one LU to another. In this option, if there is a table population, it is copied as well.
  • Use the Save As option. Note that this option does not copy the table population.

From the Project Tree:

  1. Right-click on a table and choose Duplicate from the context menu.
  2. A dialog box opens, where you insert a name to the duplicated table and click OK.

A new table is created along with a duplicated table population flow.

Edit an LU Table

  1. Go to Project Tree > Logical Units > [LU Name] > Tables > [Table Name] to display the Columns tab in the Table Schema window.
  2. Optional: Edit the table’s Columns, Indexes, Search Indexes or Table Properties.
  3. Save the table.

Note that in specific cases, the Data Type of a column - that has been automatically created based on a DB table - may need to be manually edited. For example, a Number column in Oracle is created in Fabric with Data Type = Real. This column may need to be modified to either an Integer or a Text in the LU table.

Table Definition

While creating or editing an LU table, you can set the columns and their types, mark mandatory fields and set the default values.

Name

Column name.

Data Type

The Data Type of the values in the column. The following types are supported:

  • Integer
  • Real
  • Text
  • Datetime, saved as java.sql.Timestamp class (supported up to nanoseconds level)
  • Date, saved as java.sql.Date class
  • Time, saved as java.sql.Time class
  • Blob

Note that if the Oracle field type is Number, Fabric sets the Data Type of this field to Real in the LU table and the number in Fabric has a decimal point. Therefore, if a column in the LU table does not need a decimal point (for example, for the CUSTOMER ID), change the Data Type of the LU Table column to either an Integer or a Text.

Mandatory

When checked, this column must be populated (not null); otherwise, leave it unchecked.

Default Value

The value set in this column is used for populating the table when there is no input for this column.

Comment

Additional information.

Previous