Data Quality Tools for Data Stewards from Melissa

Problem

Our organization receives and transmits data on a daily basis for business-critical processes to numerous partners, vendors, customers, suppliers and more.  Our data needs to be validated and cleansed prior to loading the data to our Microsoft SQL Server environments.  We have made significant investments with our SQL Server DBAs and Developers to automate the processes as much as possible. Unfortunately, we are constantly shooting at a moving target i.e. file formats differ between each system, we often get unexpected file formats (data is added, removed or re-ordered), new data is being requested between the organizations and more unpredictable circumstances.  On top of this, we need to ensure all of the data is properly vetted by the Data Stewards intimately knowledgeable on the data.

The constant data management moving target tends to overwhelm the SQL Server DBAs and Developers with the need for immediate response of updating the validation and import code to then load the data to meet the business requirements between the organizations.  How can we have the best of both worlds?  Have our SQL Server DBAs and Developers build a system to support the organization, while providing our Data Stewards the flexibility to lookup, validate and cleanse the data prior to the production load without having the deep programming knowledge of our technical team.

Solution

Data Quality Suite

With SQL Server Data Platform, most DBAs and Developers turn to either SQL Server Integration Services (SSIS), Azure Data Factory (ADF), BIML, API, XML, JSON or T-SQL code for data integration and data governance needs.  With standardized files, formats and data, these technologies are able to automate tedious processes common in data management solutions.  The SQL Server technologists are able to shine and show their value in terms of time savings for the entire organization.

Unfortunately, many processes are difficult to automate due daily changes between systems perhaps due to constantly changing marketing programs, systems updates, short term programs, shifting product lines, fulfillment via numerous vendors and more.  From a technology perspective, this can be considered an SSIS Nightmare with hundreds of unwieldy packages, version control debacle and countless hours trying to automate processes that are rapidly evolving.  These situations quickly become frustrating for the SQL Server technologists and can benefit from the collaboration with Application Owners, Data Stewards and Power Users, who can decipher the data and interface with the other organizations to work through the process.

Introducing Unison from Melissa

So how do we avoid the SSIS Nightmare?  Let the technologists build an automation solution that standardizes processes and gives the Data Stewards a solution for cleansing, validation, enrichment, standardization and data integration without becoming programmers?  One data quality software solution, that I would like to introduce is Unison from Melissa Data, a solution built for Data Stewards enabling them to validate, lookup, cleanse, import and export data via an intuitive project-oriented framework.  Unison data quality solutions include:

• Intuitive three step ETL process to perform data cleansing workflows
• Simple point and click interface to profile, cleanse, standardize, enrich, match, merge and validate data in real-time
• Highly refined data quality algorithms to ensure your data is accurate with monthly meta data updates
• Address verification for 240 countries, USPS certified mailings, Name Parsing and more enhancements
• Import and export data from major databases (SQL Server, Oracle and MySQL), Excel and flat files as data sources
• Intuitive web-based interface without programming expertise
• Collaboration among business users to ensure critical business processes are met
• Project oriented paradigm for re-use, modification and recurring scheduling
• Fast data processing – 50 million address records per hour
• Architecture designed for on-premises deployments of container technology to meet internal security and performance requirements

Let’s dive in to see Unison data quality suite in action.

Getting Started with Unison for Data Quality Management

To get started with cleansing and validating your data with Unison from Melissa Data, login to the dashboard to have access to Projects, Jobs and administrative interfaces such as logs, user administration, meta data updates and more.  Start a new project by clicking on the Projects link in the left navigation and select the New option.  This begins the process by naming the project then specifying whether the project will be public or private.

Once the project is named, three core steps are followed to complete the project:

1. Define the Input Source as either a text file or database connection as your data set
2. Configure the data validation tasks
3. Specify Output Source to either a text file or database connection

Both the Input and Output Sources can be a flat file or database connection to SQL Server, Oracle or MySQL.  Below is the interface to create a database connection in your project.

In this project, we will drag and drop a text file from Windows Explorer into Unison and Unison will read the file header as well as preview the first three records as shown below.  Unison includes intelligent file detection, but if needed the text file properties can be configured in terms of the delimiter, field enclosure such as double quotes or if the file is a fixed width.

Once configured, the Project looks like this:

Unison Data Validation Tasks

The true value of Unison is apparent with the data validation tasks for contact data.  There are currently four tasks available for customer data including Name Parsing, Phone, Email and Address Cleansing which includes 240 countries.  Coming soon will be Matching, Data Profiler and Data Cleansing that are currently supported in complimentary Melissa solutions.  For Unison, simply click on any task to include it in the project.  Let’s start with Address Cleansing for our project.

Address Validation with Unison

For Address Cleansing, the Task will match the input files to common address fields in Unison.  The process starts with identifying the Input Fields then the Output Fields which will be ultimately used in the final step of the process.  The Output Fields are the validated Melissa results to ensure the final data is accurate. Once we review and save the configuration, we will return to the main project design window.

Name Parsing with Unison

Our next step is to perform Naming Parsing with Unison that follows a similar paradigm as the Address Task.  This includes input and output fields with validation as well as separation of each name segment.

When we return back to the project, the current status with the Input and Tasks show completed.  Our final configuration step is to define the output.

Unison Outputs

Just like with the Unison Inputs, the Outputs can be both to a File or Table in SQL Server, Oracle or MySQL.  The Write to File option includes the name, extension, enclosure and delimiter.  Further, compression options include Zip or GZip formats.  Another unique feature is the ability to filter the file output based on Full address matches, full phone number matches, etc.

As a final step prior to outputting the data, Unison provides the ability to review the Output Fields, turn columns on / off with the slider and rearrange the column order.  The legend on the right specifies the color coding associated with the Output Fields.

Final Project Review

After configuring the project, there are three options on the top right of the interface including:

1. Run Now
2. Preview Output
3. Schedule

Preview Output

From the Preview Output option, review all columns and the first set of rows to ensure the data is delivered in the needed format.  If not, you can update the project to your requirements.

Run Now in Unison

If the data is correct based on the Preview Output, then Run the job, review the status and download the final output file the next steps in your business processes.  Whether that means copying the file to a directory for automated loading, encrypting the file then sharing with team members or partners, etc.

If you click on the View Reports button, you are able to see the results from both the Name and Address Parsing in either a graphical or tabular format for the types of changes made as well as errors recorded.

Unison Scheduling

The final option is to schedule the job. Options are either daily, weekly, monthly or a customized time frame. Below is the intuitive interface to schedule a job daily.

Unison also allows the opportunity to update and review the Jobs to meet daily management and monitoring needs.

How do I get started with Unison?

1. Think about the numerous hours and relive the frustration of meeting critical deadlines. This is typical for what business and technology groups face. It is difficult to cleanse, validate, dedupe and load data among various systems in order to achieve high-quality data.
2. If you are experiencing SSIS Nightmares with constantly changing requirements, code and too many SSIS Packages to count, then consider Unison. Unison is a technology for data enrichment that you can integrate into your processes. This enables your Power Users to validate the data. While your SQL Server Professionals to manage the data in production environments.
3. Get a personal demo of Unison from a member of the Melissa team. Then put Unison through its paces in your environment with your data. See the ease of use firsthand.  See how easy it is to validate your data and streamline tedious business processes that have not included data validation.
4. Communicate how Unison can help your organization.
   1. Time savings for data quality issues, data validation, data enrichment, data matching, data migration, deduplication and data cleansing.
   2. Put the power of data quality and data analysis in the hands of your data stewards.
   3. No programming experience necessary, only knowledge of the company data and business rules.
   4. Reduce the burden of your SQL Server Professionals to focus on production data.
   5. Introduce data quality to new processes to improve the value of your data, customer experience, decision making, data warehouse projects and business intelligence.
5. After working with Unison, measure the tangible and intangible benefits and determine your next steps. Start to radically streamline business processes and put the power of data quality in the hands of your power users.

Next Steps

• Learn about all of the Unison functionality.
• Get a personal demo of Unison.
• Download Unison.
• Check out the Melissa Docs for Unison.
• Check out this Unison video.

MSSQLTips.com Product Spotlight sponsored by Melissa, makers of Unison.

Jeremy Kadlec

Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years.  He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.