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.

Cheers..
Rajesh

  • Rakesh

    Hi Rajesh,
    I was just browsing on google for some informatica scenarios and found your webpage with very good scenarios.I am learning informatica by myself and would like you to post simple scenarios on using mapping parameters and variables.Also if you get time please post a datawarehouse project like populating Dimension and fact tables from OLTP systems.

    Thanks,
    Rakesh

    • Rajesh

      Rakesh–

      Incremental loading is one if the frequent use-case for mapping parameters and variables.
      If you go to “Designer Guide > Mapping Parameetrs> Mapping parameters and variables overview” in the help guide, there is a pretty good overview.

      As for the data warehousing project , I’ll definitely consider posting some more examples specific to Dimension and fact tables in the future.

      Cheers!
      Rajesh.

  • shankar

    Hi Rajesh,

    Nice webpage with good practical scenarios. In coming articles if you do have time, please
    post articles related to pushdown optimization and partitioning in real time situations.
    Thanks for sharing your knowledge about Informatica.

    shankar

    • Rajesh

      Thanks Shankar. Articles on optimization (push-down, partitioning, pass-through…etc) are on my to-do list and I will get to them soon…:)

      Cheers,
      Rajesh.

  • Brijesh

    Excellent article, really appreciate the efforts.

  • Anupam Sen

    Were you able to create the Source-to-Target tool .. waiting for the tool to come out. I must say you are doing an excellent job by providing this Informatica details.

    • Rajesh

      Anupam,

      Since the last time I though about it, I learnt this can be done directly in the Data Modeling tool ERWin. This is even more convenient since the data model itself is usually in ERWin (at least at most places I have worked) and this feature lets you keep the model, Source To Target and all related definitions at one place. You can take a look at the implementation here.
      http://erwin.com/uploads/tips_techniques/Documenting_ETL_Rules_in_CA_ERwin.pdf

      -Rajesh.

  • sirisha

    The webpage is very helpfull Rajesh…..Its good for the persons like me who are new to informatica.

    Keep going….

    Seeing forward with new more things on informatica:)

  • If you’re looking for a general resource and introduction to business intelligence, check out this Business Intelligence 101 guide: http://blog.softwareadvice.com/articles/bi/beginners-guide-to-bi-software-1113011/.

  • Prajjakta

    Hi Rajesh,

    Awesome article indeed!! Really apprciate your put in sincere efforts! Keep doing the good work. Espacially for learners like me(us).

    Thanks, Prajjakta

  • Rakhee

    Hi Rajesh,

    It is really good information. I am Non It person and working as a Business Analyst since 1 year. But i want to start Career in Informatica. Can you please provide me some more information about it?

    • Rajesh

      Hi Rakhee – My advise wouldn’t probably help you a lot, since I haven’t been through a similar career path. All I can suggest is to try and get exposure in the same place that you are working and move to a more development oriented role. Most business analysts I know write a fair amount of SQL. Writing procedural code like PL/SQL and developing mappings should be easy to get started with if you know SQL. You already understand the business rules, that gives you a great edge when writing code.

  • Juan

    If you need a manual of Informatica PowerCenter, you see: http://www.jgarces.info/introduccion-a-informatica-powercenter/

  • Tutorial Blogs

    Nice article. Do let us know your opinion on http://free-informatica-tutorials.blogspot.com/ . A blog where we have tried collating the best-in-class guides to provide a full learning experience on Informatica products