etl validator: creating data model

<Insert Picture Here>

datagapsThe data testing company

Dynamic Data Model Overview

Typical Data Warehousing Life Cycle

Plan Design Develop Test Operate

Initiate End

End User

Interviews

Data Profile

Specification

Data

Sources

Sample

Reports &

Concurrency

ETL

Mappings

Schema

Mappings

Warehouse

Data

Dictionary

Technical

Architecture

Facts &

Dimensions

Data Testing

ETL Process

Reports &

Analytics

Performance

& Scalability

Defects

Manage

Growth

Continuous

Integration

EnhancementsReports

Leverage the data model across all phases of the DW Life Cycle

Objective

• As a QA Engineer, I should be able to build/leverage an

existing data model for the following:

• Query Construction

• Testing Referential Integrity

• Identifying noise in the data warehouse

This Deck

• The following slides explain how a data entity model

can be designed in ETL Validator so that the

aforementioned use cases can be reused over time

Upon Login, system displays the

Home Page

Select an existing connection in the

DB Explorer, right click and click on

the “Add Data Model”

ETL Validator a listing of all the tables

in the selected connection/schema •Click on

Create

Search for tables of interest and select

them by clicking on the checkboxes.

Then click on OK.. This will enable you

to specify the join conditions bet

Select a table and click on “Add”

button on the right side.

You can pick another table (Table

2) from the pick list

1 2

3

4

Pick a table from the left side

You can select the attributes for the

join conditions and operators

Click on Add Condition

CHANNEL_ID

CHANNEL_ID

The recently added condition

should show up here

I specified a number of join conditions. Where

are these used?

Usage

• Query Builder

• The queries can be constrained by the filter

conditions

• Referential Integrity Test Plan

• This test plan can automatically identify the records

which do not have valid parents

More Resources

•http://www.datagaps.com

•http://youtube.com/datagaps

•http://www.twitter.com/datagaps