Data Integration Solutions

SCD Type 6 Implementation using Informatica PowerCenter

In one of our prior articles we described the SCD Type 6 dimensional modeling technique. This technique is the combination of SCD Type1, Type 2 and Type 3, which gives much more flexibility in terms of the number of queries it can answer. But off course at the cost of complexity. In this article lets discuss the step by step implementation of SCD Type 6 using Informatica PowerCenter.

Use Informatica Persistent Lookup Cache and Reduce Fact Table Load Time

Use Informatica Persistent Cache and Reduce Fact Table Load Time

In a matured data warehouse environment, it is very common to see fact tables with dozens of dimension tables linked to it. If we are using informatica to build this ETL process, we would expect to see dozens of lookup transformations as well; unless any other design techniques are used. Since lookup is the predominant transformation, turning this will help us gain some performance. Lets see how we can use persistent lookup cache for this performance improvement.

SCD Type 6, a Combination of SCD Type 1, 2 and 3

Slowly Changing Dimension Type 6 a Combination of SCD Type 1, 2 & 3

In couple of our previous articles, we discussed how to design and implement SCD Type1, Type 2 and Type 3. We always can not fulfill all the business requirements just by these basic SCD Types. So here lets see what is SCD Type 6 and what it offers beyond the basic SCD Types.

Tasks and Task Developer in Informatica PowerCenter Workflow Manager

The Informatica PowerCenter Workflow Manager contains many types of tasks to help you build workflows and worklets. You can create reusable tasks in the Task Developer. Or, create and add tasks in the Workflow or Worklet Designer as you develop the workflow. In this article lets see very commonly used Tasks for Workflow or Worklet development.

Re-Keying Surrogate Key For Dimension & Fact Tables. Need, Impact and Fix

A surrogate key is an artificial key that is used as a substitute for a natural key. Every surrogate key points to a dimension record, which represent the state of the dimension record at a point in time. We join between dimension tables and fact tables using surrogate keys to get the factual information at a point in time. In this article lets see the need of surrogate key re-keying, the impact of re-keying and possible fix.

Data Manipulation Using Update Strategy in Informatica PowerCenter

It is obvious that we need data manipulation such as Insert, Update and Delete in an ETL job, Informatica PowerCenter provides Update Strategy transformation to handle any such data manipulation operations. Lets understand Update Strategy Transformation in detail.

Stored Procedure Transformation to Leverage the Power of Database Scripts

A Stored Procedure is an important tool for populating and maintaining databases. Since stored procedures allow greater flexibility than SQL statements, database developers and programmers use stored procedures for various tasks within databases. Informatica PowerCenter provides Stored Procedure Transformation to leverage the power of Database Scripting. In this article lets see it in more in detail about how to use Stored Procedure Transformation.

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.

Sequence Generator Transformation for Unique Key Generation

The Sequence Generator transformation generates numeric values in a sequential order. Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers. In this tutorial lets see a practical implementation of Sequence Generator transformation.

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...

Informatica Source, Target Command Makes File Processing Easier than Before

Source, Target Command Makes File Processing Easier than Beforen

Most of the time when we process flat files in Informatica PowerCenter, we do some kind of file pre or post processing, such as unzip the source file, create a custom header or footer for the target file etc. Such processing is normally done using Unix or Windows scripts, which is called using pre or post session script. Now Informatica PowerCenter has provided Source, Target Commands to make such processing easy than before.

Working with Router Transformation and Aggregator Transformation

Working with Aggregator and Souter Transformation

This tutorial shows the process of creating an Informatica PowerCenter mapping and workflow which pulls data from multiple data sources and use Aggregator and Router Transformation. Router transformation can be used to split the data into different groups. And aggregator can be used to summarize data.

Working with Aggregator and Sorter Transformation

This tutorial shows the process of creating an Informatica PowerCenter mapping and workflow which pulls data from multiple data sources and use Aggregator and Sorter Transformation. Using a sorter transformation, you can sort data either in an ascending or descending order. And aggregator can be used to summarize data.

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.

Informatica PowerCenter Repository Contents Upgrade

Informatica PowerCenter Repository Upgrade

After an existing Informatica PowerCenter server binaries are upgraded to a higher version, we will have to upgrade the existing repository contents before we can enable the repository service and access the repository objects such as mappings, sessions, workflows etc... from the client tools. This article illustrates the step by step instructions for the upgrading the Informatica PowerCenter repository contents.

Informatica PowerCenter Constraint Based Load Ordering

Informatica PowerCenter Constraint Based Loading

Constraint based loading technique is available in Informatica PowerCenter since couple of its prior versions. This PowerCenter feature will let you load multiple tables in a single session, which is having database level primary key - foreign key constraint or parent - child relation. In this article let's see what is needed to set up a session for constraint based loading.

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.

Informatica PowerCenter Repository BackUp and Restore

Change Data Capture Implementation Using CHECKSUM Number

Informatica PowerCenter administrators regularly backup the repository contents to prevent any data loose due to hardware or software problems. When the repository contents is backed up, it saves all the contents as a binary file which includes all the repository objects such as mapping, sessions, workflows etc. These binary files can be used to restore the contents if in case of any failure. In this article lets discuss the step by step process to backup and restore a PowerCenter Repository.

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.

Popular Posts

Random Posts

Posts Being Viewed