Knowledge Base Academy Community My Tickets
Fabric 8.1
Fabric 8.1 Fabric 8.0 Fabric 7.2 Fabric 7.1 Fabric 7.0 Fabric 6.5 Fabric 6.4

LU Table Properties

The Table Properties tab is displayed in the right pane of the Schema window, when a table is selected, or when opening a table through the Project Tree and then open its properties pane.

It displays a list of properties, by sections, that shall be defined for each LU table, as follows:

Section

Property

Description

Query Statements Settings

 Columns Collation

There are 3 options for getting and handling the retrieved data:

  • BINARY - (default) compares the exact string in the field with the SQL statement.
  • NOCASE - enables the Select statement to ignore upper/lower case letters when comparing text fields. For example:
    Select TYPE from tblExample where NAME = ‘value’ returns records when the NAME field is set to either ‘VALUE’ or ‘value’.
  • RTRIM - enables the Select statement to ignore white space characters on the right side of the string when comparing text fields. For example:
    Select TYPE from tblExample where the NAME = ‘value’ returns records that match both ‘value’ and ‘value‘.
Full Text Search

When set to True, it enables the use of the MATCH Sqlite command as part of the WHERE clause of a Select statement that reads data from a Fabric table. Default = False.

Click for more information about the Match command:

http://www.sqlite.org/fts3.html#section_3

Sync

Sync Method

There are 4 Sync methods:

  • None
  • Inherited (default)
  • Time Interval
  • Decision Function
Delete Mode

This property defines the delete policy of the previous records in the LU table (populated prior to the current sync). The values are All (default value), Off or NonUpdated:

  • All - the entire LU table is truncated before the populations are executed.
  • Off - the previous records are not deleted.
  • NonUpdated - deletes the previous records (created by the earlier sync) if the data no longer exists for the LUI in the source.

    Click here for more information about the Delete Mode.

    Notes:

    • It is recommended to set the NonUpdated value when the LU table has CDC fields in order to send CDC messages only for the updated records. If the Delete Mode is set to All, Fabric sends delete messages for all the truncated records and inserts messages for the newly inserted records.
    • If the Delete Mode is NonUpdated, it is recommended to define a PK on the LU table and to set the LU table population mode to Upsert or Update in order to delete only the old data. If the LU table does not have a PK, new records are added to the LU table and all previous records are deleted.
    		•

    Indexes

    Indexes List

    Sets table's indexes, as explained here.

    Triggers

    		 Trigger List

    Refers to Trigger functions that are executed when there is a change in LU table's data.

    To add a Trigger function, click the '+' button and select the function name. Only Trigger functions are displayed.

    Data Change Indexes

    		 Columns' definitions per CDC Consumer

    Refer to CDC Implementation Steps to learn how to edit this list.

    The Table Properties tab is displayed in the right pane of the Table's window.

    image

    The Properties tab displays a list of properties that must be defined for each LU table, as follows:

    Main

    Uneditable fields:

    • Name - LU table name.
    • ID - generated by Fabric.

    Instance ID Column

    A unique field that is used as the LU table Instance ID.

    Columns Collation

    There are 3 options:

    • BINARY - (default) compares the exact string in the field with the SQL statement.
    • NOCASE - enables the Select statement to ignore upper/lower case letters when comparing text fields. For example:
      Select TYPE from tblExample where NAME = ‘value’ returns records when the NAME field is set to either ‘VALUE’ or ‘value’.
    • RTRIM - enables the Select statement to ignore white space characters on the right side of the string when comparing text fields. For example:
      Select TYPE from tblExample where the NAME = ‘value’ returns records that match both ‘value’ and ‘value ‘.

    Full Text Search

    When set to True, it enables the use of the MATCH Sqlite command as part of the WHERE clause of a Select statement that reads data from a Fabric table. Default = False.

    Click for more information about the Match command:

    http://www.sqlite.org/fts3.html#section_3

    Sync Method

    There are 4 Sync methods:

    • None
    • Inherited (default)
    • Time Interval
    • Decision Function

    Delete Mode

    Fabric 6.5.9 adds the Delete Mode property to the LU table. This property replaces the previous Truncate Before Sync LU table's property and defines the delete policy of the previous records in the LU table (populated prior to the current sync). The values are All (default value), Off or NonUpdated:

  • All - the entire LU table is truncated before the populations are executed.
  • Off - the previous records are not deleted.
  • NonUpdated - deletes the previous records (created by the earlier sync) if the data no longer exists for the LUI in the source.

    Click here for more information about the Delete Mode.

    Notes:

    • It is recommended to set the NonUpdated value when the LU table has CDC fields in order to send CDC messages only for the updated records. If the Delete Mode is set to All, Fabric sends delete messages for all the truncated records and inserts messages for the newly inserted records.
    • If the Delete Mode is NonUpdated, it is recommended to define a PK on the LU table and to set the LU table population mode to Upsert or Update in order to delete only the old data. If the LU table does not have a PK, new records are added to the LU table and all previous records are deleted.
    		•

    Enrichment Functions

    Refers to Enrichment Functions that are executed after all LU tables are populated.

    • The execution order is determined on an LU level and is based on the Sync policy of the attached table. When no Enrichment function is attached - the display is ‘Empty’.
    • When one or more Enrichment functions are attached - the display is ‘<x> enrichments’ (where <x> is the number of attached Enrichment functions).

    To select an Enrichment function, click the 3 dots next to the Enrichment functions property and select the function name. Only functions without input and output parameters are displayed.

    On Change

    Refers to Trigger functions that are executed when there is a change in an LU table's data.

    To select a Trigger function, click the 3 dots next to the On Change property and select the function name. Only Trigger functions are displayed.

    Previous

    LU Table Properties

    The Table Properties tab is displayed in the right pane of the Schema window, when a table is selected, or when opening a table through the Project Tree and then open its properties pane.

    It displays a list of properties, by sections, that shall be defined for each LU table, as follows:

    Section

    Property

    Description

    Query Statements Settings

    		 Columns Collation

    There are 3 options for getting and handling the retrieved data:

    • BINARY - (default) compares the exact string in the field with the SQL statement.
    • NOCASE - enables the Select statement to ignore upper/lower case letters when comparing text fields. For example:
      Select TYPE from tblExample where NAME = ‘value’ returns records when the NAME field is set to either ‘VALUE’ or ‘value’.
    • RTRIM - enables the Select statement to ignore white space characters on the right side of the string when comparing text fields. For example:
      Select TYPE from tblExample where the NAME = ‘value’ returns records that match both ‘value’ and ‘value‘.
    Full Text Search

    When set to True, it enables the use of the MATCH Sqlite command as part of the WHERE clause of a Select statement that reads data from a Fabric table. Default = False.

    Click for more information about the Match command:

    http://www.sqlite.org/fts3.html#section_3

    Sync

    Sync Method

    There are 4 Sync methods:

    • None
    • Inherited (default)
    • Time Interval
    • Decision Function
    Delete Mode

    This property defines the delete policy of the previous records in the LU table (populated prior to the current sync). The values are All (default value), Off or NonUpdated:

  • All - the entire LU table is truncated before the populations are executed.
  • Off - the previous records are not deleted.
  • NonUpdated - deletes the previous records (created by the earlier sync) if the data no longer exists for the LUI in the source.

    Click here for more information about the Delete Mode.

    Notes:

    • It is recommended to set the NonUpdated value when the LU table has CDC fields in order to send CDC messages only for the updated records. If the Delete Mode is set to All, Fabric sends delete messages for all the truncated records and inserts messages for the newly inserted records.
    • If the Delete Mode is NonUpdated, it is recommended to define a PK on the LU table and to set the LU table population mode to Upsert or Update in order to delete only the old data. If the LU table does not have a PK, new records are added to the LU table and all previous records are deleted.
    		•

    Indexes

    Indexes List

    Sets table's indexes, as explained here.

    Triggers

    		 Trigger List

    Refers to Trigger functions that are executed when there is a change in LU table's data.

    To add a Trigger function, click the '+' button and select the function name. Only Trigger functions are displayed.

    Data Change Indexes

    		 Columns' definitions per CDC Consumer

    Refer to CDC Implementation Steps to learn how to edit this list.

    The Table Properties tab is displayed in the right pane of the Table's window.

    image

    The Properties tab displays a list of properties that must be defined for each LU table, as follows:

    Main

    Uneditable fields:

    • Name - LU table name.
    • ID - generated by Fabric.

    Instance ID Column

    A unique field that is used as the LU table Instance ID.

    Columns Collation

    There are 3 options:

    • BINARY - (default) compares the exact string in the field with the SQL statement.
    • NOCASE - enables the Select statement to ignore upper/lower case letters when comparing text fields. For example:
      Select TYPE from tblExample where NAME = ‘value’ returns records when the NAME field is set to either ‘VALUE’ or ‘value’.
    • RTRIM - enables the Select statement to ignore white space characters on the right side of the string when comparing text fields. For example:
      Select TYPE from tblExample where the NAME = ‘value’ returns records that match both ‘value’ and ‘value ‘.

    Full Text Search

    When set to True, it enables the use of the MATCH Sqlite command as part of the WHERE clause of a Select statement that reads data from a Fabric table. Default = False.

    Click for more information about the Match command:

    http://www.sqlite.org/fts3.html#section_3

    Sync Method

    There are 4 Sync methods:

    • None
    • Inherited (default)
    • Time Interval
    • Decision Function

    Delete Mode

    Fabric 6.5.9 adds the Delete Mode property to the LU table. This property replaces the previous Truncate Before Sync LU table's property and defines the delete policy of the previous records in the LU table (populated prior to the current sync). The values are All (default value), Off or NonUpdated:

  • All - the entire LU table is truncated before the populations are executed.
  • Off - the previous records are not deleted.
  • NonUpdated - deletes the previous records (created by the earlier sync) if the data no longer exists for the LUI in the source.

    Click here for more information about the Delete Mode.

    Notes:

    • It is recommended to set the NonUpdated value when the LU table has CDC fields in order to send CDC messages only for the updated records. If the Delete Mode is set to All, Fabric sends delete messages for all the truncated records and inserts messages for the newly inserted records.
    • If the Delete Mode is NonUpdated, it is recommended to define a PK on the LU table and to set the LU table population mode to Upsert or Update in order to delete only the old data. If the LU table does not have a PK, new records are added to the LU table and all previous records are deleted.
    		•

    Enrichment Functions

    Refers to Enrichment Functions that are executed after all LU tables are populated.

    • The execution order is determined on an LU level and is based on the Sync policy of the attached table. When no Enrichment function is attached - the display is ‘Empty’.
    • When one or more Enrichment functions are attached - the display is ‘<x> enrichments’ (where <x> is the number of attached Enrichment functions).

    To select an Enrichment function, click the 3 dots next to the Enrichment functions property and select the function name. Only functions without input and output parameters are displayed.

    On Change

    Refers to Trigger functions that are executed when there is a change in an LU table's data.

    To select a Trigger function, click the 3 dots next to the On Change property and select the function name. Only Trigger functions are displayed.

    Previous

    © Copyright 2024 K2VIEW