Build your own Data quality framework part 3/3

And here we are , at the final part of this tutorial. This third part is going to be mainly about the Data quality issue fixing and possible reporting layers. I have been working with this framework in an environment, where we’ve had SSRS reports embedded in a .NET web application. The links in the SSRS subreports for each Data quality rule would navigate you onto the specific business objects in the application and there you would fix the errors. That’s the part where I find this solution really straightforward and powerful. If you’re in a completely different setup environment, you can possibly write your own simple web page for the error fixing module or you can come up with any other possible UI you can think of. The same with the reporting module. I have used a simple SSAS Tabular model with Excel sheets pushed onto the internal Sharepoint site, but you can really go in any possible direction you can think of.

So once you have the DQ schema tables filled with rules and their results, you can easily create an SSRS report dashboard  ( dataset joining dql.ValidationResult and dql.ValidationRun tables ) looking something like this for example:

Untitled Diagram (4)

The datasets for this report are a piece of cake to come up with when you think about the data model we have from the second part of this tutorial. You can use for example:


DECLARE @CurrValidationRun_ID INT;
SELECT @CurrValidationRun_ID = ISNULL(MAX(ValidationRun_ID),0) FROM dql.ValidationRun WHERE ValidationRunEnd IS NOT NULL;
DECLARE @PrevValidationRun_ID INT = @CurrValidationRun_ID - 1;

SELECT
VR.ValidationRule_ID,
VR.ValidationRuleName,
(SELECT VRP.ValidationErrorCount FROM dql.ValidationResult VRP WHERE VRP.ValidationRun_ID = @PrevValidationRun_ID AND VRP.ValidationRule_ID = VR.ValidationRule_ID) AS PrevErrorCount,
(SELECT VRP.ValidationErrorCount FROM dql.ValidationResult VRC WHERE VRC.ValidationRun_ID = @CurrValidationRun_ID AND VRC.ValidationRule_ID = VR.ValidationRule_ID) AS CurrentErrorCount
FROM dql.ValidationRule VR;

or you can use CROSS APPLY or what ever technique you are the most comfortable with. The Data quality rule column in the table should be a link to a subreport containing the specific rule errors with links to the CRM application business objects.

When it comes to the drill down into the subreports, you need to query the dql.ValidationResultItem table but you are facing one issue here. As you’ve seen, I have used 6 ResultCol columns for the results and in the Contact with multiple invoicing addresses example you have 6 columns filled with data. But what if you had used less than 6 columns in some rules? So how can you prepare the report column layout in the specific rule results subreport? The same problem is with the SSRS matrix / table column headers. I leave this part up to your imagination.

You can go with a simplistic approach, show all 6 columns in the table report layout and have the column visibility and column header value based on some Data quality rule – specific configuration and solve this issue with an lookup expression. You could also choose a more technical approach and build dynamic SQL statements using the Data quality query metadata and prepare a dataset for a matrix style report with some pivoting / unpivoting. I would not go into more details here as everyone is working in an different environment and this is really the fun part where you can spend some time evaluating the best available options. I leave the reporting for Management up to you as well. It is really the fun part where sky is the limit and the solution is totally based on your preferences.

Conclusion:

This lightweight Data quality framework can be considered pretty easy to build yet very powerful. You can further expand the functionality in many ways. You can even make this framework call available Datasets used for example for addresses cleaning and have some auto corrections being done. You should not forget about row-level security , so the users cannot touch each others owned business entities. But the main thought behind this is to provide the users an easy to use interface through which they can easily fix the Data quality issues that they are responsible for. Just remember to keep it simple and well performing!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s