While working on a data governance strategy, the data governance team has to also outline some data quality rules which are needed to help them improve data quality. The role of any organization while implementing these rules is to achieve data quality dimensions such as accuracy, consistency, validity, completeness, timeliness, and integrity.
When customers buy our solution, we expect them to have data quality rules outlined and ready, but that is not always the case. Since we launched Data Quality Studio for Microsoft Dynamics 365 Finance & Operations (D365 F&O) a few years ago, during our training sessions we are often asked for some pointers on data quality rules and the scenarios they can be used for.
So, in this blog, we are going to answer that question and share the data quality rules that we usually demonstrate during our training sessions.
10 Data Quality Rules using Data Quality Studio for D365
The rules we demonstrate in our training are to help customers understand how they can use our solutions and then adapt the rules to their business requirements. So, it is possible that some of these rules might be relevant for you, while some need a little tweaking. For example, some of our customers use a data quality rule for customer forms, while some use it for vendor forms, and some for both. Our solutions allow you the flexibility to add rules to fields and tables wherever needed based on the errors and requirements you usually see during data entry.
A) Validation Rules
1) Make the credit rating fields mandatory
As an example, this rule could be applied for vendors or customers for record validation. Using our Data Quality Studio solution, you can make certain fields mandatory in a flexible way with certain conditions.
You can either add a warning so that the user can proceed or then block it if the field is not filled in and the form will not be saved till the data for the credit rating is entered.
2) Create a Range expression for the Credit limit
This can be done for field validation so that only set values are allowed. So, under the properties tab, you add in the values, for example, 500 to 1000 or you can enter a range such as Good, Medium, and Bad, and the system will give you an error if you enter a value outside the properties defined.
3) Leave the UPS Zone blank
This can be done if you do not want the field to be filled in. For example, on the vendor form, if anybody tries entering a value in that field, the system will throw an error.
4) Add a data pattern for email
You can add a data pattern for email to avoid typos and people entering a comma instead of a period. So, you configure the data pattern and can then run a test and if you enter email@email,com the system will process it as an error and not allow the user to proceed.
B) Duplicate Checks
1) For item number and product name
You can add duplicate checks to ensure item numbers and product names are not repeated for the exact same values for individual fields. For product names that follow a certain style, for example, laptop models, you can also add a data pattern to ensure the character spaces are entered correctly.
Currently, you can use duplicate checks for a single table, or field index table.
C) Action Rules
1) Fixed Value for a new record for warehouse
Let’s say you only have one warehouse for delivery per country for your Vendors and from that warehouse, you distribute your items to different outlets. Every time you create a new vendor record, you want the same warehouse name already filled in to avoid confusion. So, you add a fixed value so that it can be automatically populated every time a new vendor record is created for the warehouse field.
2) Fixed value for a saved record for payment method
Here we take an example to explain this action rule where the target value has changed for a saved record. Let’s say the method of payment has changed for a vendor from cash to cheque, then you can change that when you update and save the record.
3) Transformation lists for credit rating and credit limit
We recommend adding a transformation list for the credit rating and credit limit field. In a transformation list, you define the source value and target value, so that when one enters the source value for the credit rating field, the target value gets automatically populated for the credit limit field.
This way, values outside the defined ones cannot be entered in the credit rating field and the values for the credit limit fields have been fixed and will be auto-populated. If the person entering the data adds any other value outside the values defined, the system will process it as an error.
We recommend deciding on these values after consulting the finance department prior to setting this rule.
4) Number sequence for vendor group and vendor account
You can set a number sequence that will automatically populate another field based on the defined conditions. Here we can take the example of a vendor account that is populated when you add the value for the vendor group. For example, this could be a company vendor that has to use a specific number sequence based on the region NL001 for Netherlands.
5) Web services for email, address, and phone number
Our solution helps you connect to external web services. Each validation rule can be used for one field to validate that data from an external party that verifies that information for you. Examples that we commonly see are for email addresses, phone numbers, and postal address. You can also use this service to populate data. So, for example, if you add the street name, and city, the web service provider can verify the details and share information for the zip code that will be populated for you automatically. This information depends on the services provided by the web service.
Are you keen to explore our Data Quality Studio solution for D365 F&O?
The consistent feedback that we get during our training sessions is the ease of use of our data governance solutions – Data Quality Studio, MDM Studio, and Data Entry Workflow. Functional consultants that are already familiar with Dynamics 365 ERP find the solutions easy to use and navigate as our solutions are embedded in D365 and offer the same user experience.
Therefore, this is as far as we can help with the rules that we demonstrate. However, the true potential of our solutions is for our customers to further explore to secure, improve, and enrich data.
If you want to explore just our data quality solution, we recommend downloading the factsheet. However, if you are keen to explore our Master Data Management suite that includes all our data governance solutions, download the factsheet from the link below.
Sumana Samuk
Eric Van Hofwegen
