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 Essentials 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.
To make a relationship between two tables physical, follow the steps below.
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 on the relation in the destination table, go to Relationship Type and click on 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.