Today's blog post was written by Nick Costanzo (Principal Consultant) and Nathan Williams (Consultant) at Sonoma Partners.
If you've ever had to use the native import tool for Dynamics 365, you've more than likely had the experience of running into import errors of some sort. These errors are not always easy to resolve, and if you're importing large volumes of data, sorting through the errors can be very time consuming. Here at Sonoma Partners, we've had situations where client import files have 50k+ records and have resulted in thousands of errors on the initial import into a test environment. Dynamics 365 offers the ability to export the errored rows, but it doesn’t include the error codes. The export only includes the rows with the data you had already included in your import, which is not very helpful. Furthermore, you cannot access the error logs through Advanced Find.
Our team set out to find a better way to tackle this situation using Power BI.
Through our efforts, we came up with the following approach to better analyze these errors and resolve them more quickly. After all, we don’t want you to start yelling, “George is getting angry!” while dealing with import errors.
Here’s the approach we took:
1. First connect to CRM by choosing Get Data > OData Feed:
2. Then choose the Import Logs and Import Files entities.
3. Next pull in the Web Service Error Codes published on MSDN, by choosing Get Data > Web:
a. Note: Power BI will recognize the table of error codes on this page, but you will need to massage the data to get the Error IDs and text into their own columns:
4. Now you can create your data model with relationships between these 3 tables:
5. With your relationships in place, you can now create a report with visualizations to categorize your errors:
- Create a slicer for the Import Date.
- Create a slicer for the File Name, in the event you have multiple files to import.
- Create a slicer for the Target Entity.
- Create a bar chart to count the errors per Field and Error Name.
- Create a bar chart to group the error by the field Value (i.e. GUID from the source system).
- Create a table to display the record(s) based on which slicers have been selected.
6. The report now allows you to easily focus on which errors need to be fixed. In this case, we can see that 2 records were responsible for 1468 and 305 errors where the lookup could not be found. By fixing these 2 values, we’re much closer to a clean data set and can move on to the next ones.
7. Once you have resolved all errors in your source files, you can now reimport with a much higher level of confidence that the job will be successful.
If you wanted to take this a step further, you could set this up to analyze your data before importing to make sure it's clean. You would need to setup your lookup tables as data sources, and update the data model with those as well. If you’d like help with these feel free to contact us, and our Power BI team would be glad to help! Either way, you can certainly do more importing and exporting than Art Vandelay ever did!