Introducing Data Quality Servicesand its role in an Enterprise Information Management (EIM) Process

James BeresfordGroup Manager, Avanade

DBI217

Who am I?… and what am I doing here?

Over a decade of MS BI & DW ExperienceBlogger @ www.bimonkey.comTweeter @BI_Monkey

Introducing Data Quality Servicesand its role in an Enterprise Information Management (EIM) ProcessGoals of session:

• Introduce DQS• Concepts• Terminology

• Showcase• Automation• Integration into an EIM process

Data Quality Servicesand its role in an Enterprise Information Management (EIM) Process

Outcomes for you:

• Manage data quality• Automate data cleansing• Support Master Data Management

The Data Quality Problem

!=

Hands up if you’ve been 2 years old

Your first data quality problem

Data Quality = Shape Sorting

There is Good Data

There is Bad Data

There is Repairable Data

Data Quality Services TermsCollection of Shapes = Knowledge Base

Data Quality Services TermsA Shape = Domain

Data Quality Services TermsShape Sorting = Domain Rule

The Data Quality Client

Allows you to:

• Create or Maintain• Knowledge Bases• Data Quality Projects

demo

Create a Knowledge Base using Knowledge Discovery

The Data Quality ClientCreate a Knowledge Base

What did we see?

• Create a Knowledge Base from Data using Knowledge Discovery

• Set values as Correct, Error or Invalid• Teach the KB some simple auto correction using Domain

Rules

The Data Quality ClientCreate a Knowledge Base

What can we do?

• Keep our knowledge about data quality in one location• Accept, Correct or Reject values• Update it as an ongoing process

The Data Quality ClientPop Quiz: Who owns a Knowledge Base?

Maintaining KB’s is done by:

a) Qualified Data Professionalsb) A BI Developerc) Usersd) Microsoft

The Data Quality ClientPop Quiz: Who owns a Knowledge Base?

Maintaining KB’s is done by:

a) Qualified Data Professionalsb) A BI Developerc) Usersd) Microsoft

The Data Quality ClientCreate a Composite Domain

+ n

demo

Create a Composite Domainwith a Domain Rule

The Data Quality ClientComposite Domains

What did we see?

• Use values from one domain to interact with another

The Data Quality ClientComposite Domains

What can we do?

• Manage interdependent data fields for quality purposes

The Data Quality ClientOther Domain Functions

Things that I won’t demonstrate

• Term Based Relations – autocorrect substrings (e.g. Inc. > Incorporated)

• Reference Data – Validation against external sources on Azure DataMarket, e.g. Melissa Data

The Data Quality ClientData Quality Projects

Application of a Knowledge Base through the DQS Client

• Interactively process data• Output results

demo

Create a Data Quality Project

The Data Quality ClientCreate a Data Quality Project

What did we see?

• Interactive Cleansing of data• Export of cleansed data set

The Data Quality ClientCreate a Data Quality Project

What can we do?

• Clean Data• Export results

The Data Quality ClientOther DQS Features

Things that I won’t demonstrate

• Matching• De-Duplication

Automation with SSIS

Automation with SSISThe DQS Cleansing Task

demo

Automation with SSIS and the DQS Cleansing Task

What did we see?

• Automatic Cleansing of data• Managing different results

Automation with SSISThe DQS Cleansing Task

What can we do?

• Clean data in an integrated manner• User input to data quality can affect DW• Updating DQ is independent of updating ETL

Automation with SSISThe DQS Cleansing Task

Data Quality Services

Master Data Services

Integration Services

EIM: Credible, Consistent Data

Master Data Services (MDS)

MDS Provides

• A central store of reliable data• Web and Excel UI’s• Easy access to Master Data

Loading MDSCreating our Entity

Updating MDSHandling new, trusted data

Feeding from MDSPublishing with Subscription Views

?

demo

Integrating DQS processes with MDS processes

DQS + MDSAs part of an EIM process

What did we see?

• Knowledge from DQS captured in MDS using Excel• New knowledge • captured in DQS• transferred to MDS using SSIS

MDSAs part of an EIM process

What can we do?

• Use DQS to provide the foundation for Master Data• Use DQS as a means of providing updated Master Data• Have a user driven EIM process

Introducing Data Quality Servicesand its role in an Enterprise Information Management (EIM) ProcessGoals of session:

• Introduce DQS• Concepts• Terminology

• Showcase• Automation• Integration into an EIM process

Introducing Data Quality Servicesand its role in an Enterprise Information Management (EIM) ProcessYou can find me at the speaker meet & greet – 11.00-11.30, speakers lounge, Thursday 13th

And follow me at:• Blogger @ www.bimonkey.com• Tweeter @BI_Monkey• MSDN SSIS & DQS Forums

Introducing Data Quality Servicesand its role in an Enterprise Information Management (EIM) ProcessQ&A

© 2012 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to

be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS

PRESENTATION.