In this tutorial we will explain a unique and NEW approach to ETL Testing. It is a simple and easy to follow method that will allow you to increase your productivity by automating ETL testing. This method can be used to test Data Warehouse mappings or Data migration projects.
In the first few chapters of this tutorial we will build an ETL environment, this will include: An Oracle Express Database, Java and SQL Developer. Although, the book details examples with Oracle, an SQL SERVER version of the Automated ETL testing code is available for download.
We will then explain all of the required Oracle DBA (Database Administrator) knowledge needed to set up a Source and Target schema. When the schemas have been created we will give you the scripts to create simple Source and Target tables.
Next we will look at how to create a simple mapping from source to target using SQL with SQL developer. We will only use SQL mappings, as the focus of this tutorial is on how to automate the mapping testing.
When we have an environment and mapping to test we move on to the core of the course, we explain the approach we will use for testing mappings at table level and field level. We will also give you an Excel template test plan for storing your SQL test queries.
The Excel template is a key part of this testing method, and we will see how to convert the Excel file into a format that will allow the SQL statements to be executed automatically. We will give you a FREE command line driven program, and the source code, which will produce an easy to follow report that indicates which steps passed or failed.
It is a rare thing to see software pass all testing first time, so learn how to remove the need for tedious manual SQL testing, by learning this exciting new ETL automation testing technique today!
Table of contents
Introduction
Before we start
Chapter 1 – Installing Oracle Express
1.1 Download and install Oracle XE
1.2 Recap
Chapter 2 – Installing Java and SQL Developer
2.1 Download and install SQL Developer
2.2 Set up SQL Developer Connections
2.3 Recap
Chapter 3 – Oracle DBA Tasks
3.1 Create accounts
3.2 Add SQL Developer connections
3.3 Create the SOURCE table
3.4 Create the TARGET table
3.4 Run a mapping to populate the target table
3.5 Recap
Chapter 4 – SQL Testing Approach
4.1 Target Table Design Tips
4.1.1 Comparing data sets.
4.2 Table Level Tests
4.2.1 Completeness Check
4.2.2 Business source key mapped.
4.3 Field Level Mapping Tests
4.4 Recap
Chapter 5 – A Source to Target Mapping
5.1 Mapping Excel Spec
5.2 Recap
Chapter 6 – Excel Test Plans
6.1 Test plan components
6.1.1 Test Step Name
6.1.2 Field Name
6.1.3 Description
6.1.4 Expected Result
6.1.5 SQL Statements
6.2 Recap
Chapter 7 – Converting Excel Test Plans to XML
7.1 The Excel Developer tab
7.2 Adding an XML Mapping to an Excel Spreadsheet
7.3 Recap
Chapter 8 – Auto ETL Installation
8.1 XML Input files
8.2 Outline of the Automated ETL module
8.3 Data Structures
8.3.1 run_log_header
8.3.2 run_log_detail
8.4 Installing the code
8.5 Recap
Chapter 9 – Automating an ETL test
9.1 SQLPLUS script commands
9.2 DOS batch commands
9.3 Specimen Test Report
9.4 Recap
Chapter 10 – In conclusion
Appendix – Listing of the Automated ETL code
Bonus Material related to this eBook
More titles from Richard Thrust
In the first few chapters of this tutorial we will build an ETL environment, this will include: An Oracle Express Database, Java and SQL Developer. Although, the book details examples with Oracle, an SQL SERVER version of the Automated ETL testing code is available for download.
We will then explain all of the required Oracle DBA (Database Administrator) knowledge needed to set up a Source and Target schema. When the schemas have been created we will give you the scripts to create simple Source and Target tables.
Next we will look at how to create a simple mapping from source to target using SQL with SQL developer. We will only use SQL mappings, as the focus of this tutorial is on how to automate the mapping testing.
When we have an environment and mapping to test we move on to the core of the course, we explain the approach we will use for testing mappings at table level and field level. We will also give you an Excel template test plan for storing your SQL test queries.
The Excel template is a key part of this testing method, and we will see how to convert the Excel file into a format that will allow the SQL statements to be executed automatically. We will give you a FREE command line driven program, and the source code, which will produce an easy to follow report that indicates which steps passed or failed.
It is a rare thing to see software pass all testing first time, so learn how to remove the need for tedious manual SQL testing, by learning this exciting new ETL automation testing technique today!
Table of contents
Introduction
Before we start
Chapter 1 – Installing Oracle Express
1.1 Download and install Oracle XE
1.2 Recap
Chapter 2 – Installing Java and SQL Developer
2.1 Download and install SQL Developer
2.2 Set up SQL Developer Connections
2.3 Recap
Chapter 3 – Oracle DBA Tasks
3.1 Create accounts
3.2 Add SQL Developer connections
3.3 Create the SOURCE table
3.4 Create the TARGET table
3.4 Run a mapping to populate the target table
3.5 Recap
Chapter 4 – SQL Testing Approach
4.1 Target Table Design Tips
4.1.1 Comparing data sets.
4.2 Table Level Tests
4.2.1 Completeness Check
4.2.2 Business source key mapped.
4.3 Field Level Mapping Tests
4.4 Recap
Chapter 5 – A Source to Target Mapping
5.1 Mapping Excel Spec
5.2 Recap
Chapter 6 – Excel Test Plans
6.1 Test plan components
6.1.1 Test Step Name
6.1.2 Field Name
6.1.3 Description
6.1.4 Expected Result
6.1.5 SQL Statements
6.2 Recap
Chapter 7 – Converting Excel Test Plans to XML
7.1 The Excel Developer tab
7.2 Adding an XML Mapping to an Excel Spreadsheet
7.3 Recap
Chapter 8 – Auto ETL Installation
8.1 XML Input files
8.2 Outline of the Automated ETL module
8.3 Data Structures
8.3.1 run_log_header
8.3.2 run_log_detail
8.4 Installing the code
8.5 Recap
Chapter 9 – Automating an ETL test
9.1 SQLPLUS script commands
9.2 DOS batch commands
9.3 Specimen Test Report
9.4 Recap
Chapter 10 – In conclusion
Appendix – Listing of the Automated ETL code
Bonus Material related to this eBook
More titles from Richard Thrust
