Top 5 Tips to Manage Your Data Problems in Dynamics 365

Data management is not the sexy topic that you want to discuss in the boardroom or executive meetings, but it is what matters most to your business. The quality of your data will help your organization improve its operations and understanding of its business. This is why you must have strong data management strategies in place that keep your data clean and useful. Clean data is critical to well-functioning sales and marketing processes that improve revenue and communication with customers.

So how do you get started with data management in Dynamics 365? Actually, it offers many out-of-the-box data management tools that will satisfy most of your needs. This article will discuss how to use these built-in tools including data exports, data imports, duplicate data detection, bulk deletion jobs and audit history.

For those that are more comfortable slicing and dicing data in Excel, this is the preferred option. To start, access the Account view and select the Active Accounts. Then choose one of the following:

Option 1: Export to Excel Worksheet Online – if you want to make simple modifications quickly, use Excel online to easily change data and update in minutes.

Option 2: Export to Static Excel Worksheet – if you need to make many changes or modifications while you slice and dice the exported data for further analysis, this is your best option.

Option 3: Export data to Dynamic Excel Worksheet – if you would like to refresh data whenever the Excel file opens from the latest data in your dynamic worksheet, this is your best option. Note that you must have Dynamics 365 installed to compare and refresh data between versions.

You can export one particular record into an out-of-the-box Word Template that will auto-complete invoices, proposals, or other documents and automatically feed Dynamics 365 CRM data directly to the database. This is a good option for busy sales and service teams to keep data current without requiring employees to input it into multiple locations. Having data automatically export and import to and from templated Word and Excel documents keeps data management a simple part of daily work life.

There are a few other built-in and third-party tools to help you with data export from Dynamics 365 depending upon your needs.

1. SDK

You may find SDK beneficial to leverage if you need to transform or parse data. You can build an application in Windows, Command Prompt, or .NET with the goal of building this application into a text or csv file.

2. CRM API

This is the easiest and best way to export loads of data.  The best tool for this today is CRM REST Builder and build a query using the REST API or the new web API.  You can download it for free and install it as a web solution.

3. JSON to CSV

This web converter provides the easiest transformation and export of data from the Dynamics environment into a CSV file.

4. Scribe Insight

This is a larger application that allows for integration with multiple platforms. It is more complicated to configure and setup, so it is not suitable for everyone and more costly than export/import alternatives (e.g. KingswaySoft). It is built on proprietary software so it cannot be extended and is best for continuous integration and migration of data.

5. KingswaySoft

This application is best for exporting large amounts of data. It is easy to use and extend as it is built on .NET. All you need is Visual Studio to set up the connector and you can build a solution inside your studio to modify the data. As a bonus, there is a free tool to download so you can try it in your environment and test for fit first. This tool is developer friendly because it is built on top of an existing platform.

Obviously, in most cases leveraging the out-of-the-box data export and data management tools in Dynamics 365 will be more than enough for most organizations, but if you want to modify the data you may want to build yourself a plug-in to make things faster and easier.

Data imports to Dynamics 365 leverage many of the same tactics, tools, and wizards as exporting, such as importing from Excel Worksheets, Word Templates, Excel Templates, etc.

As you know, Contacts to Accounts has a one-to-many relationship in Dynamics 365. The good news is that Dynamics 365 will automatically determine the order in which the data should be imported. It will prompt you to map those fields that vary from the entity names in Dynamics 365, but will automatically map those that match exactly.

You can also download a template for data import to match the entity named in your Dynamics 365 to ensure you have the same fields completed as included in your database. You can also use the SDK the same way you do for data exports in Dynamics 365.

Many of the Dynamics 365 data export tools also work for import, such as SDK, CRM API, Scribe Insight, KingswaySoft, etc. Again, not all of these tools are applicable to all organizations. It is best to pick the tool that completes the project as quickly and easily as possible.

The latest Dynamics 365 has a built-in duplicate detection functionality. It may not fulfill all organizational needs but will be efficient in minimizing data duplication in your database.

The standard duplicate data detection rules in Dynamics 365 can easily be turned on or off. Additionally, you can also create your own duplicate data detection rules in the platform to further enhance or loosen the duplicate detection sophistication or functionality.  You may want to further enhance detection to account for mistyping in forms by employees or customers to avoid further duplication by customizing a prompt for similar data detected.

When a duplication is detected, Dynamics 365 will prompt you to choose how to proceed with the record. You may: (1) merge the record(s); (2) overwrite the record(s); or (3) do nothing (keep the record(s) as is).

Upon data import to Dynamics 365 you can select to run the duplicate data detection, deciding whether you want to add duplicates or not. If you run your duplicate data detection rules and the data matches the rule(s) criteria, it won’t import—resulting in a data import error.

As a best practice when you are setting duplicate data detection jobs, you will want to evaluate how to use, measure, and sort your data to define and refine these duplicate data detection rules for your environment. Working with a certified Microsoft Dynamics Cloud Solution Provider, like Catapult, can ensure your duplicate data detection rules fit your environment and are set up correctly to preserve your data gold.

Once you have your duplicate data detection rules defined you will want to setup and run jobs in Dynamics 365 to detect duplicates. Then you will want to regularly schedule the duplicate detection jobs to run nightly or weekly to minimize duplicates in your data. You can set this up once you have one duplicate detection job active in Dynamics 365.

Some customer have asked if there is a way to segment or segregate data that has already been scrubbed for duplicates. To ensure no duplicates are missed each time data is added to your database, each duplicate data detection rule runs against the entire database.

Storing scratch data or test data that you are no longer using to trigger something can be an anchor. To bulk data delete these records in Dynamics 365 is simple with a built-in tool and process. Basically, when data is no longer needed and you want to reduce the cost of data or cleanse your data, you can set up a bulk data deletion job.

Bulk record and data deletion works in the same way the duplicate data detection rules do in Dynamics 365.  You will create a rule around the defined parameters to detect the unused data. Then you can schedule bulk deletion jobs to run regularly.

Audit history is an important element to cleanse data in Dynamics 365. Things that are auditable in Dynamics 365 (Dynamics CRM Online) are create, update, delete, and access. Things that are non-auditable are read operations, metadata changes, and notes or attachments.

The Audit History View will show the access, shared privileges, security roles, association, and disassociation of records, as well as deletion of audit logs.

For auditing history to work you will need to enable audit history on a Global level, Entity level and Field level.

The best practice is to keep auditing for important fields and operations only and to create a schedule to regularly monitor and review audit logs.

Many organizations setup their new CRM / Dynamics 365 platform and forget about the ongoing maintenance and operations of keeping the platform and data valuable to your business, not to mention enhance its worth over time. Data management is just as, if not more, important than the initial planning and setup of your customer relationship management platform.

Some organizations think once the data is cleansed, it never needs to be touched again. That is a critical error. Data management is an ongoing process and strategy. Putting best practices, schedules, and automated jobs in place to help you with your data management in Dynamics 365 helps keep you on track.

The basic tenets of strong data management in Dynamics 365 follow the VACUUM approach for data:

V – Validity

A – Accuracy

C – Consistency

U – Uniformity

U – Unity

M – Maintenance