Data Integration Solutions: Mapping Tips

How to Avoid The Usage of SQL Overrides in Informatica PowerCenter Mappings

Many Informatica PowerCenter developers tend to use SQL Override during mapping development. Developers finds it easy and more productive to use SQL Override. At the same time ETL Architects do not like SQL Overrides as it hide the ETL logic from metadata manager. In this article lets see the options available to avoid SQL Override in different transformations.

Surrogate Key Generation Approaches Using Informatica PowerCenter

Different Approaches to Generate Surrogate Key in Informatica PowerCenter

Surrogate Key is sequentially generated unique number attached with each and every record in a Dimension table in any Data Warehouse. We discussed about Surrogate Key in in detail in our previous article. Here in this article we will concentrate on different approaches to generate Surrogate Key for different type ETL process.

Time Zones Conversion and Standardization Using Informatica PowerCenter

When your data warehouse is sourcing data from multi-time zoned data sources, it is recommended to capture a universal standard time, as well as local times. Same goes with transactions involving multiple currencies. This design enables analysis on the local time along with the universal standard time. The time standardization will be done as part of the ETL, which loads the warehouse. In this article lets discuss about the implementation using Informatica PowerCenter.

Dynamically Changing ETL Calculations Using Informatica Mapping Variable

Quite often we deal with ETL logic, which is very dynamic in nature. Such as a discount calculation which changes every month or a special weekend only logic. There is a lot of practical difficulty in making such frequent ETL change into production environment. Best option to deal with this dynamic scenario is parametrization. In this article let discuss how we can make the ETL calculations dynamic.

Session Logfile with Verbose Data for Informatica Mapping Debugging

Troubleshoot Informatica Mapping Using Verbose Mode Session Log

Debugger is a great tool to troubleshoot your mapping logic, but there are instances where we need to go for a different troubleshooting approach for mappings. Session log file with verbose data gives much more details than the debugger tool, such as what data is stored in the cache files, how variables ports are evaluated. Such information helps in complex tricky troubleshooting.

Mapping Debugger to Troubleshoot your Informatica PowerCenter ETL Logic

Debug Informatica PowerCenter Mapping Using Debugger and Session Log File

Debugger is an integral part of Informatica PowerCenter mapping designer, which help you in troubleshooting the ETL logical error or data error conditions in an Informatica mapping. The debugger user interface shows the step by step execution path of a mapping and how the source data is transformed in the mapping. Features like "break points", "evaluate expression" makes the debugging process easy.

Dynamic Partitioning to Increase Parallelism Based on Resources Availability

Informatica PowerCenter session partition can be used to process data in parallel and achieve faster data delivery. Using Dynamic Session Partitioning capability, PowerCenter can dynamically decide the degree of parallelism. The Integration Service scales the number of session partitions at run time based on factors such as source database partitions or the number of CPUs on the node resulting significant performance improvement.

Informatica PowerCenter Pushdown Optimization a Hybrid ELT Approach

Informatica PowerCenter Pushdown Optimization an ELT Approach

Informatica Pushdown Optimization Option increases performance by providing the flexibility to push transformation processing to the most appropriate processing resource. Using Pushdown Optimization, data transformation logic can be pushed to source database or target database or through the PowerCenter server. This gives the option for the ETL architect to choose the best of the available resources for data processing.

Implementing Informatica PowerCenter Session Partitioning Algorithms

Implementing Different Informatica PowerCenter Session Partitioning Algorithms

Informatica PowerCenter Session Partitioning can be effectively used for parallel data processing and achieve faster data delivery. Parallel data processing performance is heavily depending on the additional hardware power available. In additional to that, it is important to choose the appropriate partitioning algorithm or partition type. In this article lets discuss the optimal session partition settings.

Informatica PowerCenter Partitioning for Parallel Processing and Faster Delivery

In addition to a better ETL design, it is obvious to have a session optimized with no bottlenecks to get the best session performance. After optimizing the session performance, we can further improve the performance by exploiting the under utilized hardware power. This refers to parallel processing and we can achieve this in Informatica PowerCenter using Partitioning Sessions.

Informatica Workflow Recovery with High Availability for Auto Restartable Jobs

Restartable ETL jobs are very crucial to job failure recovery, supportability and data quality of any ETL system. In one of our prior articles we discussed different design techniques for ETL restartability, independent of the ETL tool used. We can also implement restartability in an ETL job using Informatica PowerCenter workflow recovery capabilities. In this article lets see what is required to setup an informatica workflow for recovery.

Processing Multibyte Characters Like UNICODE Characters in Fixed Width Files

Working with Multibyte Data in Fixed-Width Filess

Processing fixed width file in Informatica PowerCenter is not a big deal. But this can be bit tricky if the fixed file contins both multi byte and single byte characters such as unicode characters. This is because of the fact that fixed width flat files are byte oriented, which means the field width is measured in bytes. So if a single character takes two bytes for its representation, it will be treated as width two. In this article, lets see how such files are handled in Informatica PowerCenter.

Pipeline Lookup Transformation, Beyond Relational and Flat File Data Sources

Pipeline Lookups, Beyond Relational and Flat FIle Data Sources

Lookup transformation is synonymous to SQL outer join, which is primarily used for relational and flat file data source lookups. But with the introduction of pipeline lookup, we can use all variety of data sources in the lookup transformation. This feature is really powerful just like Active Lookup Transformation. In this article lets see how pipeline lookup transformation can be configured in a mapping.

User Defined Functions in Informatica PowerCenter

Re-Runnability for ETL Processes Which Uses Mapping Variables

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. User Defined Functions is one among the reusability feature provided by Informatica PowerCenter. In this article lets understand User Defined Functions in detail.

Re-Runnability for Informatica ETL Processes Which Uses Mapping Variables

Informatica PowerCenter mapping variable can be effectively used to implement change data capture logic. The mapping variable is stored in the repository and its value is set to the new value only when the session execution is successful. This property makes the restartability of the ETL process easy. Since the last success point - variable value, is stored in repository, we cannot go back and reprocess an already processed data set. In this article lets see how we can handle overriding the mapping variable value.

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.

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.

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

Popular Posts

Random Posts

Posts Being Viewed