31 October 2022

How to Set up Validation Rules Using Data Quality Studio for D365?

Set up Validation Rules Using Data Quality Studio for D365

While it is important to avoid data quality mistakes such as user error or lack of guidance, you need to outline a data governance strategy and initiate data governance processes that help you avoid these mistakes in D365. Once you start looking for a solution that fits into your data governance strategy, it is time to look for a solution that is both functional and flexible.

At To-Increase, we understand your data quality challenges, so we have built solutions for Microsoft Dynamics ERP to simplify your day-to-day tasks and solve these challenges. We have built a suite of solutions that help with improving data governance. Our MDM suite of solutions are embedded within Dynamics 365 ERP for Finance & Operations/ Supply Chain Management (F&O/SCM), including a data quality solution. We understand the importance of a functional and flexible tool that helps you achieve your data quality goals.

Our prospects have often communicated that they would like to understand how the solution really works before scheduling a demo or speaking to an expert. While you might have looked at our Data Quality Studio for Microsoft Dynamics 365 product page and probably have even downloaded the factsheet, this blog will help you understand how easy it is to add a validation rule using our solution.

In this blog, we will cover the following features of Data Quality Studio for D365:

The Data Quality Studio dashboard for D365

Since the solution is built within the D365 environment, as you use the tool, you will realize that there is a short learning curve since the user experience is consistent with the ERP.

Once you login into the standard Dynamics environment, you will see a tab on the left where you will find a link to the Data Quality Studio solution after it has been installed and implemented for your organization. Once you click on it, you will see all the modules available under Data Quality Studio. You can track the number of policies, the number of data patterns, duplicate checks, draft versions, and queries you have created in this dashboard as seen below.

Data Quality Studio - Policy

How do we set up a data pattern using Data Quality Studio?

Next let’s look at an example of setting up a data pattern for email. Data patterns can be used to ensure consistent and complete data quality in the system.

The idea is to create a data pattern and related rules that need to be adhered to, for example, when entering an email address to avoid errors. An e-mail address has to have the ‘@’ sign and a dot followed by the URL such as .com. So, using our solution, you enter the description, the pattern with the rules associated with it. After that, you can add a message that serves as a guideline for data entry.

Next, you can test it. We tried adding email@email,com and tested it and got a failed test response.

Then we added email@email.com and the test response was a pass.

Data Quality Studio - Data patterns

How do we set up duplicate checks?

Using duplicate checks, you can gauge if a value is already within the table.

Let’s say that on the vendor screen there are different tables, and you want to see when you create a new vendor if there are duplicate field values already or existing values are populated. So, for example, let’s take the customer relations field and external number. If these have values of 001 and 001. So, when you create a new vendor and try to use these numbers then you will see an error message. And you need to select the table name and select the status to active.

How do you add a transformation list?

The transformation list feature helps you add values for the source field and target value to activate automatic data population using this list. So, when using Data Quality Studio, you have action rules, and these rules help in enriching data, for example, when the data for the source field value is entered, the target value automatically gets populated.

Now let’s look at an example of Credit Rating and Credit Limit fields that are linked to each other. So, if you give a Credit Rating of Good with a target value of 1500, Medium for 1000, and Bad for 500; when you enter Good, Medium or Bad, the values associated with the Credit Rating will automatically get populated.

Data Quality Studio - Transformation list

How do you set up a data policy using Data Quality Studio?

You can set up a policy ID or number value which can be letters or numbers and a value for the version, let's say in this example the policy and version are 01.

Adding a validation rule

Let's say that you want to have a certain field as mandatory while creating a new vendor. So, before you can set that up, you need to find the correct values corresponding to the field you want mandatory. You go to the vendor page and then right-mouse-click on the form name for the data source to identify the table behind the data field.

You add the right table, field, select the validation type as mandatory (other options are blank, range expression, data pattern, web service, custom), select record validation in the field type (another option is field validation), and an error message (you can also set up a warning if you want to be more flexible).

Next under messages is an open text box, you can add a guideline for the user to understand the format that is valid for the field. You can set up an error message to steer them in a certain direction. You can also enter a condition and select Inquiry or Dynamic query (if you want to create a condition that is not in the standard Dynamics platform). You can customize it for the Warehouse field and select criteria for the same. Ensure to activate the policy after you complete this step.

Data Quality Studio - Validation rule

Adding a duplicate check

Remember the duplicate check feature we explained earlier? You need to add the Duplicate check name, the table name, and the corresponding field that you want the solution to scan this for.

Data Quality Studio - Duplicate check

Once that is done, you can add it to the data quality policy by clicking on the Duplicate check tab and save this action.

Adding an action rule

Next, you can click on the action rule tab and enter in the table – we selected vendors. In the event table, you can select from source field modified, new record, and save record. Since we wanted the target value to be populated based on the corresponding value selected, we selected source field modified in the events dropdown. Next, we selected transformation list under the action type table that we had already set up (as explained in the section titled How do you set up a Transformation List?). In the source field and target field, we added credit rating and credit limit respectively. Ensure that you select the Transformation ID on the right-hand side (as created earlier) and then click on the save button.

Data Quality Studio - Action rule

Assign the Organization

Under the organization assignment tab, you can assign this policy rule to all organizations or select specific ones from the list of legal entities or subsidiaries you have in your system. Once you save this, you can activate the data policy from the maintain tab on the top left.

Testing out the Data Quality Policy

You start to create a new vendor and fill in the mandatory details and then move to the credit rating and enter anything besides ‘Good, Medium, Bad’ and you will see an error message. And as soon as you enter one of the three you should see the corresponding value you had entered in the system for them while setting up a Transformation List. If you see the error message and the values, then that means the data policy has been successfully activated.

Data Quality Studio - Testing the new policy

Next you can check for the Duplicate check by selecting a value for the purchase pool. If the system gives you an error for an existing value, then you know that the Duplicate check rule is working. Ideally, it is better to do this for a combination of fields so that you have clean and consistent data for all your vendors.

Are you keen to explore data governance solutions for your organization?

If you are looking for a comprehensive Data Governance suite with solutions to manage your application ecosystem, streamline data entry processes, and manage and distribute master data then we recommend downloading our data governance factsheet to better understand our offering.

If you are using Dynamics 365 for your organization and looking for a user-friendly, configuration-based, stand-alone solution to improve just data quality, then Data Quality Studio would be a good fit for your organization. Our Data Quality cost blog should help you understand the costs involved including licenses and training and that would give you an idea of whether this solution is a good fit for your organization or not.

However, before you decide to implement a data quality management solution, do consider a list of rules after consulting with teams across business units to speed up implementation and improve your data quality considerably. While our experts can train your technical and business users, they will not have a view of the data quality challenges your team faces and the priorities for your organization.

Once you are ready to dive further, go ahead and schedule a demo with our experts and begin your journey to transforming your data quality across your organization.

Book a slot for a demo with our experts today!

Kevin Rahan
Kevin Rahan,
Kevin Rahan,
Business Consultant

Also interesting