Testing

Differences between OLTP and OLAP

2016-12-20T10:39:00.001-08:00

Online transaction processing system:

*******************************

In OLTP system, the tables are in normalized structure
As the OLTP is in normalized structure, DML operations like Updates and deletes will be faster
Simple queries are run against the OLTP system
OLTP system contains only current data. As the OLTP system does not contain history data, it cannot be used in reporting

Online analytic processing system:

****************************

In OLAP system, the tables are in de normalized structure
As the OLAP is in de normalized structure, the numbers of joins between tables are less and hence select statements will be faster
Complex queries are run against the OLAP system
OLTP system contains only history data and can be used for reporting purpose

Differences between the Star and Snow flake schemas in Dimensional Modeling

2016-12-20T10:32:00.000-08:00

Star Schema:

1. In a start schema, the fact table will be at the center and is connected to the dimension tables.

2. The tables are completely in de normalized structure.

3. SQL queries performance is good as there are less number of joins involved.

4. Data redundancy is high and occupies more disk space.

Snow Flake Schema:

1. Snow flake schema is an extension of star schema where the dimension tables are further connected to one or more dimensions.

2. The tables are partially de normalized in structure.

3. The performance of SQL queries are a bit less when compared to star schema as more number of joins are involved.

4. Data redundancy is low and occupies less disk space when compared to star schema.

DWH Staging Area Importance

2016-12-20T10:26:00.001-08:00

The Data Warehouse Staging Area is temporary location where data from source systems is pulled in.

A staging area is mainly required in Data Warehousing architecture for timing reasons. In short, all required data must be available before data can be integrated into the Data Warehouse.

Due to varying business cycles, data processing cycles, hardware and network resource limitations and geographical factors, it is not feasible to extract all the data from all Operational databases at exactly the same time.

One another reason for having staging area is to collect the data from heterogeneous databases and processing the data.

Not all business require a Data Warehouse Staging Area. For many businesses it is feasible to use ETL to copy data directly from operational databases into the Data Warehouse.

Parameter Files in DVO

2016-12-18T10:23:00.000-08:00

We can use parameter file in Informatica DVO while defining the where clause of either table pair or single pair. However there are certain rules in order to use.

We must add the parameters to the parameter file before we can add the parameters to a table pair or single table definition.

The parameters in the parameter file must be in the format
$$parameter name=value.

We need to place the parameter file in a location which is accessible by the Data Validation Option Client or DVOCmd that runs the tests.

If we specify the location of the parameter file in the Data Validation Option Client, enter the parameter file location as a network path.
Example, if the Informatica services runs on Windows, use the following syntax:
\\Host name of the Informatica services machine\shared folder path\parameter file name

If we specify the parameter file on a UNIX machine, the Data Validation Option Client on Windows cannot access the parameter file. In this case, we need to install DVOCmd on the UNIX machine and verify that DVOCmd has access to the parameter file.

We can use a parameter file for one or more table pairs and single tables.

Parameter file option is available if the user is having the enterprise license.

Steps to add a Parameter to a WHERE Clause in Table pair \ Single pair :

1. We need enterprise license,to use a parameter in the WHERE clause to dynamically change the WHERE clause after each test run.

2. Create the parameter file and add the parameters as per the above guidelines

3. Place the parameter file in a location that is accessible by the Data Validation Option Client or the DVOCmd command line that runs the tests that require the parameters as per the above guidelines

4. If the parameter file is kept on a UNIX machine, install DVOCmd on the UNIX machine and verify that DVOCmd has access to the parameter file.

5. On the Advanced tab of a table pair or single table definition, specify the location and name of the parameter file.

6. On the Advanced tab of a table pair or single table definition, specify the name, datatype, scale, and precision of each parameter.

ETL Testing

2016-12-03T00:15:00.002-08:00

Data ware house is relational database which is subject oriented, integrated, time-variant and nonvolatile collection of data used to support strategic decision making process

Subject Oriented

Data warehouses are designed to help you analyze data. For example, to learn more about your company's sales data, you can build a warehouse that concentrates on sales. Using this warehouse, you can answer questions like "Who was our best customer for this item last year?" This ability to define a data warehouse by subject matter, sales in this case, makes the data warehouse subject oriented.

Integrated

Integration is closely related to subject orientation. Data warehouses must put data from disparate sources into a consistent format. They must resolve such problems as naming conflicts and inconsistencies among units of measure. When they achieve this, they are said to be integrated.

Nonvolatile

Nonvolatile means that, once entered into the warehouse, data should not change. This is logical because the purpose of a warehouse is to enable you to analyze what has occurred.

Time Variant

In order to discover trends in business, analysts need large amounts of data. This is very much in contrast to online transaction processing (OLTP) systems, where performance requirements demand that historical data be moved to an archive. A data warehouse's focus on change over time is what is meant by the term time variant.

ETL Architecture:

The above snapshot shows the pictorial representation of how a typical Data warehouse Architecture looks like.

ETL basically stands for Extract Transform Load, which simply implies the process where you extract data from Source Tables, transform them in to the desired format based on certain rules and finally load them onto Target tables

Extraction:
Extraction is the process of extracting data from a heterogeneous data source system for further use in a data warehouse environment. This is the first step of the ETL process. After the extraction, this data can be transformed based on the business requirements and loaded into the data warehouse.

There are different mechanisms to extract the data from source systems like Full Extract and Incremental Extract.

Transformation:
The transform stage applies a series of rules or transformations, to the extracted data from the source to derive the data for loading into the target.

Loading:
Once the data got transformed to the business requirement, the transformed data will be loaded into the target systems.
Now as we understood on what we meant by ETL as a process, we should also know on, different tools available in the market to perform ETL process.

Scope of ETL Testing

There is an exponentially increasing cost associated with finding software defects later in the development lifecycle. In data warehousing, this is compounded because of the additional business costs of using incorrect data to make critical business decisions. Given the importance of early detection of software defects, let's first review some general goals of testing an ETL application:

Data completeness
Data transformation
Data quality
Performance and scalability
Integration testing
User-acceptance testing
Regression testing

ETL TESTING LIFE CYCLE

The ETL Test life cycle is not much when compared to the other testing life cycles.

Pre-requisites for ETL Testing

We can start ETL testing when we have the below mentioned are available:

Access to the required folders in the network.
Implementation checklist for move from development to test.
Production configuration and data available.
Test plan is made based on the inputs from test estimation and business requirement.
Test cases and test scenarios from all the available inputs are designed.
All unit testing completed and summarized.
Test data available in test environment.
Migration to the test environment from the development environment

ETL TEST PLAN:

We all know that a test life cycle should undergo different stages as showed in the picture above.

There is no difference in developing the test plan for ETL Testing and conventional testing test plan. The additional items which we have to include in ETL testing are Mapping Names, Session Names, Workflow names, Workflow logs, Parameter Files, SQL files, Source/Target File names, etc. in the Test Plan document. Mention the risks related to data loss, bad data truncation in target db.

ETL TESTING CHALLENGES

ETL testing is quite different from conventional testing. There are many challenges we faced while performing data warehouse testing. Here is the list of few ETL testing challenges I experienced on my project:

1. Incompatible and duplicate data
2. Loss of data during ETL process
3. Testers have no privileges to execute ETL jobs by their own
4. Volume and complexity of data is very huge
5. Fault in business process and procedures
6. Trouble acquiring and building test data

Data is important for businesses to make the critical business decisions. ETL testing plays a significant role validating and ensuring that the business information is exact, consistent and reliable. Also, it minimizes hazard of data loss in production.

ETL TESTING TOOLS

We can perform DWH testing using ETL testing tools available in the market. Few of the ETL testing tools are Informatica DVO, Query Surge etc.

For Informatica DVO details please refer to Informatica DVO section in this blog.

Informatica DVO

2016-12-03T00:14:00.003-08:00

What is Data Validation Option ?

What can be tested ?

To answer above two questions, Data Validation Option is a ETL Testing tool which comes along with power center and enables to test and validate data

Data Validation Option reads table definitions from Power Center metadata repositories, and checks the data for inconsistencies

Verify that moved or transformed data is complete and accurate and has not been changed because of errors in the movement or transformation process

Will not check the correctness of transformations or mappings designed in Informatica

Data Validation stages

A typical workflow or stages for data validation consists of multiple tasks-

1. Data Validation Option reads one or more Power Center metadata repositories
2. We define the validation rules(creation of tests) in Data Validation Option
3. We run the rules(tests) to ensure the data conforms to the validation rules. When we do this, Data Validation Option performs the following tasks:

1.Creates and executes all tests through PowerCenter.
2.Loads results into the Data Validation Option results database and displays them in the Data Validation Option Client.

We examine the results and identify data inconsistencies in the ETL process

We repeat this process for new records.

Data Validation Option Client Layout Overview

The Data Validation Option Client contains multiple areas that allow us to perform different tasks.
Working with Repositories

We can perform below operations on repositories in DVO

Add
Modify
Delete
Refresh
Exporting Repository Metadata

Table pair

A table pair is the basis for all tests that compare one table to another. We can select a relational
table, flat file, lookup view, or SQL view as one or both tables in a table pair.

We can perform below operations on Table Pairs in DVO

Add
Modify
Delete

Tests for Table Pairs

We can create\run the following types of tests on table pairs:

Aggregate -
•Includes COUNT, COUNT_DISTINCT, COUNT_ROWS, MIN, MAX, AVG, and SUM.

Set -
•Includes AinB, BinA, and AeqB.

Value -
•Includes VALUE and OUTER_VALUE.

Note: When we run tests, the target folder must be closed in the Designer and Workflow Manager. If the target folder is open, Data Validation Option cannot write to the folder, and the tests return an error

Single Table Constraints Pairs

single-table pair will be used to run tests on a single table. We can enforce valid values, aggregates, formats, and uniqueness
To run single-table constraints, we must create a single table. You can select a relational table, flat file, lookup view, or SQL view as a single table

SQL Views

SQL views facilitate the use of more complex functionality for single tables and table pairs.
An SQL view allows to use several tables and several calculations in a query to produce a set of fields that you can use as a table in a single table or table pair.

This functionality is similar to the SQL override in PowerCenter or a view in a relational database. You can use any valid SQL statement to create an SQL view.

Lookup Views

Data Validation Option lookup views allows to test the validity of the lookup logic in transformation layer
One disadvantage of the SQL view is that it does not allow the use of flat files or heterogeneous database tables

Join two heterogeneous sources with a lookup view

Import and export of Tests

Data Validation Option allows you to export and import test metadata.

Metadata import and export allows users to share tests and allows rapid generation of tests through scripting.

You can import and export the following metadata:

•Table Pairs
•Single Tables
•PowerCenter Sources
•SQL views
•Lookup views
•Join views

Reports Generation

Data Validation Option stores all test definitions and test results in the Data Validation Option repository. We can run reports to display test definitions and results.
Types of reports:

Summary of Testing Activities
Table Pair Summary
Detailed Test Results

Run tests through Command prompt

Invoke Data Validation Option capabilities at the command line.

Running tests at the command line allows you to schedule test execution. It also allows you to embed a specific test as part of the ETL workflow or as part of another process. For example, you can create an ETL process that moves data from source to staging, runs validation, and then moves data into the target or an error table based on the validation results.

Send Emails
Data Validation Option sends an email when the tests are complete.
You can provide multiple email addresses separated by commas.
The email specifies whether the test has passed or failed and provides a link to the test results.

Benefits of ETL Testing with Data Validation Option

Production Reconciliation: Informatica Data Validation Option provides automation and visibility for ETL testing, to ensure that you deliver trusted data in your production system updates.
IT Developer Productivity: 50% to 90% less time and resources required to do ETL testing
Data Integrity: Comprehensive ETL testing coverage means lower business risk and greater confidence in the data.

HOW TO: Enable debug logging in DVO
================================

To enable debug logging in Data Validator (DVO), edit the log4j.properties file under the DVO installation directory.

To increase the level of debugging, change the parameters from info to debug.

Steps:

====

Go to C:\Program Files (x86)\DVOSoft914\config directory

Edit the log4j.properties file

Change log4j.rootLogger from info to debug;log4j.rootLogger=debug, stdout

Save and close the file

Test Scenarios to be considered during each phase of E-T-L process till Reporting

2016-12-01T05:28:00.001-08:00

During Extraction Process

Extraction scripts are extracting all the attributes/columns required as per the extraction design
Extraction scripts have access to all the source systems from which data needs to be extracted
Extracted data format (flat file/complex file/table) is as per the interface specification design
Extraction scripts are extracting complete data (full dump/delta) as per Changed Data Capture (CDC) design
Appropriate audit/error logs for extraction are getting generated
Extraction process is getting finished within the expected time frame

During Transformation Process

All the data required for loading has been extracted and is in correct format
Attribute level source to target mapping is as per mapping design
Business rules have been implemented correctly as per mapping design
There is no data leakage during various transformation phases in workflow
Data being rejected for any reason (PF-FK integrity, data type etc) is being recorded for audit purpose
Appropriate audit/error/performance/run logs are getting generated
Transformation processes are getting finished within stipulated timeframe

During Loading Process

Physical Data Model of DW has all the entities/attributes to deliver reports/KPIs
All the records/attributes coming out of transformation stage are getting loaded in the target
Data being rejected for any reason (PF-FK integrity, data type etc) is being recorded for audit purpose
Appropriate logs for loading activity are getting generated
Record count matches for target tables with the same granularity as source data (usually dimension/master tables)
Summarized values of measures match with source data in case of aggregation (usually fact tables)
Data loaded in DW is loaded/transformed correctly as defined in mapping design
Loading processes are getting finished within stipulated timeframe

During Data mart loading Process

Physical Data Model of DM has all the entities/attributes to deliver reports/KPIs
Data being rejected for any reason (PF-FK integrity, data type etc) is being recorded for audit purpose
Appropriate logs for loading activity are getting generated
Record count matches for target tables with the same granularity as source data (usually dimension/master tables)
Summarized values of measures match with source data in case of aggregation (usually fact tables)
Loading processes are getting finished within stipulated timeframe

During Reporting Process

Business reports and dashboards are displaying correct data
Completeness of report structure in terms of format, attributes displayed, report level filters/derived calculation, prompts
Ad-hoc report users have all the attributes available in semantic layer which they need for ad-hoc reporting
Users have access to only those reports (also slice of data in report) which they are authorized to access
Report bursting has been implemented correctly for scheduled reports
Reports (canned, ad-hoc, scheduled) are refreshing within acceptable timeframe
Security profiles for different type (basic, power) users have been setup correctly

Down Stream Feed

Downstream systems has access to (all/subset of) DW/DM objects for data extraction
DW/DM has all the attributes at required grain for downstream systems
Downstream systems always get complete feed from DW/DM. DW/DM should not send any feed to downstream system if the loading into DW/DM happened partially.
Data sent to downstream systems is complete and in format as per interface specification design
Appropriate audit/error logs are getting generated

BIG DATA TESTING POINT OF VIEW

2016-11-30T11:11:00.000-08:00

1. Overview

Organizations are adopting "Big Data" as their Data Analytics solution, they are finding it difficult to define a robust testing strategy and setting up an optimal test environment for Big Data.

This is mostly due to the lack of knowledge and understanding on Big Data testing. Big Data involves processing of huge volume of structured/unstructured data across different nodes using languages such as "Map-reduce", "Hive" and "Pig".

A robust testing strategy needs to be defined well in advance in order to ensure that the functional and non-functional requirements are met and that the data conforms to acceptable quality.

In this document we intend to define recommended test approaches in order to test Big data Projects.

2. Definition

We are living in the data age. Every day, we create 2.5 quintillion bytes of data — so much that 90% of the data in the world today has been created in the last two years alone.

Big Data refers to the massive amounts of data collected over time that are difficult to analyze and handle using common database management tools. Below are the few heterogeneous sources from where data is collected -

§ sensors used to gather climate information,

§ posts to social media sites,

§ digital pictures and videos,

§ purchase transaction records,

§ military surveillance,

§ e-commerce,

§ complex scientific and

§ Mobile phone GPS signals and so on and on to name a few.

In short Big Data is nothing but an assortment of such a huge and complex data set which is hard to capture, store, process, retrieve and analyze with the traditional approach.

And 90% of the data collected will be unstructured and only 10 % is structured. There is a high need to evaluate and analyze these 90% of unstructured data.

Example:

Consumer product companies and retail organizations are monitoring social media like Facebook and Twitter to get an unprecedented view into customer behavior, preferences, and product perception

3. Characteristics of BIG DATA

3.1. Volume

Big data implies enormous volumes of data.

Now a day’s data is generated by machines, networks and human interaction on systems like social media and we see the exponential growth in the data storage as the data is now more than text data. We can find data in the format of videos, music’s and large images on our social media channels in TB’s and even in PB’s (e.g.,. blog text is a few kilobytes; voice calls or video files are a few megabytes; sensor data, machine logs, and clickstream data can be in gigabytes.)

In terms of QA there will be a big challenge to ensure that entire data setup processed is correct.

E.g. - Millions of smartphones send a variety of information to the network infrastructure; multiple sensors readings from Factories Pipelines tec. This data did not exist five years ago and the result is that more sources of data with a larger size of data combine to increase the Volume of data that has to be analyzed and tested.

3.2. Variety

Variety refers to the many sources and types of data structured, semi structured and unstructured.

We used to store data from sources like spreadsheets and databases. Now data comes in the form of emails, photos, videos, monitoring devices, PDFs, audio, etc. This variety of structured and unstructured data creates problems for storage, mining and analyzing data.

E.g. - Big Data comes in multiple formats as it ranges from emails to tweets to social media and sensor data. There is no control over the input data format or the structure of the data.

3.3. Velocity

Big Data Velocity deals with the pace at which data flows in from sources like business processes, machines, networks and human interaction with things like social media sites, mobile devices, etc.

The flow of data is massive and continuous.

With the advent of Big Data, understanding the velocity of data is extremely important. The basic reason for this arises from the fact that in the early days of data processing, we used to analyze data in batches, acquired over time.

Typically, data is broken into fixed-size chunks and processed through different layers from source to targets, and the end result is stored in a data warehouse for further use in reporting and analysis. This data processing technique in batches or micro batches works great when the flow of input data is at a fixed rate and results are used for analysis with all process delays. The scalability and throughput of the data processing architecture is maintained due to the fixed size of the batches.

In the case of Big Data, the data streams in a continuous fashion and the result sets are useful when the acquisition and processing delays are short. Here is where the need becomes critical for ingestion and processing engine that can work at extremely scalable speeds on extremely volatile sizes of data in a relatively minimal amount of time.

E.g. - The most popular way to share pictures, music, and data today is via mobile devices. The sheer volume of data that is transmitted by mobile networks provides insights to the providers on the performance of their network, the amount of data processed at each tower, the time of day, the associated geographies, user demographics, location, latencies, and much more. The velocity of data movement is unpredictable, and sometimes can cause a network to crash.

The data movement and its study have enabled mobile service providers to improve the QoS (quality of service), and associating this data with social media inputs has enabled insights into competitive intelligence.

4. Testing BIG Data Implementations - How is this different from Testing DWH Implementations?

Whether it is a Data Warehouse (DWH) or a BIG Data Storage system, the basic component that's of interest to us, the testers, is the 'Data'.

At the fundamental level, the data validation in both these storage systems involves validation of data against the source systems, for the defined business rules

Let us look at these differences between DWH testing and Big data testing from the following 3 perspectives:

· Data

· Infrastructure

· Validation tools

	DWH	Big Data
Data	A DWH tester has the advantage of working with 'Structured' data	BIG Data tester has to work with 'Unstructured or Semi Structured' data (Data with dynamic schema)

Infrastructure	RDBMS based databases (Oracle, SQL Server etc) are installed in the ordinary file system. So, testing of DWH systems does not require any special test environment as it can be done from within the file system	For testing Big data in HDFS, the tester requires a test environment that is based on HDFS itself. Testers need to learn the how to work with HDFS as it is different than working with ordinary file system.

Validation Tool	DWH tester has to work with structured query language (SQL) so querying the data and validation the data is easy	NoSQL databases are document based, key-value pairs, graph databases or wide-column stores. So testing big data with NOSQL tester has to write junit or unix scripts with logic to validate

5. Big Data Testing Approach

Big data deals with huge data and executing on multiple nodes so there are high chances of having bad data and each stage of process.

Testing should be performed at each of the phases to ensure that data is getting processes without any error

· Pre- Hadoop processing

· Map reduce process

· Data extract and load in EDW

· Reports

5.1. Pre- Hadoop processing

1. Comparing input data file against source system data to ensure the data is extracted correctly.

2. Validating the data requirements and ensuring the right data is extracted.

3. Validating that the files are loaded into HDFS correctly.

4. Validating the input files are split, moved and replicated in different data nodes

5.2. Map Reduce Process:

1. Validating that data processing is completed and output file is generated.

2. Validating the business logic on standalone node and then validating after running against multiple nodes.

3. Validating the map reduce process to verify that key value pairs are generated correctly.

4. Validating the aggregation process after reduce process.

5. Validating the output data and format against the source file and ensuring the data processing is complete.

5.3. Data extract and load in EDW:

1. Validating that transformation rules are applied correctly.

2. Validating the data load in the target system.

3. Validating the aggregation of data.

4. Validating the data integrity in Target system

5.4. Reports:

1. Validate the data coming in the reports is as expected

2. Validated the cube to t verify the pre –aggregated values are calculated correctly

3. Validate the Dashboards to ensure that all objects are rendered properly

4. Validate the reports to ensure that data fetched from various web parts is validated against data base.

6. Big Data Testing Types

In this section we will discuss about types of testing can be performed to big data

1. Functional Testing

2. Non-Functional Testing

6.1. Functional Testing

Testing big data is essentially testing its three dimension or characteristics of big data i.e. volume, velocity and variety to ensure there is no data quality defects

6.1.1. Testing velocity

a) Performance of Pig/Hive jobs and capture

b) Job completion time and validating against the benchmark

c) Throughput of the jobs

d) Impact of background processes on performance of the system

e) Memory and CPU details of task tracker

f) Availability of name node and data nodes

6.1.2. Testing volume

a. Use sampling strategy

b. Convert raw data into expected result format to compare with actual output data

c. Prepare ‘Compare scripts’ to compare the data present in HDFS file storage

6.1.3. Testing variety

6.1.3.1. Structured Data:

ü Compare data using compare tools and identify the discrepancies

6.1.3.2. Semi-structured Data:

ü Convert semi-structured data into structured format

ü Format converted raw data to expected results

ü Compare expected result data with actual results

6.1.3.3. Unstructured Data:

ü Parse unstructured text data into data blocks and aggregate the computed data blocks

ü Validate aggregated data against the data output

6.2. Non- function Testing

6.2.1. Performance testing

Big data project involves in processing huge volumes of structured and unstructured data and is processed across multiple, nodes to complete the job in less amount of time. At times because of poor design and architecture performance is degraded. Some of the areas where performance issues can occur are imbalance in input slits, redundant shuffle and sorts, moving most of the aggregation computations to reduce process and so on. Performance testing is conducted by setting up huge volume of data in an environment.

NoSQL solutions are very different from your usual RDBMS, but they are still bound by the usual performance constraints: CPU, I/O and most importantly how it is used.

We test big data to identify the bottlenecks. And we conduct performance testing but setting up huge volume of data and infrastructure same as production and check performance metrics like job completion time, throughput and system metrics like memory utilization etc.

The process starts with the setting up of the Big data cluster which is to be tested for performance.

Some tools for performance test on big data are:

1) YCSB: YCSB is a cloud service testing client that performs reads, writes and updates according to specified workloads.

2) SandStorm: SandStorm is an automated performance testing tool that supports big data performance testing.

3) JMeter: JMeter provides few plugins to apply load to Cassandra. This plugin acts as a client of Cassandra and can send requests over Thrift.

6.2.2. Failover testing

Failover testing is an important focus area in big data implementation with objective of validation the recovery process and to ensure the data processing happens seamlessly when switched to other data nodes.

Some validation needs to be performed during failover testing are validating that checkpoints of edit logs, recovery when data nodes fails or become corrupt.

6.2.3. Security Testing

As data grows with variation means from different channel and sources associates risk also grows so we can say big data as BIG risk associated with it so security testing plays important role here

a) Authentication and Authorizations : validating roles and privileges to collect the data , ideal way is to maintain a list of users, roles and authorizations against each source/system

b) Network Security: sometime Data received/transferred from/to is encrypted to maintain Confidentiality we need to test all aspect of security like Firewall, Networks Policies and Anti-Virus Software, Intrusion detection to insure the data format and degree of confidentiality remain intact.

7. Automation testing on big data testing

Manual testing does not scale in the testing in big data implementation as it is on distributed environment and data size is also very huge.

There are some tools available in market which supports big data test automation like

Query surge, Zettaset, Informatica DVO

How to avoid problematic and unwanted connections while refreshing the Connections in DVO Repository

2016-11-30T07:15:00.000-08:00

As we know we can refresh the folder list, the Sources, Targets folders and connections in DVO repository and however whole process will consume lot of time for refresh to happen.

In order to save time we can explicitly follow certain steps to skip the unwanted connections being refreshed from Powercenter repository into the DVO repository.

Here are the steps we can follow to do so:

§ Close the Data Validation Option Client

§ Create a text file and name it as ‘ExcludeConns.txt’

§ Add the file to the root directory where Data Validation Option is installed. For example, add the ‘ExcludeConns.txt’ file to the following directory: C:\Program Files\Informatica9.5\DVO952

§ Edit the file and just add the name of the connections which were to be excluded from importing\refreshing

§ Open the Data Validation Option Client

§ Refresh the connections again

Note:

1) We can run the pmrep listConnections command to list all connections in a PowerCenter repository)

2) Data Validation Option searches for this file name before importing the connections.

Maintain Multiple Data Validation Option instances

2016-11-21T09:52:00.000-08:00

Maintain Multiple Informatica DVO instances ========================================

We can create multiple instances of Data Validation Option such as Dev instance, SIT Instance or Prod instance with a single DVO installation.

By Default Data Validation Option (DVO) will be installed in C:\Program Files\DVOSoft Directory and Data Validation Option configuration files will be stored in the C:\Documents and Settings\\DataValidator directory.

If you want to maintain multiple instances of DVO (Dev, QA, and Prod), we can do the following:

Step 1: Delete the contents of the C:\Documents and Settings\\DataValidator\* directory.

Step 2 : Create Dev, QA and Prod folder in the same directory.

C:\Documents and Settings\\DataValidator\Dev
C:\Documents and Settings\\DataValidator\Qa
C:\Documents and Settings\\DataValidator\Prod

Step 3 : Start the DataValidator with the following command:

DataValidator.exe “C:\Documents and Settings\\DataValidator\Dev”
DataValidator.exe “C:\Documents and Settings\\DataValidator\Qa”
DataValidator.exe “C:\Documents and Settings\\DataValidator\Prod”

This will store the respective configuration in corresponding Dev, QA and Prod folder.

We can also create the shortcuts for all these three DVO instances by editing the target definition properties as following:

Create three different shortcuts for three different configurations and this will avoid the issue of reconfiguring the DVO every time when you want to change the DVO repository instance.

Enabling debug logging in DVO

2016-11-21T09:25:00.001-08:00

HOW TO: Enable debug logging in DVO
================================

To enable debug logging in Data Validator (DVO), edit the log4j.properties file under the DVO installation directory.

To increase the level of debugging, change the parameters from info to debug.

Steps:

====

Go to C:\Program Files (x86)\DVOSoft914\config directory

Edit the log4j.properties file

Change log4j.rootLogger from info to debug;log4j.rootLogger=debug, stdout

Save and close the file

Dimensional Modelling in DWH

2014-08-26T07:40:00.004-07:00

Data Warehouse Dimensional Modelling (Types of Schemas)

Four types of schemas

Star Schema:

A star schema is the one in which a central fact table is sourrounded by denormalized dimensional tables.

A star schema can be simple or complex.

A simple star schema consists of one fact table where as a complex star schema have more than one fact table.

Snow Flake Schema:

A snow flake schema is an enhancement of star schema by adding additional dimensions.

Snow flake schema are useful when there are low cardinality attributes in the dimensions.

Galaxy Schema:

Galaxy schema contains many fact tables with some common dimensions (conformed dimensions).

This schema is a combination of many data marts.

Fact Constellation Schema:

The dimensions in this schema are segregated into independent dimensions based on the levels of hierarchy.

For example, if geography has five levels of hierarchy like teritary, region, country, state and city;

constellation schema would have five dimensions instead of one.

Setting up an Oracle ODBC Driver and Data Source in 64 bit OS

2014-08-26T01:20:00.001-07:00

Setting up an Oracle ODBC Driver and Data Source

Setting up an Oracle ODBC Driver and Data Source requires more steps than the Microsoft SQL Server database setup. This article explains the steps including tnsnames.ora configuration.

Connecting to an Oracle database via and ODBC driver requires the following steps:

Install the ODAC (Oracle Data Access Components) or Oracle Client on the Workstation or Application Server.
Setup the SQLNET and TNSNAMES files in the Client or ODAC home’s Admin directory.
Use the ODBC Data Source configuration tools to create the ODBC Data Source.

The following blog shows the process involved for each of these steps to get you a working connection to an Oracle database.

Prerequisites: Java JRE or SDK 6 Update 27 or later. These are required by the Oracle Universal Installer to run the ODAC installation program.

Step 1 – Install the ODAC or Oracle Client

In this example we will be installing the ODAC client to complete an ODBC setup on a Windows 7 (64-bit) operating system. You can install the full administrative option of the Oracle Client to obtain the ODBC driver.

Download the ODAC112030_x64.zip file from Oracle’s web site:

http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

Unzip the file to a location of your choosing.

Locate the Setup.exe file under the root directory of the extracted zip file’s directories.

Double-click to run the setup program.

In the ‘Oracle Universal Installer’, click the Next button.

In the ‘Select a Product to Install’ dialog, go with the default ‘Oracle Data Access Components for Oracle Client 11.2.0.3.0’ option.

In the ‘Install Location’ dialog, enter a path to install the ODAC home directory to in the ‘Oracle Base:’ field.

In the ‘Available Product Components’ dialog, go with the default options.

In the ‘Summary’ dialog, verify that the components you have selected for install include the ‘Oracle ODBC Driver for Instant Client 11.2.0.3.0’.

Click ‘Install’.

When the installation is complete, click ‘Exit’.

This concludes the steps for installing the ODBC drivers.

Step 2 – Setup the SQLNET and TNSNAMES files

Locate the SQLNET.ora and TNSNAMES.ora in the ‘samples’ directory of the newly installed Oracle client home directory. This is typically:

C:\app\johndoe\product\11.2.0\client_1\Network\Admin\Sample

Where ‘johndoe’ is the windows user name.

Copy these two files into the ‘Admin’ directory above.

Open the ‘TNSNAMES.ora’ file in Notepad and enter your Oracle database details in the file.

Save your changes.

Note: the ‘SQLNET.ora’ generally doesn’t need to be changed.

Step 3 – Create the ODBC Data Source

In the Windows Start menu, click on the Data Sources (ODBC) icon that was created by the ODAC client install.

In the ‘User DSN’ tab of the ‘ODBC Data Source Administrator’ dialog, click Add.

In the ‘Create New Data Source’ dialog, select the ‘Oracle in OraClient 11g_home1’ driver.

Click ‘Finish’.

In the ‘Oracle ODBC Driver Configuration’ dialog, enter the details of your data source.

Note: you should be able to select the TNS Service Name from the drop down list. This name will be that which you named in the TNSNAMES.ora file’s Alias = section.

Click ‘Test Connection’ to ensure your settings are all correct.

When prompted, enter the database login credentials.

Click ‘OK’.

Click OK to complete the process.

Data Migration Testing Startegy

2014-08-25T00:54:00.000-07:00

1. Purpose of the Article

The purpose of this Aricle is to describe the different test stages that will be performed; how the testing will be prepared and executed for each stage, and the criteria that need to be met before transition from one stage to the next can occur.

2. Phases involved in Data Migration Testing

Below are the phases involved in testing data migration.

· Fusion

· Release

1. Design

2. Implementation

a) Ear

b) Iterations

3. SIT (System Integration Testing)

4. UAT (User Acceptance Testing)

2.1. Fusion (Discover and Define)

In this Phase the requirements for the Data Migration project is gathered and identified from the Testing perspective. QA is responsible for defining the scope of testing; high level estimation and Requirement Specification docs.

This phase we will perform pre-migration testing, these tests occur early in the migration process, before any migration, even migration for testing purposes, is completed. The pre-migration testing options include

· Verify scope of source systems and data with user community and IT. Verification should include data to be included as well as excluded and, if applicable, tied to the specific queries being used for the migration.

· Verify the source to target high-level mappings for each category of data or content and verify that the desired type has been defined in the destination system.

· Verify destination system data requirements such as the field names, field type, mandatory fields, valid value lists and other field-level validation checks.

· Using the source to destination mappings, test the source data against the requirements of the destination system. For example, if the destination system has a mandatory field, ensure that the appropriate source is not null, or if the destination system field has a list of valid values, test to ensure that the appropriate source fields contain these valid values.

· Test source and target system connections from the migration platform.

Key inputs:

· High Level Data Flow

· High Level Use Cases/Specification

Key Outputs:

· Requirement Specification Docs

· Draft Version of Test Plan

· Mapping Documents

· High Level Estimation Plan

2.2. Release

2.2.1. Design

Conduct a formal design review of the migration specification when the pre-migration testing in near complete.

The specification should include:

· A definition of the source systems

· The source system’s data sets and queries

· The mappings between the source system fields and the destination system

· Number of source records

· Number of source systems records created per unit time (to be used to define the migration timing and downtime)

· Identification of supplementary sources

· Data cleansing requirements

· Performance requirements

· Testing requirements

In this phase, the Mapping document has been defined which has detailed information about mapping of Legacy system to that of migrated system. QA persons responsible for closure of the test strategy plan and start writing high level scenarios/cases based on the requirement specification document and mapping documents. The aim of QA people is to understand the Data Model, field mappings with the help of requirement specification document and mapping document and create the Test scenarios. Team is responsible for identification the test data management process and defining the RTM. Existing database need to be reviewed in this phase.

Key inputs:

· Detailed Business requirement/Specification Document

· Mapping Document

Key Outputs:

· Draft test plan & strategy

· Signed –off Test Strategy

· Signed -off Test Plan

· High Level Test Scenarios

· RTM (Scenarios mapped with requirements)

2.2.2. Implementation

2.2.2.1. EAR (Executable architectural release)

In this phase QA team create the detailed test cases and SQL scripts for validating the Migration. QA Team is responsible for test data set-up and is ensuring that the test cases are mapped to all the test scenarios which in turn are mapped to scope items in the requirements document. Team is responsible for checking the readiness of test environment like ETL process ran for updated/Refreshed Data in DB.

Key inputs:

· Signed –off Test Scenarios

· Mapping Document (contains business logic or transformation logic)

Key Outputs:

· Signed –off Test cases & SQL Queries for Data validation

· Test Data

· Traceability Matrix ( Requirement mapped with test cases)

· Test Environment readiness report

2.2.2.2. Iterations

In this phase the QA persons performs the actual testing on data being migrated in iterations. During this phase tester would be performing system testing partially as per the scope of that particular iteration.

Key inputs:

· Signed –off Test Cases

· Test Environment and tools are running fine

· Test Data

Key Outputs:

· Test Results

· Defect report

· All System test cases executed successfully

· No Sev1 and Agreed number of Open Sev2 Defects

2.2.3. System Integration Testing (SIT)

Once a migration has been executed, end to end testing can be executed. Expect a significant sum of errors to be identified during the initial test runs although it will be minimized if sufficient pre-migration testing is well executed. Post-migration is typically performed in a test environment and includes:

· Test the throughput of the migration process (number of records per unit time). This testing will be used to verify that the planned downtime is sufficient. For planning purposes, consider the time to verify that the migration process was completed successfully.

· Compare Migrated Records to Records Generated by the Destination System – Ensure that migrated records are complete and of the appropriate context.

· Summary Verification – There are several techniques that provide summary information including record counts and checksums. Here, the number of records migrated is compiled from the destination system and then compared to the number of records migrated.
Compare Migrated Records to Sources – Tests should verify that fields’ values are migrated as per the migration specification. In short, source values and the field level mappings are used to calculate the expected results at the destination. This testing can be completed using sampling if appropriate or if the migration includes data that poses significant business or compliance risk, 100% of the migrated data can be verified using an automated testing tool.

Key inputs:

· Signed –off Test Cases

· Test Environment and tools are running fine

· Test Data

Key Outputs:

· Test Results

· Defect report

· No Sev1 and Agreed number of Open Sev2 Defects.

· UAT Preparedness (set of activities which does)

2.2.4. USER ACCEPTANCE Testing (UAT)

In this phase the UAT team is involved in testing and making sure of the below:

· Validate that the migration plan satisfies the business and technical requirements identified for the migration.

· Find and resolve potential risks to the production environment

· Attention should be given to reporting, downstream feeds, and other system processes that rely on migrated data

Key inputs:

· QA Testing is complete and meets exit criteria

· No Sev1 defects in OPEN state

· UAT Test Cases have been written and Sign off happened by Business SME

· UAT Test Data has been set up

· All the necessary access has been granted to the business users

· Release notes/Traceability Matrix has been made available

Key Outputs:

· All in-scope tests for UAT have been executed at least once

· UAT Test Results have been recorded in QC

· No Sev1 and Agreed number of Open Sev2 Defects.

· An action plan has been created for any agreed outstanding defects

3. Architecture

Data migration testing is to compare migrated data with original data to find out any discrepancies.

When we are speaking database migration testing we can say it as ETL testing.

Below is the ETL architecture:

4. Testing Approach

Data migration activity typically will include everything that is required with respect to data to ensure that the new database is up and running without any defects and it also contains all the legacy data ( data that is present in the existing database) , and the data has been migrated to correct table(s) and column(s).

Many constraints shape the style and amount of testing that we perform on a database. Testing can contain one or all of the following:

· Simple data validation

· Full life cycle of testing addressing individual unit tests

· System and acceptance testing

There are several types of tests that we use during the migration process. During the testing stage, we go through several cycles of testing to enhance the quality of the database. The test cases we use should make sure that any issues encountered in a previous version of the DB database are not introduced again.

Figure 1 below indicates what you should consider testing when it comes to relational databases.

The diagram is drawn from the point of view of a single database; the dashed lines indicate threat boundaries, indicating that you need to consider threats both within the database.

Below picture depicts stages involved in Data Migration Testing (agile approach):

4.1. To test the DB database:

1. Create a controlled version of the migrated database. Keep the database migration scripts in a source control system.

2. Design a set of test cases that we can use to test the DB database from unit to system level. The test cases should:

a. Ensure the following:

v All the users in the source database have migrated successfully

v Privileges and grants for users are correct

v Tables have the correct structure, defaults are functioning correctly,

v and errors did not occur during mapping

b. Validate that the data migrated successfully by doing the following:

v Comparing the number of rows in the DB database with those in the source database

v Calculating the sum of numerical columns in the DB database and compare with those in the source database

c. Ensure that the following

v Constraints: We cannot enter duplicate primary keys

v Foreign keys prevent we from entering inconsistent data

v Check constraints prevent we from entering invalid data

d. Check that indexes and sequences are created successfully

e. Ensure that triggers, procedures, and functions are migrated successfully. Check that the correct values are returned for triggers and functions

3. Run the test cases against the migrated database.

4. Create a report that evaluates the test case results.

These reports allow evaluating the data to qualify the errors, file problem reports, and provide a customer with a controlled version of the database.

5. If the tests pass, go to Step 7

If all tests in the test cases pass or contain acceptable errors, the test passes. If acceptable errors occur, document them in an error report that we can use for audit purposes.

6. If the test cases fail, do the following:

a. Identify the cause of the error.

b. Identify the test cases needed to check the errors.

c. Log an issue on the controlled version of the migrated database code in the problem report.

d. Add the test case and a description of the problem to the incident tracking system of yours organization, which could be a spreadsheet or bug reporting system. Aside from the test case, the incident log should include the following:

v Provide a clear, concise description of the incident encountered

v Provide a complete description of the environment, such as platform and source control version

v Attach the output of the test; if useful indicate the frequency and predictability of the incident

v Provide a sequence of events leading to the incident

v Describe the effect on the current test, diagnostic steps taken, and results noted

e. Attempt to fix the errors.

f. Return to Step 1

7. Identify acceptance tests that we can use to make sure the DB database is an acceptable quality level.

4.2. Types of Testing

QA will focus on the following testing types for Data Migration.

1. Functional Testing

a) Data Validation

b) Regression testing

2. Non Functional Testing

a) Performance testing

b) Load testing

3. Integration Testing

4. UAT Testing

4.2.1. Functional Testing

All the functionality testing activities carried out in the Implementation phase of development would be categorized as Functional testing. Testing team will create functional test cases and ensure that these test cases are mapped to all the test scenarios which in turn are mapped to scope items in the requirements document. Testing activities in a particular iteration would depend upon the development stories being delivered.

4.2.1.1. Data Validation Testing Approach and its Activities

Refer to section 4.1 to test the DB database

4.2.1.2. Regression Testing Approach and its Activities

At the end of each iteration, application would be regress tested to ensure that any change in one part of application does not hamper functionalities of other areas.

· Regression suite will consist of all high priority scenarios of the requirements delivered in a given iteration.

· Re-validate of existing functionalities after fixes/changes.

4.2.2. Non-Functional Testing

Testing team will create Non- functional test cases and ensure that these test cases are mapped to all the test scenarios which in turn are mapped to scope items in the requirements document.

Non-functional technical tests are tests that verify correct operation of migration mechanisms, such as transformation, loading, purging, incremental data migration. Of course, the roll-back and commit modes are verified.

4.2.3. Integration Testing

The objective of integration testing is to verify that functional areas of system are working when integrated.

4.2.4. User Acceptance Testing (UAT)

User acceptance testing provides an opportunity for the user community to interact with legacy data in the destination system prior to production release, and most often, this is the first such opportunity for the users. Attention should be given to reporting, downstream feeds, and other system processes that rely on migrated data.

Informatica DVO installation steps

2014-08-25T00:30:00.003-07:00

1      About This
Article

The purpose of this article is to explain the steps involved in installing
and configuring Data Validation Option for usage with Informatica PowerCenter
installed on a local machine.

Informatica PowerCenter Server can be installed on a remote machine other than
the local machine. But installation of Data Validation Option should be done on
the same machine as the Informatica PowerCenter Client.

2      Functional
Overview

Informatica Data Validation Option enables you to test and validate data.
It requires the installation of, and does much of its work through, Informatica
PowerCenter.

Data Validation Option reads table definitions from PowerCenter metadata
repositories, and checks the data at either end of the process. It does not
check the correctness of transformations or mappings. Data Validation Option
identifies problems or inconsistencies. It does not attempt to identify the
source of the problem in the ETL process.

3      Pre Requisites
and Permissions

3.1          Pre-requisites

·The Informatica domain must contain at least one PowerCenter Integration
Service.

·Install PowerCenter Client on the same machine where Data Validation
Option will be installed.

·PowerCenter Client is setup with at least one PowerCenter repository.

·Informatica user should have privileges to create a folder in Informatica
PowerCenter Repository Manager and to create a relational connection in
Informatica Workflow Manager.

3.2          System
Permissions

We
require certain system permissions to complete Data Validation Option
installation and configuration. To complete Data Validation Option setup,
verify that you have the permission to complete the following tasks:

·To
create a database, including the ability to create schemas, tables, indexes,
sequences, and views.

·To
create a PowerCenter connection object in the Workflow Manager.

4      Pre -
Installation Steps

STEP-1: Create SQL user account for DVO
repository

· Create
a new user\schema and make sure this user is having privileges to create
and modify tables, indexes, sequences and views

STEP-2: Create folder in Informatica
PowerCenter Repository Manager

· Login to the Informatica
PowerCenter repository from the PowerCenter Repository Manager tool

· Create a folder . This folder is needed
by DVO to store the mappings, sessions and workflows that get generated while
performing tests and to execute these tests. Every DVO user must have the
privileges to use this folder

· As a best practice, do not store other
mappings and workflows in this folder

STEP-3: Create relational connection in
Informatica Workflow Manager

·Login to the Informatica
PowerCenter repository from the PowerCenter Workflow Manager tool and create a
relational connection that points to the DVO repository database created
in STEP-1. This is needed for Informatica PowerCenter Integration Service to
connect to the DVO repository to store the test results in the DVO repository
tables

.All Data Validation Option users must have
the permission to use this connection.

STEP-4: Before you install
DVO, gather the below information that you need during  installation 

·Informatica Domain name

·PowerCenter Integration
Service name

·PowerCenter Repository
Service name

·PowerCenter Repository user
name

· PowerCenter Repository password

·Location of the
domains.infa file on the client machine

5      Installation
Steps

STEP-1: Close all other applications before
running the installation

STEP-2: Run the installer
from the following location : < Download
location>\Install_DataValidator_.exe

STEP-3: In the Welcome panel,
enter the location where you want to install Data Validation Option and Click
Next.

STEP-4: Select the Start Menu folder where you
want to create Data Validation Option shortcuts

·         Optionally, select Create
shortcuts for all users.

·         Optionally, select don’t
create Start Menu items to skip creation of Start Menu items.

·         Click Next.

STEP-5: The installer shows the progress of the
installation. When the installation is complete, the Installation Summary
window displays the status of the installation

·         Optionally, select Run
PowerCenter Data Validation Option to run Data Validation Option after you exit
the installation.

·         Optionally, select create a
Quick Launch icon to create a quick launch icon to launch Data Validation
Option.

·         Optionally, select create a
Desktop icon to create a desktop icon to launch Data Validation Option.

STEP-6: Click Finish

6      Post -
Installation Steps

STEP 1: On the client machine, create an
environment variable called INFA_HOME and set the value to the location of the
domains.infa file. The location of the domains.infa file is \clients\PowerCenterClient\.

STEP 2: Create a folder in the root directory
on the Informatica Services machine and copy the Data Validation Option JAR
file, dvoct.jar, from the \powercenterlibs directory on the Data
Validation Option Client to that folder.

STEP 3: Update the Java SDK Class path for the
PowerCenter Integration Service with the path of the Data Validation Option JAR
file. 

STEP 4:  Configure the Data Validation
Option repository in the Data Validation Option Client.

       Configure
the Data Validation Repository with the database details.

1.In
the Data Validation Option client, select Tools > Preferences >
Data Validation Option.

2.The
Preferences dialog box appears.

Enter the following database information:

Option

Description

User

Any User name for Data Validation Option.

Database Type

Type of database created for Data Validation Option repository.
  Select required database.

Database URL

Connection string to connect to the database. Update the string
  with the details of the

   Data Validation Option repository database.

The following connection strings appear for the different
  databases:

1. Oracle : jdbc:informatica:oracle://:;ServiceName= 

2. Microsoft SQL Server :
  jdbc:informatica:sqlserver://:;DatabaseName= 

3. IBM DB2 : jdbc:informatica:db2://:;DatabaseName=

Database User

Database user name.

Database Password

Database user password.

3.Click Test to make sure the database information is correct.

4.Click Save, and create the Data Validation Option repository schema when
prompted.

7      Configuring DVO
Repository

STEP 1: Add the PowerCenter repository to store
the mappings that Data Validation Option creates in the Data Validation Option
client.

Property

Description

Name

Name of the repository. When you create a repository, enter a
  name that indicates whether the repository is a source or a target
  repository. When you edit a repository, you cannot change the name. The
  repository name is case sensitive.

Client Location

Location of the pmrep.exe file on the client machine. Typically
  the location is: C:\Informatica\\clients\PowerCenterClient\client\bin.

PowerCenter Version

The PowerCenter version.

PowerCenter Domain

The Informatica domain name.

Repository

The name of the PowerCenter repository.

User name

The user name for the PowerCenter repository.

Password

The user password for the PowerCenter repository.

Security Domain

The LDAP security domain. Leave this field blank if you use
  native authentication.

Contains Target Folder

Indicates whether the repository is a source or target
  repository. Select false if the repository you create is a source repository.
  Select true if it is a target repository. When you select true, you must
  specify the Target Folder, Integration Service, and Data Validation Option
  Results Warehouse Connection information.

Target Folder

The name of the folder that has been set up in the PowerCenter
  repository to store the mappings generated by the tests set up in Data
  Validation Option.

Integration Service

The name of the PowerCenter Integration Service.

Data Validation Option Results Warehouse Connection

The name of the PowerCenter connection to the Data Validation
  Option repository.

STEP 2: Click Test to make sure the Repository
is created successfully

STEP 3: Save and close the window

ETL Testing: How to Achieve 100% Data Validation (QuerySurge)

2013-11-28T11:04:00.001-08:00

Data Migration Testing

2013-11-28T01:59:00.000-08:00

Data Migration Testing

Data migration is a process which involves the migration of data from an existing legacy system to a new system. Whenever an organization decides to upgrade or change its database, it will need to transport the existing data to the new database.

Data migration activity includes everything that is required with respect to data to ensure that the new database is up and running without any defects and it also contains all the legacy data ( data that is present in the existing database) , and the data has been migrated to correct table(s) and column(s).

During this data migration there may be possibility of data missing or data corruption. So after the data migration process one has to test whether the whole data is migrated successfully or not, whether something is missing or got corrupted during migration.

So data migration testing is to compare migrated data with original data to find out any discrepancies.

Two types of data Load:

1. Direct Loading.
2. Incremental Loading.

Direct Loading:

The direct loading is copying the data from source to target. The number of rows should be equal among source and target.

Incremental Loading:

Loading the data from source to target by applying some transformations. Cleaning the data or load the data with specific conditions.

Types of Migration

Migration of data from one database to another can be further classified into various subsets.

a. Direct Migration from the source database to the target:
In this type of migration the data elements that are present in the source database is directly transferred into the target database
b. Migration after performing certain transformations on the source data:
This migration requires some transformation of the source data before it is migrated to the new database.
For e.g., a field ‘Priority’ in the source database may allow only one out of the three values High, Medium and Low. But in the target database the same field allows one out of the three values 1, 2 and 3. Thus the a High value in the source database will be transformed to a value 1 and will then be migrated to the target database.

Test Planning

Activities during planning of data migration testing:-

1. Understanding the Requirements.

2. Understanding the databases involved.

3. Estimating the size of the data that is under the scope of data migration.

4. Identifying the 'Entity Relationship' in the existing and the new databases. This indicates how various attributes are related to each other in the two databases.

5. Identification of the complexity of migration, like -

6. a. Data migration (directly migrated from existing to the new database). This can be further divided into two categories
i. AS IS transformation: The data from the existing database that is directly migrated to the new database without any transformation, this follows the logic - transform as it is.
ii. Functional transformation: This is the set of data that undergoes some transformation before it is migrated to the new database. This is done based on the transformation rule as specified in the requirements.
b. Manually setting up data (manually populating some of the tables in the new database).

c. Data that is generated at the new database. There are some attributes for which values are generated every time any insert into or update on the table occurs.

d. Data that has to comply with referential integrity. This data is generated at the new database but the data comes from the parent attribute.

e. Data that is populated as default value for some of the attributes.

6. Identifying the tests to be automated and the tests that can be automated.

7. Developing approach and SQL queries. This step includes development of test approaches. Test approaches are developed based on the different types of migration identified in the step 5

Checklist for test Data Migration Testing

1. We can check for the table row count.
2. We have to check database integrity.
3. We have to check the referential integrity.
4. Whether the data are loaded in the target database correctly.
5. Check the constraints.
6. All the entities are transformed or not like tables, SPs, Views, functions etc
7. Check the functionality of your application with target database, it should work as it is supposed to work

Data Migration Testing Life Cycle

The following are the steps to be followed in the process of Data Migration Testing:-
1. Data Migration Requirement Analysis
2. Test Strategizing
3. Creation of POC
4. Test Planning
5. Design of Automated Test Scripts
6. Test Environment Setup
7. Test Execution
8. Result and Defect Analysis

Risks Involved

1. Before the QA team starts to develop its approach, the requirements - in case of data migration it can also be a data mapping document where different transformation rules and logic and the mapping of attributes in the source and target database are listed, are frozen.

2. The QA team should be provided with the latest requirements (mapping documents) otherwise this will lead to major rework in the approach and SQL queries and automating programs.

3. QA team may not understand the transformation rules correctly incase of functional transformations.

4. The QA team must understand the relation ship between different attributes correctly else the SQL queries and automating programs will not give correct results and may eventually result in improper defect reporting.

Test Automation

When testing large databases, manual testing of the migration may prove to be in-efficient. Sampling of data can be done and a manual verification can be performed on this sample data, but this can lead to in-sufficient testing as a larger part of the database may be left untested. Thus automated tests should be performed to ensure that an error free migration is performed.

Automation involves using scripts which will do the job of comparing the data in the source and the target databases. The candidates for automation would be those fields where the number of records involved are large and also those where there is functional transformation taking place when the data is being migrated from the source to the target database.

Automation can be done by writing test scripts (i.e. programs) that compare the data present in the two databases. The script may be written in any programming language that is comfortable to the tester and which has the functionality to access both the source and target databases. The automated script merely has to retrieve the data present in the two databases and compare the values/records of each column/table of the source and target database with each other. The scripts will contain SQL queries that will retrieve the data present in the database. The complexity of these queries will depend on the database and also on the type of migration being performed. Data retrieved from the source and target databases should be sorted in the same manner. After the script retrieves the data, the script should perform a one to one comparison between the data elements retrieved from the source and target databases. The details of the comparison should be written into a file thus allowing the tester to verify if any errors were encountered during the comparison.

Data Warehouse Testing

2013-11-27T10:49:00.002-08:00

A data warehouse:

is a relational database that is designed for query and analysis rather than for transaction processing.
usually contains historical data derived from the transaction data, also data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.

3.is subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process.

Subject-Oriented: A data warehouse can be used to analyze a particular subject area.
Integrated: A data warehouse integrates data from multiple data sources.
Non volatile: Data once loaded is usually never changed.
Time-Variant: Historical data is kept in a data warehous

A data warehouse environment includes:

an extraction, transformation, and loading (ETL),
an online analytical processing (OLAP) engine,
client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.

Importance of Data warehouse:

A data warehouse is optimized to perform well for a wide variety of possible query operations.
A data warehouse is updated on a regular basis by the ETL process (run nightly or weekly) using bulk data modification techniques.
Data warehouses use de-normalized schemas (star schema) to optimize query performance.
A data warehouse query scans millions of rows.

Data warehouses stores years of months or data to support historical data.

Data warehouse Architecture:

Data warehouses and their architectures vary depending upon the specifics of an organization's situation depending on its data modeling, security, reference data management, staging area etc.

1. Data Warehouse Architecture (Basic):- Data from different sources are transformed in the data warehouse directly. This data, once processed is used by users for the reporting and analysis thereafter.

2. Data Warehouse Architecture (with a Staging Area):- Data from different sources get processed in a place called staging area before it reaches the warehouse.

3. Data Warehouse Architecture (with a Staging Area and Data Marts):- Data mart is created on top of the data warehouse to cater to different business function areas or geographies. The data that are accessed by end users in the front end applications are fed by the data mart.

Terminology used in Data Warehouse:

Meta Data: - Data that describes data and other structures, such as objects, business rules, and processes. A repository contains metadata.

Summary Data: - A Summary Data Report offers you data about workloads and filters, and then generates recommendations.

Data Mart: - A data warehouse that is designed for a particular line of business, such as sales, marketing, or finance. In a dependent data mart, the data can be derived from an enterprise-wide data warehouse. In an independent data mart, data can be collected directly from sources.

Staging Area: - A place where data is processed before entering the warehouse.

Data Mining: - Data mining (or data discovery) is the process of autonomously extracting useful information or knowledge (“actionable assets”) from large data stores or sets.

Different Types of Tables in Data Warehouse

Dimension table (lookup or reference tables.)

Dimension tables describe the business entities of an enterprise, represented as hierarchical, categorical information such as time, departments, locations, and products.

Fact Table
A table in a star schema that contains facts. A fact table might contain either detail level facts or facts that have been aggregated (fact tables that contain aggregated facts are often instead called summary tables).

Aggregation Table
The process of consolidating data values into a single value. The data can then be referred to as aggregate data. Aggregation is synonymous with summarization, and aggregate data is synonymous with summary data.

Level value table
A database table that stores the values or data for the levels you created as part of your dimensions and hierarchies.

Phases in testing of Data Warehouses:

Extraction Transformation & Loading:
Business functionality testing, Performance testing and Data Quality testing are required in this phase.

Data Load:
Configuration testing is required in this phase.

Data Loaded:
Availability / Performance testing, Security testing, Recovery testing and Data Quality testing are required in this phase.

Incremental Data Addition:
Regression testing required in the phase.

End to End Business Process:
Interoperability, API and Reports Testing are required in this phase

Data Warehouse Functional Validations:

Standard Validation

Data Validation: This evaluates and makes sure that a correctness of the data in the warehouse
Count Validation: This refers to running database queries on a given table or related tables to get the total count of a given transaction or business scenario
End to End testing: End-to-end testing here refers to validating the data as they pass various stages of the system

Business validation

Straight/Direct move: Data is directly moved from source to target system without applying any transformation rule
Data transformation: Validates whether the transformation is in line with the business rules applied to the source data base or not
Look up validation: Positive and negative test cases will be created to verify ETL process and lookup validations rules with valid and invalid data respectively.
Defaulting: The fields in Source (to be defaulted) having invalid values w.r.t. the requirements in Target get defaulted to some appropriate value during ETL run.
Signage: Various General Ledger amounts are assigned appropriate sign (based on certain rules) before being populated in Target tables.
Translation: Usually projects may need to translate the currency from one format to another.
Filtering: Some records have filtering criteria attached to them. The filtered out record will be left behind in the Source. It helps in maximizing the performance of the query in the database and retrieves only required data.
Average Balance Calculation: The average balance is usually computed every weekend or month end.
Data integrity validation: Testing to validate the exactness of the data stored. Testing conducted on a periodic basis as data get changed over a period of time in database.
External field validation: Validates whether the database is able to perform the same function/operation for newly added fields or existing fields in a table or not.

Data Warehouse Non Functional Validations

Load Testing –Incremental load, bulk load and full load
Performance testing
Volume testing

Testing Strategy followed in Data Warehouse Testing

Testing Strategies: - Customized test Cases -> Aggregation Validation -> Automation à Data Sampling -> 100% Verification

1. Data Sampling is applicable for Straight hold and homogeneous data. Pros: Time Effective is an advantage, Cons: Precision cannot be high.

2. Automating Data Warehouse is applicable for Straight loads that require exhaustive testing. Pros: High confidence level and more Coverage of scenarios. Cons: Heavily dependent on source target mapping. Higher elapsed time and COST poor customer buying. All scenarios cannot be automated

3. Customized test cases are applicable for Complex data load and hetero generous data load. Pros: Mitigation of business risks. Cons: Good business knowledge required

Challenges in Data Warehouse testing

Data Warehouse Testing needs to deal with large volumes of data
Complex transformation logic necessitates high skill levels to design tests with optimal coverage
Higher Cost of Quality as the impact of defect slippage is high
Scattered sources of input data and different frequencies of updates
High turnaround time leading to delay in time to market
Defining the scope of testing is very important in every project
In DWH testing projects, estimation is a big challenge
How to ensure data consistency during transfer from flat file format to dimensional and Identification of Negative Test Scenarios
Execution time, Huge error logs, Test result verification, Domain Knowledge and Knowledge on SQL
System Constraints:- Database Size, Slow performance and automation issues
Environmental Setup Challenges :- Setting up of Source Systems, Staging Areas and DWH and Setting up ETL mappings for the DWH
Critical Success factors of data warehouse testing

Data Quality

The right data populated in the database?
Is the data good enough for the customer to make a strategic business decision

Critical Business Scenarios

The current business scenarios have to be tested.
Future scenarios based on the business goals of the company need to be envisaged and tested.
Quantity/Size of the data migrated
One of the major issues in testing of migration project is the size of the data involved, which runs to millions of records especially if historical data load is present.

Surrogate Key

2013-11-27T10:36:00.001-08:00

A surrogate key may also be called as:

synthetic key
entity identifier
system-generated key
database sequence number
fact less key
technical key
arbitrary unique identifier

-It is the system generated key which cannot be edited by the user.
-It is the primary key of the dimension table in warehouse.
-It is nothing but the sequence which generates the numbers for primary key column.

Surrogate keys are system generated keys. They are integers.

Surrogate keys are extremely useful when having type 2 data (i.e. storing historical information)
For ex: Consider one has a table in which a person and his location are stored. Now when his location is changed and we want to keep a historical record of the same it is stored with a surrogate key that will help us to uniquely identify the record. This is also a reason that OLTP keys are not used in the warehouse and a separate dimension or surrogate key is maintained.

What is the data type of the surrogate key?

Surrogate keys are the system generated keys and used to eliminate the redundancy in the primary keys. So they are always integer numbers.

Generate surrogate key

Goal: Fill in a data warehouse dimension table with data which comes from different source systems and assign a unique record identifier (surrogate key) to each record.

Scenario overview and details : To illustrate this example, we will use two made up sources of information to provide data about customer’s dimension. Each extract contains customer records with a business key (natural key) assigned to it.

In order to isolate the data warehouse from source systems, we will introduce a technical surrogate key instead of re-using the source system's natural (business) key.
A unique and common surrogate key is a one-field numeric key which is shorter, easier to maintain and understand and independent from changes in source system than using a business key. Also, if a surrogate key generation process is implemented correctly, adding a new source system to the data warehouse processing will not require major efforts.

Surrogate key generation mechanism may vary depending on the requirements; however the inputs and outputs usually fit into the design shown below:

Inputs:
- an input represented by an extract from the source system
- data warehouse table reference for identifying the existing records
- maximum key lookup

Outputs:
- output table or file with newly assigned surrogate keys
- new maximum key
- updated reference table with new records

How to test the surrogate key?

usually test with the following way

1. total count = max(sgk) - min(sgk) +1
2. select sgk from table group by sgk having count(*)>1;

Defect Density

2013-11-27T09:43:00.002-08:00

Purpose:
Defect Density is used to detect the rate at which developers are generating defects

Definition
Defect Density is the number of confirmed defects detected in software/module during a defined period of development divided by the size of the software/module.

Formula
Defect Density = Defect/unit size

Unit size=Size is typically counted either in Lines of Code or Function Points.

Address validation on e-commerce application

2013-11-24T23:31:00.002-08:00

Address validation on e-commerce application

Let’s discuss on how we write test cases for address validation in an e-commerce application:

If fields are editable

If fields take maximum and minimum characters length

If all the button are functional

If address entered is standardized meaning correct and valid address has been entered

If address entered in front end is getting loaded @ table level

Can able to modify the address?

Can able to delete the address?

Phone number field validation (validation for # of digits and if it is allowing special characters or not)

These are the few test cases which came to my mind and these may get change depending on the functionality of the application

GUI Testcase Template

2013-11-23T10:44:00.003-08:00

I am sharing the testcase template for writing GUI based test cases.

The template may change a bit while writing the testcases for DWH based applications or web services based application.

Below template however is for GUI based applications.

This Test case Template consists three sheets

Revision History
Test Scenario
Test Cases

Revision History:

Details in this sheet will look like

Test Scenario

Details in this sheet will look like

Test Cases

Details in this sheet will look like

Details in this sheet will look like

As all the column names are self explanatory I am not describing each of these terms again.

Please let me know if I have to share more details on this.

Option	Description
User	Any User name for Data Validation Option.
Database Type	Type of database created for Data Validation Option repository. Select required database.
Database URL	Connection string to connect to the database. Update the string with the details of the Data Validation Option repository database.
	The following connection strings appear for the different databases:
	1. Oracle : jdbc:informatica:oracle://:;ServiceName=
	2. Microsoft SQL Server : jdbc:informatica:sqlserver://:;DatabaseName=
	3. IBM DB2 : jdbc:informatica:db2://:;DatabaseName=
Database User	Database user name.
Database Password	Database user password.

Property	Description
Name	Name of the repository. When you create a repository, enter a name that indicates whether the repository is a source or a target repository. When you edit a repository, you cannot change the name. The repository name is case sensitive.
Client Location	Location of the pmrep.exe file on the client machine. Typically the location is: C:\Informatica\\clients\PowerCenterClient\client\bin.
PowerCenter Version	The PowerCenter version.
PowerCenter Domain	The Informatica domain name.
Repository	The name of the PowerCenter repository.
User name	The user name for the PowerCenter repository.
Password	The user password for the PowerCenter repository.
Security Domain	The LDAP security domain. Leave this field blank if you use native authentication.
Contains Target Folder	Indicates whether the repository is a source or target repository. Select false if the repository you create is a source repository. Select true if it is a target repository. When you select true, you must specify the Target Folder, Integration Service, and Data Validation Option Results Warehouse Connection information.
Target Folder	The name of the folder that has been set up in the PowerCenter repository to store the mappings generated by the tests set up in Data Validation Option.
Integration Service	The name of the PowerCenter Integration Service.
Data Validation Option Results Warehouse Connection	The name of the PowerCenter connection to the Data Validation Option repository.

Testing

Differences between OLTP and OLAP

Differences between the Star and Snow flake schemas in Dimensional Modeling

DWH Staging Area Importance

Parameter Files in DVO

ETL Testing

Subject Oriented

Integrated

Nonvolatile

Time Variant

Informatica DVO

Benefits of ETL Testing with Data Validation Option

HOW TO: Enable debug logging in DVO================================

Test Scenarios to be considered during each phase of E-T-L process till Reporting

During Extraction Process

During Transformation Process

During Loading Process

During Data mart loading Process

During Reporting Process

Down Stream Feed

BIG DATA TESTING POINT OF VIEW

How to avoid problematic and unwanted connections while refreshing the Connections in DVO Repository

Maintain Multiple Data Validation Option instances

Maintain Multiple Informatica DVO instances ========================================

Enabling debug logging in DVO

HOW TO: Enable debug logging in DVO================================

Dimensional Modelling in DWH

Setting up an Oracle ODBC Driver and Data Source in 64 bit OS

Data Migration Testing Startegy

Informatica DVO installation steps

ETL Testing: How to Achieve 100% Data Validation (QuerySurge)

Data Migration Testing

Data Migration Testing

Types of Migration

Test Planning

Checklist for test Data Migration Testing

Data Migration Testing Life Cycle

Risks Involved

Test Automation

Data Warehouse Testing

Surrogate Key

Defect Density

Address validation on e-commerce application

GUI Testcase Template

HOW TO: Enable debug logging in DVO
================================

HOW TO: Enable debug logging in DVO
================================