It will occasionally be reported that not all records are being imported into a particular table in the data warehouse or staging database. This is generally the result of an incorrect setting or selection that has been made in the Jet Data Manager. This article highlights some of the most common causes.
Data Selection Rule
Data Selection Rules in the Jet Data Manager act as filters on the data source that control which data is brought into the destination table. It is possible that a Data Selection Rule has been added to a table that is limiting records that are being brought into the table. In the data warehouse, Data Selection Rules will be represented by a filter icon with the selection criteria to the right:
When troubleshooting a table in the staging database, all data selection rules are defined in the Data Source itself (not the staging database table).
If it is reported that the data warehouse or staging database is missing data for an entire company, it is most likely because this company has not been selected under the data source. This can be updated by right-clicking the data source and selecting the appropriate option to modify the companies being used.
Defining the companies to be used will vary depending on the ERP system being connected to. Some of the most common methods are:
- NAV: Right-click the data source name and select "Set Up Accounts"
- AX: Right-click the data source name and select "Set Up Accounts"
- GP: Right-click the data source name and select "Synchronize Dynamics GP Companies"
If incremental loading has been configured for the table, double check to ensure that all primary keys and incremental loading rules have been properly defined. If they have not been defined correctly for your data source it is possible that incremental loading is not executing properly and records are being omitted from the table. If this could be the cause it is recommended to resolve the issue and then complete a full load on the table. This can be done by right-clicking the table, selecting "Deploy and Execute", and then ensure that the "Full Load Valid Table" selection at the top of the "Deploy and Execute Steps" window has been checked.
Incorrect Primary Key
In the Jet Data Manager it is possible to set the fields to be used for the primary key in a table. This combination of fields should ensure that every record is the table is unique. When the primary key is not set properly then any records that contain duplicate combinations of these primary key fields will be marked as invalid and excluded from the table. This can be verified by navigating to the Errors tab in the Jet Data Manager to see if any records exist. Any records that are shown on the Errors tab are being excluded from the destination table. If this is the cause the issue then the correct primary key should be researched further. Also, removing all fields from being present in the primary key will resolve the issue as well. This will not work, however, if the table is being incrementally loaded as a primary key is required for incremental loading.