Tag Archives: Informatica

How to prepare for Informatica PowerCenter v9 Developer certification – Part2 (The Reading Material)

This post is in continuation to my previous post on how to prepare for Informatica PowerCenter v9 Developer certification,  in which I described the syllabus for the exam,

In this post I will describe how to get the reading material for the certification and which parts to study for an efficient and effective study.

Also, I would like to highlight that there is no substitute for hands on experience, and I would strongly encourage you to practice various transformations and concepts on the PowerCenter tools. You can install a copy of Informatica PowerCenter v9 for personal use by following my six part guide here.

With all the intro aside, lets dive straight into the topic at hand. Where to get the reading material and what to read.

Downloading the reading materials:- The product guides supplied with the product installation package is the main source of reading material. If you followed my guide on how to install informatica, you would have already received all the pdf guides supplied with the product.

or else you can also download the guides for v9.6..1 from the below link. You will have access to this site only if you have an email id of a corporation which is a partner of Informatica. If you belong to such a company you can register with your company email id.

https://mysupport.informatica.com/docs/DOC-14109

If you do not have such access you can try searching for individual pdf files on google and downloading them from the links shown by google which will be mainly from Informatica community website. Any 9.x.x version guide will be good enough for our preparation.

We will need the following 7 guides for the preparation

IN_961HF2_AdministratorGuide_en.pdf
PC_961HF1_DesignerGuide_en.pdf
PC_961HF3_TransformationGuide_en.pdf
PC_961HF3_TransformationLanguageReference_en.pdf
PC_961HF3_WorkflowBasicsGuide_en.pdf
PC_961HF3_AdvancedWorkflowGuide_en.pdf
IN_961HF3_CommandReference_en.pdf

Google them individually and download if you do not have access to myinformatica portal.

Which chapters to study?:-

Below is a breakup of each subtopic from the syllabus along with the chapters from the guides that we need to study.

Mapping Design Basics:- 45%

Screen Shot 2015-09-02 at 11.15.50 PM

Mapping Design Advanced:- 20%

Screen Shot 2015-09-02 at 11.16.05 PM

Others:- 35%

Screen Shot 2015-09-02 at 11.16.18 PM

This is the reading material and the chapters we need to study to prepare ourselves for the certification. Hope this is helpful to you.

Next Post(Part3) will be on Architecture and Administration overview and questions we can expect. You can write to me at raghavatalblog@gmail.com or follow me on twitter @atalraghav

 

How to prepare for Informatica PowerCenter v9 Developer certification – Part1 (The Syllabus)

In this multipart series of posts I will describe how to prepare for PowerCenter v9 Developer certification.

I will cover topics right from getting the syllabus to registering for the exam to preparing for it. It will be a big series which will cover the complete syllabus in depth with some sample questions.

There are two certification offered by Informatica in PowerCenter space – one is for Developers and one for Admins. This series will cover the syllabus for the Developer certification.

Part 1:- What is the syllabus and how to get it?

In order to get the syllabus for the Developer 9.x certification head over to the Informatica community website. From this page download the pdf PowerCenter DI 9x Dev Specialist Skill Set Inventory.pdf.

This 5 page pdf also called the skill set inventory by Informatica contains the information about the developer certification, the topics percentage breakup, question format and the Test topics that will be covered in this certification.

In summary

  • The test will consist of 70 multiple choice/multiple response/true-false type questions
  • It covers 7 broad topics of Informatica Powercenter product
  • Passing grade to achieve Certified Informatica PowerCenter Data Integration 9.x: Developer, Specialist status is 70%

Below are the broad topics and their percentage wise breakup that constitute the test

Screen Shot 2015-08-24 at 1.14.17 AM

For subtopics please see pages 3 and 4 of the skill set inventory we downloaded above.

In the next post I will list down how to get the required reading material for each subtopic and the pages and chapters we need to read to cover these topics. All this in Part 2.

Please subscribe to follow along.

How to generate a star pattern using Informatica with a Java Transformation

This post is in continuation to my earlier post where I described how to generate a star pattern without using a Java transformation. In this post I will describe the method to generate this pattern using Java transformation.

Just for the benefit of readers who have not read my previous post, below is the description of the problem which we will solve using a Java transformation this time.

Question:- To generate a star pattern using Informatica depending on the number of rows in the input file

Suppose there are 7 rows in the source file (any type of data),  the target should have 7 rows of stars in the following pattern:-

FinalStarPattern

Solution:- 

Solving this problem using java transformation is easier than the solution i presented without java transformation in my previous post here.

In this mapping also we will have one pipeline to get the row count of the file using aggregator and set it into a mapping variable.

Second pipeline will use a java transformation and some java code magic to generate the required pattern.

Below is the complete mapping screenshot

map_with_java

Mapping explanation:-

Pipeline 1:- To get the row count in the file. We will use an aggregator to get the count of the rows in the file and an expression to set this count into a mapping variable. Below are the screenshots of the aggregator and the expression used

agg_count

exp_set_var

Pipeline 2:- Here we use the row count, generate a row sequence in and expression and then pass these two values to the java transformation.

exp_sequence

Use Java transformation in Passive mode and create following input and output ports

java_count1

Use below code to generate the star pattern and connect the finalPattern port to target.

java_count2

The code snippet used above is pasted below:-

starpattern = "";
spacepattern = "";

for (int i =0; i < sequence ; i++){

starpattern = starpattern + “*”;

}

for (int j =0; j< rowcount – sequence; j++){

spacepattern = spacepattern + ” “;

}

for (int i =1; i < sequence ; i++){

starpattern = starpattern + “*”;

}

finalPattern = spacepattern + starpattern;

Well that is all about solving this problem using a java transformation.

let me know in comments if you need any clarifications.

How to install legitimate copy of Informatica v9.6.1 on windows 7 for personal use for free – Part 5

This is the fifth part of the 6 part guide which describes how to install Informatica PowerCenter 9.6.1 for personal use

  1. How to get and unpack the legitimate copies of the software packages (see Part 1)
  2. How to install Oracle 11g and create a database for Informatica repository and domain (see Part 2)
  3. How to install Informatica 9.6.1 server software (see Part 3)
  4. How to install Informatica 9.6.1 client software (see Part 4)
  5. How to create Informatica repository and integration services
  6. How to test whether the setup is working (see Part 6)

Click here for previous post which describes How to install Informatica 9.6.1 client software

In this post I will describe how to create and configure Informatica repository and integration services

This is where we get to login to the Informatica Admin Console

So lets get started!!

STEP1:- Open Informatica Admin console by going to following path

Start->All Programs-> Informatica 9.6.1 -> Server -> Informatica Administrator Home Page

If browser security stops the site from opening (which is hosted locally) you can bypass it and go ahead anyways. You will get below screen, where you will login using Administrator user and password which you created while installing the Informatica Server (see Part 3 – Step10)

Infa22

STEP2:- Creating a Repository Service. You will see the screen below, here click on Actions->New->PowerCenter Repository Service.

Infa23

 

STEP3:- Fill in the details as in the screenshot below and press Next

Infa24

STEP4:- Fill in the details as in the screenshot below and press Finish. Use the infa_rep schema password you created while installing Oracle 11g database. See Part 2 Step 10.

Infa26

This will create the repository service and start it in the exclusive mode. Keep the repository service in exclusive mode until we have created the integration service and are ready for connectivity from the client. Once we are ready we will recycle the repository service in Normal mode.

STEP5:- Creating Integration Service. Again go to Actions->New->PoweCenter Integration Service

Infa23

STEP6:- Fill in the details as in screenshot below and press Next

Infa28

STEP7:- In the next screen select the INFA_REP_DEV repository that we created in previous steps and provide Administrator username and password and press Finish

STEP8:- In the next dialogue box select the code page

Infa30

This will create and start the Integration service. Now we are ready to bring the repository service in Normal mode

STEP9:- Recycle the Repository service to start in Normal mode. Click on the repository service on the left hand many in Domain Navigator. Click the edit icon Repository properties and select Normal mode from drop down.

Recycle

 

Recycle2

 

This completes the setup of the repository and integration services. Now we are ready to connect the clients to the services and do a test run which I will describe in my next and final post in this series.

To Informatica Nirvana!!!

 

How to generate a star pattern using Informatica

Question:- To generate a star pattern using Informatica depending on the number of rows in the input file

Suppose there are 7 rows in the source file (any type of data),  the target should have 7 rows of stars in the following pattern:-

FinalStarPattern

Fig1
Solution:-
There are two ways of doing this – first is to use a Java transformation in passive mode and second is without using a java transformation.

Doing it WITHOUT a Java transformation is challenging hence I will describe this method in this post.  Java transformation based solution will be posted later.

Solution with Java Transformation

We can observe the following about the pattern we are generating:-
1. First line has 7 spaces before the star, second has 6 and so on. Hence the spaces in front are decreasing by 1 for each row. The number of spaces in first row is equal to the number of rows in the source file and number of spaces in front for the last row is zero

2. The number of stars are increasing by two after the first row. 1,3,5 and so on

As we are doing this without a java transformation, the trick lies in how we generate variable number of stars and spaces for each row.

Algorithm:-

1. First generate only the stars as in below pattern

Int1StarPattern

Fig2

2. Sort the above pattern in descending order to generate the pattern below

InvInt1StarPattern

Fig 3

3. Add spaces to the pattern, starting with zero spaces in first line and increasing it by one for each subsequent row to get the following pattern:-

InvFinalStarPattern

Fig 4

4. Sort again, this time in ascending order to get the final pattern

FinalStarPattern

Fig 5

The mapping will also include one pipeline to get the count of the rows in the file. This will be used in the main flow to generate the star pattern.

Complete mapping will look like below (click on the image to enlarge):-

CompleMappingIconic

Mapping Variable:- $$ROWCOUNT

Target Load Plan:- SQ_FF_TEST_SRC first (to count the number of rows)

Mapping Explanation:-

First pipeline to count number of rows in the file

1. Use an aggregator to count the number of rows in the file like below (agg_count_rows)

agg_count_rows

2. Use an expression transformation to assign the count value to a mapping variable (exp_set_row_count_variable)

exp_set_row_count_var

3. Connect both ports from the above expression to a dummy target file.

Second Pipeline to produce star pattern

1. Expression to get the row count variable value in the pipeline and also generate a sequence for each row starting from one (exp_seq_row_count)

exp_seq_row_count

2. Expression to generate intermediate start pattern as in Fig 2 above (exp_int_star_pattern)

exp_int_star_pattern

3. Sorter to sort the intermediate pattern in descending order (sort_desc)

srt_desc

4. Expression to add spaces to the sorted pattern (ex_add_spaces_to_pattern)

exp_add_spaces_to_patt

5. Sorter to again sort the pattern in ascending order to generate the final star pattern (srt_final_pattern)

srt_final_pattern

Place a source file with any number of rows, result you will get is lot of stars!!

Let me know in comments if you need any clarifications or if you have any feedback.

How to Load Only the first row and last row from a file to a target file using aggregator

This post describes another way of loading the first and last rows. This time using the aggregator transformation. Please see my previous post if you want to know how to do this using a Rank Transformation.(Using Rank Transformation)

Here we are going to use the default behaviour of the Aggregator transformation to get the last row from the file. In an aggregator if you do not specify any group by port it lets pass only the last row from the complete dataset.

Therefore create two pipelines from the source one to find first row similar to the way we did in the mapping with Rank transformation and second to feed an aggregator without any group by port to get the last row.

Use an Union transformation to combine the two dataflows and connect it to a target.

This will give you the simplest way of finding first and last row from a source file.

Let me know if you need any more information in the comments below.