Testing the consistency of your data during an upgrade project is critical to the success of your project. In the first test runs of the upgrade, it is common to experience data consistency errors both during and after the upgrade process. Follow these general guidelines to quickly find the root cause of your issues and determine the best course of action for the next run of your upgrade testing. These steps assume that you have gone all the way through the Data upgrade cockpit within the Dynamics AX 2012 environment.
One of the very first steps would be to look at the Generate Mapping window and see if there are any mapping errors for tables. Any mapping error will cause the table to not be copied during the bulk copy process.
Use a Divide-and-Conquer approach – Determine if the error happened on the Source or Target side of the Upgrade Process.
The first step in troubleshooting a data consistency issue is to determine if the issue happened on either side of the Upgrade Process.
1. Look at the DirectSQL field in the ReleaseUpdateBulkCopyTable on the table in which the error was found. This field stores the SQL Statement that the Upgrade Framework generated during Bulk Copy
If no statement can be found for the table in question, the table was not copied over to the Target System. Verify that all the mappings for that table are correct.
2. Run the statement against the Source Database and check the results. This is a snapshot of the data in this table as it was copied over to the target system.
3. Analyze the data in the results obtained by the SQL Statement. There are two options:
a. The data is not in a good state – Then the error happened in the Source side of the Upgrade Process
b. The data looks correct – Then the error happened on the Target side of the Upgrade Process
Troubleshooting Errors in the Source System
- Determine if the table in question is part of one or more transformations in the Source System. This can be easily achieved by querying the SourceTableName field in the ReleaseUpdateTransformTable.
- If the table is a Dictionary Table in the Source Environment, determine which scripts were populating that table by querying the ReleaseUpdateDictionaries, ReleaseUpdateTransformTable and ReleaseUpdateTransformScripts tables.
- If the table is part of a transformation in Preprocessing, make sure that the Shadow tables were populated correctly
- Query all Shadow tables listed for the table in question. Take note of the JoinType field in the ReleaseUpdateTransformTable
- For Shadow tables that participate in an Inner Join with their Source Table, make sure that for each record in the Source Table there is a matching entry in the Shadow table by using the Source Table’s RecID field and the Shadow Table’s RefRecID field.
- For Shadow tables that participate in a Right Join with their Source Table, make sure that for each record in the Source Table there is a matching entry in the Shadow table.
- For Shadow tables that participate in a Left Join with their Source Table, make sure that for each record in the Shadow Table there is a matching record in the Source Table. Join the two tables together and make sure that all records from the source table are returned.
- If an error was found in the Shadow Table, determine which scripts were operating on that table by querying the ReleaseUpdateTransformTable and ReleaseUpdateTransformScripts table.
- If the table is neither a Dictionary Table nor is part of a transformation in preprocessing, the table in question had bad data before the Upgrade Process started. Determine how to best fix the data.
Troubleshooting Errors in the Target System
Once determined that the data was copied correctly during Bulk Copy, we will need to find which scripts worked against the table in question. This information can be obtained by looking into the ReleaseUpdateScriptsUsedTables and ReleaseUpdateScripts tables. This will considerably narrow the list of scripts down to a more manageable amount.
From this point on, we can search within the scripts to find what kind of operations they are doing on the script. It is also useful to look into the dependency tree to find out the chain of scripts involved in a table’s upgrade process. Look into the ReleaseUpdateScripts and ReleaseUpdateScriptDependency table to get this information.