built by

Bill HaydukCEO/President

RTTS

How to Automate Data Interface Testing for

Internal & External Data Feeds

Christopher ThompsonSenior Domain Expert

RTTS

Laura PoggiMarketing Manager

RTTS

Webinar

Today’s Agenda

Data Interface testing • take your testing process to its full potential using

our Maturity Model • centralize and standardize your testing• automate data interface testing • compare XML files and flat files to each other

and to a database• gain 100% coverage with a 95% decrease in

testing time• Demo

built by

AGENDA

Data Interface testing • take your testing process to its

full potential using our Maturity Model

• centralize and standardize your testing

• automate data interface testing

• compare XML files and flat files to each other and to a database

• gain 100% coverage with a 95% decrease in testing time

• Demo

About

RTTS is the leading provider of software quality for critical business systems

FACTSFounded: 1996

Primary Focus:

consulting services, software

Locations: New York, Atlanta, Philly, Phoenix

Geographic region:North America

Customer profile:Fortune 1000, > 600 clients

Software:

QuerySurge

“Unfortunately companies often don't spend enough time aligning the data testing…and validation cycles to the project timeline”.

"You really need to make sure that you're validating and testing throughout the process”.

- InformationWeekQuestion:

How are you going to test the data?

Failure to validate and test the process

built by

The average organization loses $8.2 million annually through poor Data Quality.

- Gartner

46% of companies cite Data Quality as a barrier for adopting Business Intelligence products.

- InformationWeek

Data Quality Best Practices boost revenue by 66%. - Research firm Sirius Decisions

built by

Data Maturity Models &

Data Interface testing

built by

built by

Data Maturity Model - Process

source: IBM Data Governance Council Maturity Model

• Patterned after the Capability Maturity Model Integration(CMMI) from the Software Engineering Institute (SEI) at Carnegie Mellon University

• Devised by IBM, along with 55 other companies

• Few stable processes exist• “Just do it” mentality

• Data-related policies become more clear & reflect the organization’s data principles.

• Data integration opportunities are better leveraged. • Risk assessment for data integrity & quality becomes part of the

organization’s project methodology.

• Further defined value of data for more data elements • Data Governance methodology is introduced during the

planning stages of new projects • Enterprise data models are documented & published

• Data Governance is second nature • ROI for data-related projects is tracked • Business value of data mgmt is recognized • Cost of data mgmt is easier to manage• Costs are reduced as processes become

automated

• More data-related controls are documented• Metadata becomes an important part of documenting critical data

elements.

built by

SamplingLevel

1Sampling a % of data by visually comparing data sets. Not repeatable.

Excel, Ad Hoc ReportingLevel

2Using Excel or other homegrown method. Ad hoc reporting.

Minus QueriesLevel

3Utilizing SQL editor & minus queries to test data. More detailed reporting.

Data Test AutomationLevel

4Fully repeatable test automation, centralized reporting.

Data Maturity Model – Test Execution

What is the maturity level of your

data testing?

source: RTTS

• Patterned after CMMI

• Devised by RTTS based on observations

Data Quality OptimizingLevel

5Full automation, tracking of ROI, predictive data issues, auditable history & results. Business value is fully understood/supported by management.

A Data Interface is a set of attributes representing a given entity, used to create processes that read from, or write to, interfaces rather than directly from or to sources or targets of data.

built by

Data Interface - definition

- Oracle

built by

Data Interface Testing: internal/external feeds

mainframe

Distributed apps

web apps

client/vendor data

• Import into Excel• Use SQL editor to query database• Import results into Excel

• Use the CountIF function• Compare column by column• Excel is incredibly slow• The process is inefficient

built by

Data Interface Testing: Popular Test Strategy

Question:

Is there a better way?

built by

Data Interface Testing

13

Our Solution: Automated Testing

of Data Interfaces

built by

QuerySurge is the

premier test tool built

to automate Data Testing

What is QuerySurge?

built by

automates the testing effort the kickoff, the tests, the comparison, emailing the results

speeds up testing up to 1,000 times faster than manual testing

tests across different platformsany JDBC-compliant db, DWH, DMart, flat file, XML, Hadoop

The QuerySurge solution…

built by

verifies more data verifies upwards of 100% of all data quickly

views & shares results Auditable report history, automated emailing of reports

QuerySurge™ Architecture

built by

Target

Sources

built by

Data Interface Testing: Data Flow

SQL

SQL

SQL

SQL

SQL

SQL

built by

Design Library Create Query Pairs (source & target queries)

18

QuerySurge™ Modules

Scheduling Build groups of Query Pairs Schedule Test Runs

Deep-Dive Reporting Examine and automatically

email test results

Run Dashboard View real-time execution Analyze real-time results

QuerySurge™ Modules

built by

QuerySurge Value-Add

QuerySurge provides value by either:

in testing data coverage from < 1% to upwards of 100%

in testing time by as much as 1,000 x 

combination of in test coverage while in testing time

20built by

Return on Investment (ROI)

redeployment of head count because of an increase in coverage and decrease in need for testers

an increase in better data due to shorter / more thorough testing cycle, possibly saving $ millions by preventing bad data. 

21built by

SamplingLevel

1Sampling a % of data by visually comparing data sets. Not repeatable.

Excel, Ad Hoc ReportingLevel

2Using Excel or other homegrown method. Ad hoc reporting.

Minus QueriesLevel

3Utilizing SQL editor & minus queries to test data. More detailed reporting.

Data Test AutomationLevel

4Repeatable test automation, agreed-upon process, centralized reporting.

On which Level should your

process be?

Data Quality OptimizingLevel

5Full automation, tracking of ROI, predictive data issues, auditable results. Business value is fully understood/supported by management.

built by

Data Maturity Model – Test Execution

Ensuring Data Warehouse Quality

Demonstration

Christopher ThompsonSenior Domain Expert

RTTS

24

• “The IBM Data Governance Council Maturity Model: Building a roadmap for effective data governance”, October 2007.

• “Data Interfaces”, Enterprise Data Quality Help, Oracle

• “What is CMMI?”, CMMI Institute

• “Capability Maturity Model Integration”. Wikipedia

References

built by