Getting started with Informatica PowerCenter

One of the readers posted a comment as to how to get started with the Informatica Power Center. I’ve seen it asked a lot of times and I hope to answer it here. If I have missed anything, kindly comment below and I’ll update the post as needed.

How to get Started

Understand SQL : I am assuming you have some database background and familiarity with SQL, other wise it is better to first learn SQL and queries (joins, sub queries, constraints, primary keys..etc) before starting with ETL. Some people argue that this is not necessary, but you just cannot go far in your career as an ETL developer if you don’t understand SQL and (at least one) underlying RDBMS. As to what is the best one to learn, pick whatever is the RDBMS you’ll be working on next (in your company). Oracle would be my choice as I started out as an oracle developer, but it depends on which one will be the most relevant for your case.

1. Get access to Informatica : The first (obvious) thing to do would be to download and install Informatica. Nicolas Gerard has some very good posts here. Install the database, Informatica Server and Client tools.

2. All the documentation for Informatica is in the Help file. Go to any of the client tools and Click on “Help>Contents” to see the entire list of User Guides for each of them.

3. For Developer Stream, these are the guides that will be the most useful. You should probably go through them in this order, but it is not a hard-and-fast rule. It’s just the way I would go through them if I were to start now.

Getting Started Guide : Go through each of the tutorials given (Pay close attention to the Product Overview and the clients section) , create and run the mappings, sessions and work-flows. Running the work-flows and seeing the session and work-flow log will hep you get your feet wet before you move on the architecture and advanced topics.

Designer Guide : More information on Sources, Targets, Mappings, Parameters and Variables. The mapping wizards and type-1, type-2 dimension tables will give you a good idea of how things are implemented and how all of these components fit together. You’ll see how transformations are used and what they do.

Transformation Guide : Another very important guide. Transformations are what you use day-in day-out in Informatica and you should get familiar with most of them to be able to pick whatever is needed for your task.

Transformation Language reference : This is more of a reference, but the functions section is something you should at least browse through a couple of times. You don’t have to remember the syntax , but you should know the existence of functions like DECODE, IIF, GET_DATE_PART and so on. You can always come back to the documentation to look it up.

Workflow Administration Guide : Despite the name, this guide is one of the most important one for developers. All the mappings that you create are “logical” that shows the data flow, but you give all the connections and design the actual ETL Processes using the work flow manager. This guide gives you all the necessary details. Once you know how to create a session task and a work flow, you can keep coming back to this guide as needed later on.

Performance Tuning Guide : Probably not when you get started, but once you develop some mapings and workflows, this is the place you’l come back to, if any of the flows take longer than expected and you need to tune them.

4. Write some Code (Develop mappings) : One you get familiarity with these, you should try and implement as many Data Warehouse scenarios as possible using Informatica to get really familiar with the tool. Some of them would be…

i) Slowly changing dimensions (look at the wizards for reference)
ii) truncate-load
iii) one work flow waiting on another (work flow dependencies)
iv) Loading Fact and Dimension Tables

and so on.

You can come across these scenarios in your project, on discussion boards like the IT-Toolbox, or any good ETL and Dimensional modeling books like the ones below by Ralph Kimball and his team .

The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
The Data Warehouse Lifecycle Toolkit

Go through the different stages of ETL cycle and different kind of models and try to implement them using Informatica.

For Administrators

I haven’t really worked an Informatica administrator except as a back up , But from the little experience I have , these are the things that you should go through to get started if you are looking for a career on the administrative side.

i) Administrator Guide
ii) Installation and Configuration Guide
iii) Power Connect Guide (based on the external environment that you have)
iv) Repository Guide (all deployments to higher environments)
v) Command Line reference

In most places I have seen, there is a common team for both database and Informatica administration. You would be a lot better off if you understand the underlying database concepts, administration, installation and recovery concepts.