Set up and Apply Changes to Mapping Sets

The Mapping Set feature allows multiple tables from one or more data sources to be merged into a single Prepare table using a UNION ALL approach. Multiple mappings can be created manually, but Mapping Sets allow them to be created using rules — making it faster to add multiple mappings and easier to maintain them when tables are added or removed in the future.

Prerequisites

All tables to be merged must share at least some fields with identical names. Fields with different names or fields that do not exist in all tables cannot be merged.

Set up a Mapping Set

This example uses Dynamics 365 Business Central data sources with multiple companies. Two data sources contain four and two companies respectively — six versions of the same tables — and the goal is to merge all six into one Prepare table.

1. Drag one of the tables into a Data Area. Select only the fields that are shared with the other tables to be merged.
2. Ensure the table is not in Simple mode. Simple mode is indicated by an S icon on the table and can be toggled in Table Settings > General.
3. Right-click the mappings folder under the table and select Convert to Mapping Set.
4. Click the existing rule and select Edit to modify the rule so it applies to all target tables.
5. Configure the filter criteria using Data Source, Schema, and Table conditions. Each condition can be set to Include matches or Exclude matches, with filtering options including Begins with, Ends with, Contains, and case-insensitive variants. In this example, Ends with (ignore case) is used to match all tables ending with "Customer".
6. Click Refresh to preview the results:
   • Tables matching the rule appear in Tables in the mapping set.
   • Tables that match the rule but do not share the required fields appear in Tables excluded for not matching the table structure. Hover over a table in this list to see which fields are missing.
7. Click OK. Jet Analytics Data Integration will attempt to synchronise the Mapping Set with the Ingest instance and prompt for confirmation. Click Yes to apply the changes.
8. The Mapping Set appears in the mappings folder for the table. Expanding it shows the names of all mapped tables and the short name of their respective data source.

Source Table field

After applying the Mapping Set, a Source Table field is automatically added to the Prepare table. This field identifies the mapped source table for each row.

Consider including this field in the table's primary key. The same primary key value can appear across different mapped tables, which would cause primary key violations if the Source Table field is not included. For Dynamics 365 Business Central, a SQL snippet can be applied to the Source Table field to extract only the company name from the full table name.

Apply changes to a Mapping Set

Changes to Mapping Sets are not applied automatically. The steps below describe how to handle different types of source changes.

Tables removed or added

Before synchronising, ensure that all Metadata Import tasks and transfer tasks for the relevant data sources have been executed in the Ingest instance.

1. Right-click the Mapping Set and select Synchronize Mapping Set.
2. The detected changes are shown. Click Yes to apply.
3. Deploy the table to apply the changes.

Changes to data sources in the Ingest instance

If data source changes have been made in the Ingest instance (such as incremental load rules or data type changes), first ensure that the Metadata Import task for the data source has been executed. Then:

1. Right-click the Ingest instance and select Synchronize Objects.
2. If changes are detected, a confirmation dialog appears.
3. Click Yes. Any affected field data type changes or incremental load rule updates are shown.
4. Deploy the table to apply the changes.

Columns removed, changed, or added

1. Right-click the Ingest instance and select Synchronize Objects.
2. Right-click the Mapping Set and select Synchronize Mapping Set.
3. A dialog appears asking whether to remove mapped tables that now have missing fields:
   • Click Yes to remove the affected mapped tables from the Mapping Set.
   • Click No to keep all mapped tables. To resolve the mismatch, identify the field that is no longer present in all versions of the mapped table, then remove or re-add the field from the Prepare table and run Synchronize Mapping Set again.