Data Integration Solutions: ETL Design

Change Data Capture (CDC) Implementation for Multi Sourced ETL Processes

We have discussed couple of different options for Change Data Capture including a Change Data Capture Framework in our prior discussions. Implementing change capture for ETL process which involves multiple data source needs special care to capture changes from any of your data source. Here in this article lets see CDC implementation for ETL Process which involve multiple data sources.

Initial History Building Algorithm for Slowly Changing Dimensions

Building initial history for a Data Warehouse is a complex and time consuming task. It involve taking into account of all the date intervals from different source tables during which the source system’s representation of data in any of the tables feeding into the Dimension Tables. So we can imagine the history building complexity and the need of a reusable algorithm.

5 Restartability Design Pattern for Different Type ETL Loads

ETL Restartability design for informatica workflows

Restartable ETL jobs are very crucial to job failure recovery, supportability and data quality of any ETL System. So you need to build your ETL system around the ability to recover from abnormal ending of a job and restart. So a well designed ETL system should have a good restartable mechanism. In this article lets discuss ETL restartability approaches to support different type of ETL Jobs such as Dimension loads, Fact Loads etc...

SCD Type 1 Implementation using Informatica PowerCenter

Unlike SCD Type 2, Slowly Changing Dimension Type 1 do not preserve any history versions of data. This methodology overwrites old data with new data, and therefore stores only the most current information. In this article lets discuss the step by step implementation of SCD Type 1 using Informatica PowerCenter.

Design approach to Update Huge Tables Using Oracle MERGE

Design approach to Update Huge Tables in Informatica powercenter workflow

One of the issues we come across during the ETL design is "Update Large Tables". This is a very common ETL scenarion especially when you treat with large volume of data like loading an SCD Type 2 Dimension. We discussed about a design approach for this scenarion in one of our prior articles. Here in this updated article lets discuss a different approach to update Larger tables using Informatica Mapping.

SCD Type 3 Implementation using Informatica PowerCenter

Unlike SCD Type 2, Slowly Changing Dimension Type 3 preserves only few history versions of data, most of the time 'Current' and Previous' versions. The 'Previous' version value will be stored into the additional columns with in the same dimension record. In this article lets discuss the step by step implementation of SCD Type 3 using Informatica PowerCenter.

Change Data Capture (CDC) Implementation Using CHECKSUM Number

Typically we use a date column or a flag column to identify the change record for change data capture implementation. But there can be scenarios where you source do not have any columns to identify the changed records, especially when working with legacy systems. Today in this article lets see how to implement Change Data Capture or CDC for such scenarios using checksum number.

Data Cleansing and Standardization Using Regular Expression

Data Quality is one of the major priorities of any data warehouse or any data integration project. We use different tools for data quality and data standardization implementation. But tools may not be the right solution for small projects which involve couple of data feeds. Regular Expression is an alternative approach for such small projects. In this article lets discuss about data quality implementation using Regular Expression or RegEx in Informatica PowerCenter.

ACTIVE LookUp, To Unlock the Limitations of JOINER Transformation

Joiner Transformation can be used to achieve the functionality of SQL join Operation including full outer join. Additionally we can use Joiner to join data from heterogeneous data sources. But it is limited with the operators, which can be used in the join condition, it can use only 'equal to' operator in the join condition. In this article lets see how we can unlock this limitation using Informatica PowerCenter Active LookUp transformation.

11 Ways to Make Informatica PowerCenter Code Reusable

Reusability is a great feature in Informatica PowerCenter which can be used by developers. Its general purpose is to reduce unnecessary coding which ultimately reduces development time and increases supportability. In this article lets see different options available in Informatica PowerCenter to make your code reusable.

Update Without Update Strategy for Better Session Performance

You might have come across an ETL scenario, where you need to update a huge table with few records and occasional inserts. The straight forward approach of using LookUp transformation to identify the Inserts, Update and Update Strategy to do the Insert or Update may not be right for this particular scenario, mainly because of the LookUp transformation may not perform better and start degrading as the lookup table size increases.

User Defined Error Handling in Informatica PowerCenter

Error Handling is one of the must have components in any Data Warehouse or Data Integration project. When we start with any Data Warehouse or Data Integration projects, business users come up with set of exceptions to be handled in the ETL process. In this article, lets talk about how do we easily handle these user defined error.

Change Data Capture (CDC) Made Easy Using Mapping Variables

At times we may need to implement Change Data Capture for small data integration projects which includes just couple of workflows. Introducing a Change Data Capture framework for such project is not a recommended way to handle this, just because of the efforts required to build the framework may not be justified. Here in this article lets discuss about a simple, easy approach handle Change Data Capture.

An ETL Framework for Change Data Capture (CDC)

Change data capture (CDC) is the process of capturing changes made at the data source and applying them throughout the Data Warehouse. Since capturing and preserving the state of data across time is one of the core functions of a data warehouse, a change data capture framework has a very important role in ETL design for Data Warehouses. Change Data Capture can be set up on different ways based on Timestamps on rows, Version Numbers on rows, Status indicators on rows etc. Here we will be building our framework based on "Timestamps on rows" approach.

An ETL Framework for Operational Metadata Logging

ETL Framework for Operational Metadata Logging in Informatica powercenter

Quite often there is requirement to track runtime information about your ETL jobs such as record count, error count, job run time etc... In general it is a non-functional requirement, required by the IT team to have such information for reconciliation purposes, calculate performance statistics etc... It is important to have a framework, which can capture all the operational meta data you need with out adding too much time to your development cycle.

SCD Type 2 Implementation using Informatica PowerCenter

Slowly Changing Dimension Type 2 in Informatica powercenter workflow

Slowly Changing Dimension Type 2 also known SCD Type 2 is one of the most commonly used type of Dimension table in a Data Warehouse. SCD Type 2 dimension loads are considered to be complex mainly because of the data volume we process and because of the number of transformation we are using in the mapping. Here in this article, we will be building an Informatica PowerCenter mapping to load SCD Type 2 Dimension.

Design Approach to Ensure High Data Availability During Data Refresh

Many times we create snapshot tables and do build reporting on top of it. Refreshing the underlying table became difficult, if the report needs to be available 24x7 with out any down time. The refresh process can be a major design bottleneck, if in case the refresh takes hours. In this article lets discuss a design approach to make the target table data highly available.

Error Handling Made Easy Using Informatica Row Error Logging

Error Handling made easy in Informatica powercenter workflow

Every time we start with a Data Warehouse or Data Integration project we spent lot of time defining our error handling approach. Still there are good chances that we might miss some scenarios because of unexpected data issues. Here in this article, lets us discuss an approach to handle the unexpected error.

Popular Posts

Random Posts

Posts Being Viewed