No budget, No problem! - Data Quality on the cheap

While most organizations have data quality issues, not every organization has a budget for software to monitor, report and remedy data quality. However just because there is a lack of funding that doesn't mean there needs to be a lack of quality.  

With some SQL skills and creativity, a data quality program can thrive. It can even have slick dashboards that measure quality trends.

What you really need

In order to build any data quality program only one thing is absolutely necessary; a business rule that differentiates good quality data from bad.  Preferably, and inevitably, there would be many rules, but the point is still the same.  

The main components of a data quality program are business rules

Walking the walk
So now that we've talked the talk, let's walk the walk.  As I mentioned, all you really need is a rule.

Rule: Every bill must be associated with a customer

To understand the rest of this article we need to quickly go over the framework of a data quality scorecard.

1. Data Quality Dimension: Integrity is the dimension we will use in our example
2. Metric: That's the name we give to the rule
3. Rule: Our essential component
4. Total Records: In our example this would be the total number of bill records
5. Total Records Violating the rule: In our example this would be the total number of bill records not associated to a customer
6. % Violating: Violations / Total Records
7. Target: Number of violating records we are willing to accept
8. Target Description: Explanation of the target derivation
9. Count Trend: Are the number of violating records increasing or decreasing
10. % Trend: Is the percentage of violating records increasing or decreasing

Step 2. Step one was get the rule from the business.  Step two involves writing the SQL code that will give you numbers to plug into the scorecard framework above.

Total Records SQL

-------------------------------------------------------- Total BILL COUNT

SELECT COUNT(*) AS TOTAL_BILL_COUNT FROM dbo.TBL_Bill_Detail AS C

Total Records Violating Rule SQL

-------------------------------------------------------- TOTAL Bill Orphans

SELECT COUNT(*) AS TOTAL_BILL_Orphans FROM dbo.TBL_Bill_Detail AS C WHERE C.Customer_Key IS NULL

% Violating SQL

-------------------------------------------------------- ORPHANED BILL % VIOLATING

DECLARE @TOTAL_BILLING AS decimal (38,2)

SELECT @TOTAL_BILLING = COUNT(*) FROM dbo.TBL_Bill_Detail AS C

DECLARE @TOTAL_BILLING_VIOLATING AS decimal (38,2)

SELECT @TOTAL_BILLING_VIOLATING = COUNT(*) FROM dbo.TBL_Bill_Detail AS C WHERE C.Customer_Key IS NULL 

SELECT SUM(@TOTAL_BILLING_VIOLATING / @TOTAL_BILLING*100) AS ORPHAN_BILLING_PERCENTAGE_VIOLATING

Target SQL

-------------------------------------------------------- ORPHANED BILL TARGET (HARD CODED AT 10% FOR TESTING PURPOSES)

DECLARE @TARGET_PERCENT AS NUMERIC(2,2)

SELECT @TARGET_PERCENT = 0.10

SELECT COUNT(*) * @TARGET_PERCENT as TOTAL_BILL_TARGET FROM dbo.TBL_Bill_Detail AS C

Now that we have our SQL coded, let's take a look at the Excel formulas for determining the trends.  One thing to keep in mind is that trends are a comparison of the current total and percent violating against the a previous total and percent violating measurement.  In this case I have chosen a week over week measurement, or this week's execution against last week's execution.

Here is what it looks like in Excel to the user:

Here are the Excel formulas

=SUM(E15-'DQ SCORECARD 8292014'!E15)

In this example, E15 is the current week count violating the rule and 'DQ SCORECARD 8292014'!E15 is the previous week count violating the rule.

I use conditional formatting to create the traffic light icon using a rule like this:

Once I have the SQL and Excel formulas setup, I simply take the query results and plug them into the Excel spreadsheet.  

The end result looks like this ...

Interpreting the results

1. Only 0.13 % of Bills are not associated to a customer.  This provides a measure of the severity of the issue relative to the number of bills 
2. The count trend is heading in the wrong direction indicated by the red traffic light icon.  59 bills were processed this week that are not associated with a customer
3. The % trend is headed in the right direction in that more bills are being processed each week and the overall percentage of bills not associated with a customer is not growing
4. In the end, there is an issue.  It is a small one.  The issue is not growing relative to the growth in bills processed

With this structure you can quickly add metrics by writing new SQL and then leveraging the Excel formulas to expand your data quality program and scorecards.

That's how you do data quality on the cheap!

Data Management Poll Results

Here is a summary of the results from each of the polls open on the sound off page ... disagree? vote here

[gallery type="slideshow" ids="2761,2759,2760,2758,2757" orderby="rand"]

The many uses of data discovery

Bloor research defines data discovery as …

the discovery of relationships between data elements, regardless of where the data is stored.

If you expand your mind beyond the conventional relational database meaning of relationships, I agree with this definition.  Relationships in this context, or rather the context I chose to apply, means much more than a primary – foreign key relationship.

In this context relationships is defined as commonality.  This commonality can be of a data type, value pattern, or business use.  If you can profile data and understand the relationships you can set yourself up for more efficient data management practices in the areas of ETL, MDM, and application lifecycle (or application retirement).  Let’s take each of these and examine how a data discovery can increase the quality of the effort.

ETL and Data Discovery

Classical ETL takes data from a source and loads it to a target.  If you perform data discovery profiling on the sources before you build the ETL mapping you can achieve the following:

• a more accurate picture of the required data type of the attribute
  
  
  
  • By examining the profile you can determine if the assigned data type is most appropriate for the data element
  
  
  
  


• a more accurate specification for the type of transform required
  
  
  
  • If the data and metadata are not 100% coordinated you can build transforms to accommodate for this
  
  
  
  


• identification of data anomalies and outliers which require further investigation for possible remediation prior to the migration of data
  
  
  
  • this leads to a more robust error handling and exception handling process
  
  
  
  


• the identification of data, previously unknown, that meets the business requirements and needs to be migrated
  
  
  
  • discovery can lead to uncovering data that was previously undefined or unobtainable for data migrations
  
  
  
  

As discover tools mature, it may also be possible to generate ETL mappings directly from the tool.  If the target is more richly defined in the discovery tool and the sources are more accurately identified, it makes sense to me that a discovery tool can build a better ETL mapping.

This will require a tight coupling between the discovery and ETL tool, however, there are vendors in the market with this type of coupling available to them.

MDM and Data Discovery

In the same way that data discovery can aid ETL, so too can it aid the efforts of an MDM implementation.  Since MDM implementations are so dependent on ETL, the same leverage is available and can lead to a better MDM hub definition and ETL specification.

Here too can a feature to generate a data mapping be particularly useful.  With so much configuration required for match and merge rules, cutting some development form the scope of the effort would only add benefit.

Another particularly interesting feature would be the ability to generate candidate schemas for the MDM hub based on the data and metadata obtained in the profiles of the sources.

Application Lifecycle and Data Discovery

Finally, during a data discovery investigation it is possible to segment data by data ranges derived from last create / update dates.  This can be leveraged to perform application and/or data lifecycle management which would basically archive data past a certain date line or retire an application which has not be accessed in a predetermined, business driven date.

Here is another use for dynamically generated data mappings which would migrate the retired data to a target or archive destination.

Discovery is only the first step

As you can see from this quick summary, there are many uses for data discovery and as the tools mature there are many more things that can be done to leverage a discovery effort.

Your thoughts?

Too much push, not enough pull

As you might have noticed from my long absence in the blogosphere, I have been very busy managing and developing EMC Consulting's Data Quality Practice.  One thing has become very clear to me over the past few months ... data quality is an ambiguous term that means different things to different people.

I have also become aware of the fact that blogging lends itself to publishing a perspective, rather than collecting perspectives.

In an attempt to put some structure around the term data quality by collecting perspectives, I have decided to post some polls and then write about them.

I have chosen three rather basic polls in an attempt to build a baseline.  If you are reading this post, you are obviously interested in data quality (or an insomniac looking for a cure!).  Please participate in the polls and comment on this post if you feel I have missed a baseline measure that will help add clarity to data quality.

When is data quality useful?

[polldaddy poll=5684585]

When using data quality tools, what do you include?

[polldaddy poll=5684590]

Data Quality: underutilized or over-hyped?

[polldaddy poll=5684595]

Of course I have my own opinions, but I want this post, and the series of posts that follow, to be more about what others think than my own thoughts.

In an attempt to gather as many perspectives as possible, please pass this post around your network and feel free to add comments if you feel I have neglected to add something important.

The role of data quality in ETL design: DQETL

Introduction

Data integration is nothing new.  Since the concept of data warehousing, data integration has been a major initiative for most large organizations.  On the most common obstacles of integrating data into a warehouse has been the fact that assumptions about the state of the source data have been either false or flawed at best.  One of the reasons for this is that very little investigation, or data profiling, is performed on the source data prior to design and execution of the data extraction routines. 

With all there is to plan for and deliver on data warehousing projects, this oversight is understandable.  However, I believe, it is time for data quality to assume the role of reporting on and remdiating the current state of all source data to be migrated into a warehouse.

Turning assumptions into facts

If source data was profiled what was once assumptions about data can be transformed into facts about the state of the data .  Data profiling consists of scanning data and typcially delivers measures such as the frequency of nulls, the extent to which data is unique, and ranges of actual values within each fields included.  With data quality tools such as Informatica's Data Quality v9, Global ID's data profiler or Talend's data profiler, these basic reports can be compiled with a few clicks on the mouse.  Furthermore, this products offer portals where dashboards detailing the current state of the data can be delivered to both a technical and business oriented audience.

Data Profiling 101

As I mentioned, creating a data profile can be done with a few mouse clicks.  Typically the steps are as follows:

1. Define a connection to the data source


2. Define the data source


3. Define which fields are to be included in the profile


4. Define any business related rules to be included in the profile


5. Schedule the profile for execution

Connections

Defining the connection to the data source usually involves a few simple steps.  Connections are typcially either to a database or to a file.  While connecting to a file includes parameters such as delimiters, field names, and data types and lengths, connecting to a database usually involves location parameters like host and authetication credentials like username and password.

Whether you connect directly to the database or use a flat file extract typically depends on circumstances like resources for a test environment and ability to procure the required credentials.  Either way, the lion's share of the work is setting up the connection.

Candidates

The next logical step in creating a data profile is to define what fields to include.  Within the context of validating ETL for data integration, this step would heavily depend on those fields nicluded inthe migration, and even more by those fields requiring transformation. 

Bercause not all data is migrated, data profiling is best of limited to the tables that are required.  Primary and foreign keys are almost always included to ensure uniqueness as well as timestamp fields to ensure completeness and date format conformity.

Rules

When we talk about data profiling most of the metrics are technical (percedntage of uniqueness, percentage of nulls, etc ...), however, once fo the most beneficial practices is to include rules in the data profile that are based on business rules.  Some examples of these rules are to validate that certain chronological events are in order (i.e. ship date does not proceed order date) and logical assumptions (i.e. that individuals who are indicated as male do not have postive pregnancy outcomes) are indeed valid.

Constructing business rules often involve participation of a business domain subject matter expert, however they can also be formed from the conceptual deisng of the ETL.  By reverse engineering the transformation logic, it is possible to derive, at least, one rule that needs to be tested.  Afterall, transformation logic would be negatively affected by things such as nulls, data type nonconformity and values outside the expceted range.

The upside of yet another step

Because of its complex nature and multi-step requirements, adding another step to data integration and migration is rarely a welcomed effort.  However, once viewed within the context of reducing ETL redesign and increasing transfer success rates, it is frequently, albeit begrudgingly, accepted.

Including data profiling in the data maigration suite of perations, indeed, can deliver these desired outcomes.  When data is profiled prior to ETL design and execution, data states that would otherwise cause ETL loads to fail can be identified and remedied.  An example can be found in the all important date related fields.  A simple data profile can detect date formats that are not supported by the target repository.  Dates in data warehousing are crucial to track transaction lineage and if not configured correctly can be the undoing of an ETL design.

Summary

While I have just touched the surface of the role of data profiling in data integration this is, none the less, an important concept to adopt.  For increases in successful loads and decreases in ETL test and troubleshooting will save time and resources and paint a more positive image of the data integration team and their capabilities.

The Seven Habits of Highly Effective Data Quality

7 Habits of Highly Effective Data Quality

I've been reading Stephen Covey's The 7 Habits of Highly Effective People and I couldn't help but notice the parallels between effective people and effective data management.  In the book Covey discloses that there are principles, centered on self-discipline, that lead to success and fulfillment.  Sounds great, right?

The seven habits include some ear-cringing buzz words, but let's take a look at them and their data quality doppelgänger.

Be Proactive

For years data quality has been a discipline striving to transform itself from reactive to proactive. In fact, the ROI in data quality programs centers on being more proactive to avoid regulatory issues and costs and improving decision making.  It's an understatement to say that data quality programs need to be focused on taking the initiative and become proactive programs of change.

Proactive data quality means identifying and remediating data quality issues before they become proliferated throughout the enterprise.  Simply put, proactive data quality is about having identification and remediation processes at data entry points and addressing issues at the source.

Begin with the end in mind

Beginning with the end in mind brings a smile to my face.  This was practically the title of one of my first posts for this blog.  Without knowing where you need to end, your route to that end will almost inevitably be scattered and twisted.  For it is only by setting a clear destination that a clear path can be developed.  Often, in the world of data quality, setting a destination focuses on developing metrics and targets that will bring about positive change in the organization.

Put first things first

Putting first things first is about setting priorities and building a course of action(s) that will address the prioritized list of objectives.  In others words, don't focus on everything all at once but rather break down large tasks into smaller more achievable parts.  This is often useful when developing and implementing data quality programs because there are so many moving parts that need to be put in place simultaneously.

Think Win-Win

Win-wins in the data management / data quality arena are all about implementing rules that help multiple business units improve their data and its use.  There are some easy domains where one data quality service equates to a win-win.

Address validation is a prime example of the win-win scenario.  Every business unit benefits from more accurate customer addresses.  Implementing address validation processes can be orchestrated in such a way that the process can accept different address sources and implement the same validation routines.  Not only is this a win-win, it also cost effective and generates a high rate of return on investment.

Seek First to Understand, Then to Be Understood

This one is pretty straight forward.  Data quality / data management is all about solving problems and building effective change.  You can’t be affective at solving a problem without first knowing what it is.  A more subtle point I’d like to make here is that all too often there is a tendency in the technology field to explain the intricacies of the solution.  Frankly, business people don’t care how you solve the issue just that you do solve it accurately.  Only understanding issue ensures that you can do this.

Synergize

Cringe!  Worst buzzword ever?  Maybe.  In essence synergy means bringing together a whole that is greater than a sum of its parts.  As described in the win-win section, synergies in data management / data quality are largely derived from building a solution that works for multiple business units in such a way that they produce a benefit greater than if the solution was only built for one unit.

That said, building a solution that “chains” several beneficial processes together like address validation and duplicate reduction can also be thought of as a way of bringing together a whole greater than the sum of its parts.

Sharpen the Saw

My personal favorite!  Sharpening the saw has to do with the continuous process of developing skills.  In part due to the wide range of data quality modules, there is always a need to sharpen the saw.  For example, I am currently working on expanding my ability to produce more accurate matching techniques so I can be sure that I identify true duplicates and produce the minimal amount of false positives.  In addition, I am always searching for more knowledge on address validation techniques.

Sharpening the saw with regard to data quality processes is a way to revisit the existing solution and make it better.  This is an essential practice due to the growing number and varied nature of data sources continuously added to the enterprise landscape.

Conclusion

Effective people and effective projects and strategies can learn a lot from Covey’s 7 habits research.  I encourage those of you reading this post to try and implement these habits not only in yourself but also in your projects!

Data Quality: underutilized or over-hyped?

Poll Results

Conclusion

When using data quality tools, what do you include?

Poll Results

Conclusion

When is data quality useful?

Poll results

Conclusion