etl test cases

8/18/2019 Etl Test Cases

1/5

Index

1. Introduction...................................................................................................32. About Data Warehouse....................................................................................3

2.1 Data Warehouse definition..........................................................................33. Testing Process for Data warehouse:.................................................................3

3.1 Requirements Testing :...............................................................................33.2 nit Testing :.............................................................................................!3.3 Integration Testing :...................................................................................!

3.3.1 "cenarios to be co#ered in Integration Testing.........................................$

3.3.2 %a&idating the Re'ort data.....................................................................$3.! ser Acce'tance Testing.............................................................................$

!. (onc&usion.....................................................................................................$


2/5

Introduction

This document detai&s the testing 'rocess in#oed in data warehouse testing and test co#erageareas. It e)'&ains the im'ortance of data warehouse a''&ication testing and the #arious ste's of the testing 'rocess.

About Data Warehouse

Data warehouse is the main re'ositor* of the organi+ation,s historica& data. It contains the datafor management,s decision su''ort s*stem. The im'ortant factor &eading to the use of a datawarehouse is that a data ana&*st can 'erform com'&e) queries and ana&*sis -data mining on theinformation within data warehouse without s&owing down the o'erationa& s*stems.

Data Warehouse definition

• Subject-oriented : "ub/ect 0riented Data warehouses are designed to he&' *ou ana&*se

data. or e)am'&e to &earn more about *our com'an*,s sa&es data *ou can bui&d a

warehouse that concentrates on sa&es. sing this warehouse *ou can answer questions

&i4e 5Who was our best customer for this item &ast *ear65 This abi&it* to define a datawarehouse b* sub/ect matter sa&es in this case ma4es the data warehouse sub/ectoriented. The data is organi+ed so that a&& the data e&ements re&ating to the same rea&

wor&d e#ent or ob/ect are &in4ed together.

• Integrated : Integration is c&ose&* re&ated to sub/ect orientation. Data warehouses must

'ut data from dis'arate sources into a consistent format. The database contains data

from most or a&& of an organi+ation,s o'erationa& a''&ications and is made consistent.

• Time-variant : The changes to the data in the database are trac4ed and recorded to

'roduce re'orts on data changed o#er time. In order to disco#er trends in business

ana&*sts need &arge amounts of data. A data warehouse,s focus on change o#er time iswhat is meant b* the term time #ariant.

• Non-volatile : Data in the database is ne#er o#erwritten or de&eted once committed

the data is static readon&* but retained for future re'orting. 0nce entered into thewarehouse data shou&d not change. This is &ogica& because the 'ur'ose of a data

warehouse is to enab&e *ou to ana&*se what has occurred.

Testing Process for Data warehouse:

Testing for a Data warehouse consists of requirements testing unit testing integration testingand acce'tance testing.

Requirements Testing :

The main aim for doing Requirements testing is to chec4 stated requirements forcom'&eteness. Requirements can be tested on fo&&owing factors.

1. Are the requirements (om'&ete62. Are the requirements "ingu&ar63. Are the requirements Ambiguous6!. Are the requirements De#e&o'ab&e6$. Are the requirements Testab&e6

In a Data warehouse the requirements are most&* around re'orting. 7ence it becomes moreim'ortant to #erif* whether these re'orting requirements can be catered using the dataa#ai&ab&e.

"uccessfu& requirements are those structured c&ose&* to business ru&es and addressfunctiona&it* and 'erformance. These business ru&es and requirements 'ro#ide a so&id


3/5

foundation to the data architects. sing the defined requirements and business ru&es high&e#e& design of the data mode& is created. 0nce requirements and business ru&es area#ai&ab&e rough scri'ts can be drafted to #a&idate the data mode& constraints against thedefined business ru&es.

Unit Testing :

nit testing for data warehouses is W7IT890. It shou&d chec4 the 8T;'rocedures


4/5

Scenarios to be covered in Integration Testing

Integration Testing wou&d co#er 8ndto8nd Testing for DW7. The co#erage of the testswou&d inc&ude the be&ow:

1. Count Validation

Record (ount %erification DW7 bac4end start with test cases on street.

'. (t)er validations

?ra'hs "&ice


5/5

,a!idating the Re'ort data

0nce the 8T;s are tested for count and data #erification the data being showed onto there'orts ho&d utmost im'ortance. @A team shou&d #erif* the data re'orted with thesource data for consistenc* and accurac*.

1. %erif* Re'ort data with source A&though the data 'resent in a data warehouse wi&& be stored at an aggregate &e#e&com'are to source s*stems. 7ere the @A team shou&d #erif* the granu&ar datastored in data warehouse against the source data a#ai&ab&e.

2. ie&d &e#e& data #erification @A team must understand the &in4ages for the fie&ds dis'&a*ed in the re'ort andshou&d trace bac4 and com'are that with the source s*stems.

3. (reating "@;s (reate "@; queries to fetch and #erif* the data from "ource and Target."ometimes it=s not 'ossib&e to do the com'&e) transformations done in 8T;. In sucha case the data can be transferred to some fi&e and ca&cu&ations can be 'erformed.

User Acceptance Testing

7ere the s*stem is tested with fu&& functiona&it* and is e)'ected to function as in 'roduction. Atthe end of AT the s*stem shou&d be acce'tab&e to the c&ient for use in terms of 8T; 'rocessintegrit* and business functiona&it* and re'orting.

&onc!usion

8#oing needs of the business and changes in the source s*stems wi&& dri#e continuous changein the data warehouse schema and the data being &oaded. 7ence it is necessar* thatde#e&o'ment and testing 'rocesses are c&ear&* defined fo&&owed b* im'actana&*sis and strong

a&ignment between de#e&o'ment o'erations and the business.

etl test cases

Documents