Physical Foreign Keys Setup in the Jet Data Manger

Overview

The Jet Data Manager can store physical foreign keys in the data warehouse. While the JDM already knows how tables are related, Physical Foreign Keys will store this information in the data warehouse, enabling other applications that read from the data warehouse to know the relationships between tables as well.

This feature is useful when creating reports directly from the data warehouse. For instance, with physical foreign keys enabled, Microsoft PowerPivot, Power BI, and Jet Reports can find related tables when importing tables for use in reports. In addition to that, you can set up PivotTables without first defining the relationships between tables.

Process

To make a relationship between two tables physical, follow these steps:

1. If a relation is not already defined between the two tables then it must first be defined. This is done by dragging a field from the source table (for example the Code field from the Salesperson table) and dropping it on the matching field in the destination table (for example the Salesperson Code field in the Sales Transactions table). It will prompt if a relation should be created.

2. Right-click the relation in the destination table, go to Relationship Type and click Error with a physical relation.

3. Repeat these steps for all relations in the project that should be set as foreign keys in the SQL database.

   

Note: The physical relation cannot be enabled on views. Tables with physical relation enabled need to have a primary key. This can be done by right-clicking on the necessary field(s) and selecting Add Primary Key. Tables with physical relation enabled cannot have any missing links, for example foreign keys that do not exist in the table the foreign key points to. However, since the relationship is defined as “error with physical relation”, rows with errors such as this will be moved to the error table.