How do I create a New Reference Table in Fabric Studio

A Reference table holds information common to all LU instances or to multiple LU. For obvious optimization reasons, this information is stored in an external table and not in each LUI MicroDB.

How Do I Create a New Reference Table in Fabric from an External Source?

1. Go to Project Tree > References , right click Create References Based On DB Tables to display the DB Browser menu, Context menu and References popup window.

2. Click DB Connection (top of the window) and select the Data Source Interface to display the Tables directory in the source DB.

3. Select the table. To select multiple tables, press the Ctrl key.

4. Optional:

Add a prefix to the Reference table's name in the Name Prefix field (window's footer).

• If there are objects in the project with the same name, add a prefix to differentiate between Reference tables and LU tables with similar names.
• We recommend using a prefix that indicates the project name as a Reference table.

To populate the Reference Table using a Broadway flow, tick the Table population based Broadway flow box.

5. Click Create Tables to add the new Reference table under References in the Project Tree. If the table does not appear immediately, click Refresh on the top of the Project Tree.

Editing and Viewing Reference Tables

Reference tables can be edited by either changing the default data mapping, adding transformations or adding or removing columns like in LU tables.

When the Broadway flow population option is selected as defined above in Step 4, the table population process can be edited using the Broadway flow described here.

How do I View Reference Table Data?

To access the Reference Viewer do the following:

1. Go to Project Tree > References, right click References Viewer, and then select the table. The Data Viewer window is displayed according to its hierarchy in the Instances Tree pane.

2. Click the data file to display the components hierarchy in the Instance DB Tree pane.

2. Click the table name to display the table's data in the main Data Viewer window.

Reference Tables Properties

Additional properties can be defined in the Table Properties panel in the right pane of the selected Table tab.

Main Properties

• Name, can be defined or modified.
• Primary Key column, ensures uniqueness of records.
• Column collation type:
  • BINARY, compares string data regardless of text encoding.
  • NOCASE, folds upper case characters to their lower case equivalents.
  • RTRIM, ignores trailing space characters.

Sync Method

By default Reference tables are synched in the background of each table according to the defined Sync policy. The following Sync options can be selected in the Table Properties panel:

• None, default value, synchronization is according to the Sync policy defined.

• Time Interval, set in days.hrs:min:sec format.

• Decision function, syncs the table according to the Decision function defined under the References > Java folder.

Miscellaneous

Functions or other tables can be attached to the Reference table:

• Enrichment Functions, to perform data manipulations on the table's content.
• Other Reference Tables, onto which the current Reference table is depending (e.g. it needs data from these tables).

Attach the Reference Table to an LU Schema

Before accessing the Reference Table from a specific LU, or before it can be used as a lookup object, it must be attached to the LU.

Configure LU to Use a Reference Table

1. Open the LU Schema Window

2. In the right panel, select the References tab.

3. Check the relevant Reference table(s) option.

4. Click Save to save the association created between the LU and the Reference Table(s).

Note: Reference tables can also be accessed via Lookup tables, Web Services, functions, jobs and Broadway Actors.

Deploy the Reference Tables

Reference Tables must be deployed before being used. As a result of the deployment, a synchronization job process is triggered in the background to ensure that all commonDB copies are kept in-sync across the Fabric Cluster.

To deploy the Reference Tables, go to the Project Tree, right click References, select Deploy to Server and then the Server to deploy to the Reference table.

Note:

If the reference table as been attached to an LU Schema as described above, the LU must also be (re-)deployed.