Table Relations and Relationship Types

The feature referred to in this article applies to the Jet Data Manager version 16.4.3 and higher.

Overview

Sometimes, it's desirable to be able to create multiple foreign key relationships within a Jet Data Manager project.

For the example below, we're going to link the Customer table in the standard NAV 2015 project to the Posted Sales Transaction table.

The links will be:

Company=Company No=Bill to Customer No

AND

Company=Company No=Sell to Customer No

Create and Set Relation Types

The first thing to do will be to determine the dimension table that contains the keys to link to the fact table. Once that table is selected, the recommendation is to open it in a new window. This can be done by right-clicking and selecting "Open in new window" or selecting the table and hitting CTRL+W on your keyboard.

1. Create the first relation

   The fields that will create the relation should be selected, and dragged on to the corresponding columns on the fact table. In this case, the Company and No fields represent the composite primary key of this table, so those columns will be used.

   

   Dragging the first column (Company to Company) will present the box withe following text:

   Do you want to create relation?

   

   Clicking "Yes" here creates a relation between the tables. That shows up at the bottom of the field list as shown below.

   

   Now create any additional relation for this particular join by dragging the corresponding fields over from the dimension table. When you do this, a new box will appear.

   

   In this case, the No=Sell to Customer No relation should be added to the existing relation "Customer_Company". Once that is complete, the relation can be renamed more appropriately. Below is the finished "Sell-to Customer No" relation between the Customer table and Posted Sales Transactions.

   

2. Create the second relation

   In the example provided, the Customer dimension table relates to the Posted Sales Transactions fact table in two ways. One relation has already been created, but the second still has not. Start by dragging the Company again, which will prompt for creation of a new relation:

   

   Clicking yes will add a new relation to the relations section at the bottom of the fact table.

   

   The process will be very similar as it was before. Select the second (third, etc.) part of the relation and drag and drop it again.

   

   The box that appears looks identical to the one previously, however the original relation has been renamed. Renaming the relations isn't necessary, but it will help identify them later. Both relations are completed and renamed.

   

   Once the relations are available in the project, they can be used for many purposes. They can be used in Conditional Lookups between the two tables, or, more commonly, be used for identifying missing key relations between the tables.

3. Set the relation types

   Now that the relations are defined, a relationship type should be set up for the relation to perform the aforementioned functions. The default type is Relation which simply provides pre-built joins between the tables.

   The other three options are shown below:

   

   • Error: This relation type automatically adds a data quality check and foreign key violations will result in records ending up in the Errors tab of the Jet Data Manager. These records are physically removed from the table and moved into the Error table.
   • Error with physical relation: This can only be enabled on physical tables, not views. This functionality stores physical foreign keys in the data warehouse. This also requires primary keys to be added to each table in the project used in relations. The behavior aside from those things is identical to Error. Please note that if using this functionality, you'll also need to have a "Unique Index" of the primary key columns set up on the dimension table.
   • Warning: This relation type also automatically adds a data quality check but foreign key violations will not be removed from the table. Rather, they will be flagged and copied into the Error table. These can be found by looking at the Warnings tab.

   Below you'll find the location of the Warnings and Errors tab in the Jet Data Manager.