Query Control¶

How to create quality checks and manage the exceptions they generate.

In this module: add and format queries · create notifications and add properties · finalize and schedule tests · compare and map data · preview, validate and look up values

Create a query control¶

1. Open Data Quality in Solution Explorer.
2. Right-click Tests → New → Query.
3. Enter the name Customer Missing Name.
4. Select Training Business Central from the Data Provider drop-down.
5. Enter the query below.
6. Click the save icon to save the query.

SELECT No_, Name, Address, City, [Territory Code]
FROM [training].[Cronus Canada, Inc_$Customer]
WHERE [Address] IS NULL OR [Address] = ' '

Column formatting¶

1. Open the quality control query you created under Tests in Solution Explorer.
2. Run the saved query.
3. In the Properties explorer on the right, select Edit — Column Formatting → Edit.
4. Select the No_ column and edit its caption to Customer No..
5. Click Save.

Create a notification¶

1. Go to Exception Manager.
2. In the first tab (Common), enable the Exception Manager.
3. Make sure you are the query owner.
4. Add a description and action.
5. Go to the Notification tab, click Add Notification and choose Email.
6. Send exception emails on New and Open.
7. Go to the Primary Key tab, choose Custom and check No_.
8. Click outside the window to close it, and save.

Testing the notification¶

1. Click the Run button.
2. Make sure the UAT is set to Development.
3. Make sure the Mute all emails checkbox is unchecked.
4. In the select box behind Email, choose your own name.
5. Click Run.
6. Check your own inbox to see whether you get an email with all missing addresses.

Add additional properties¶

1. Go to Exception Manager.
2. Open the first tab (Common).
3. In Description and Action, describe what the test monitors and its business impact — and what the exception owner is supposed to do to resolve the issue.
4. Enter the description "If a customer address is missing, the invoice process will fail".
5. Enter the action "Please update the customer card in Business Central".

Finalize the test¶

1. Make sure the query Customer Missing Address is still open.
2. Click the Deployment button in the toolbar.
3. Ensure Development to Production is selected, if Deployment Mode is an option.
4. Click Deploy.

Related reference¶

• Data Providers — the connection this check queries through.
• What is Data Quality — the three control types explained.