Once tables and fields have been added to the Data Model you can map in data from the data sources and from Field Transformations to control where data should be populated when the data is refreshed. This article will cover how to map fields from the source data into the Data Model tables and fields.
Once tables and fields have been added to the Data Model, you are ready to map in the source data to these tables and fields.
To map source data into the tables and fields in the Data Model, first you must check out the extension that the mapping should be associated with and then you can navigate to the Data Model section within Data Management.
Adding a Source Table to a Data Model Table
Click on the Data Model table that you wish to map data source tables and fields into, and you will then see the Data Model structure on the right. If there are not currently any source tables that have been added for the Data Model table, you will just see a + at the top of the field list after the Field Name and Key Field columns. Clicking on this will add a source table.
When you click the + a "Staging Model" window will appear showing all the tables within the Staging Area. Click on the table that you would like to add as a source table. In the example below we will add the Customer table.
Once you have clicked the table, the table name will show up at the top of the window as a new column.
Mapping Source Table Fields to Data Model Fields
Once at least one source table has been added, you can click the dropdown list for each Data Model field to see a list of all fields that can be mapped into this field for the source table that the column represents. You can see in the example below that several fields have now been mapped in from the source table.
Continue this process until all fields have been mapped. Once the extension has been checked in and the pipeline has been published, data from the selected fields in the source table will now be mapped into the tables in the Data Model reporting table when the data is refreshed.
Removing a Source Table
If you need to remove a source table, you can click the - to the right of the table name. This will remove the source table mapping from the selected Data Model table.
Mapping Data from Multiple Source Tables
If you need to bring in data from multiple source tables into a single Data Model table, add the first source table to be mapped and then click the + and select the next table to be added as a source table from the Staging Tables list. You can add as many source tables as needed.
You will then map all the relevant fields from the second source table to the Data Model fields in the same manner you did with the first source table.
This will result in data from both tables being consolidated into the single Data Model table for reporting. For example, in the case above where data from the Customer and Vendor tables are being mapped into a single Data Model table, if there were 200 records in the Customer table and 50 records in the Vendor table, the resulting Data Model table would contain 250 records.