How do you implement Data Quality & Validation rules in a data warehouse?

Question

I'm developing a datawarehouse to be part of my company's enterprise application suite. So I've been learning a lot about DW concepts but the rules engine seems difficult and I can't find much information about various ways to implement. The focus of the rules is to validate data quality, and also alert when certain business metrics are reached ($xx.xx in sales for the month, for example)

Our app needs to be customizable for each client, so I would like to make the rules generic. What are some ways to implement a rules engine?

1. ready made tools? (I will be redistributing so this usually doesn't work well)
2. Frameworks/APIs
3. Design Paterns for creating our own
4. Other Ideas

Thanks.

Answer 1

It may help to look at this as having a few separate systems working together -- as opposed of one "big engine" being responsible for everything.

When it comes to "business metrics", look at KPIs (key performance indicators). Analytic engines (MS-SSAS, Pentaho-Mondrian, etc...) allow for for simple definition and presentation (dashboards) of KPIs. If developing your own, you may still get an idea of the concept.

Data quality is mostly responsibility of "operational systems", that's where data is collected. If garbage reaches DW, it's too late. Use data profiling tools to get an idea of how source data look like -- data quality has to be enforced at the source.

During the DW loading process, you can use step-by-step ECCD (Extract, Clean, Conform, Deliver) approach to implement certain "data standards". ETL tools (MS-SSIS, Pentaho-Kettle, Oracle Data Itegrator, etc..) are designed to help with this.

Regarding rule engines, look at inrule, ILOG, Fico, Corticon, Jboss Drools, etc. These are "independent systems", and can be used to enforce business rules when orchestrated with operational systems. Enforcing business rules usually leads to increased data quality. You can download Drools and start tinkering fairly quickly, other vendors allow some freebie downloads too.