INFORMATICA FAQs

1) I have a table like

empid       ename           year      month       sal

1             x              98        jan         500

1             x              98         feb       500

1             x              98        mar        500

1             x              99        jan         600

1             x              99        feb         600

2             y              98        jan         600

2             y              98        feb         600

2             y              98        mar         600

2             y              99        jan         700

2             y              99        jan         700

and so on i want to find out totsal for every emp on year wise

plz help me.

A)Use an aggregator where you need to check the group by

Port as emp id, ename and year.

2) What is a candidate key?

A1)A candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data(unique). Each table may have one or more candidate keys. One of these candidate keys is selected as the table primary key else are called Alternate Key.

A2)A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

3) Data is passed from one active trans and one passive trans into a passive transformation. Is the mapping valid or invalid?

A) Invalid (mapping validation rules)

4) what is shortcut in informatica? Difference between shortcuts, reusable object?

A) Short cut is a referenced object. We cannot edit on short cut objects. Shortcut objects are present under shared folder. Mostly sources and targets will be the shortcut objects. Reusable object can be used multiple times in a mapping.

5) If the source has duplicate records as id and name columns, values: 1 a, 1 b, 1 c, 2 a, 2 b,the target should be loaded as 1 a+b+c or 1 a||b||c, what transformations should be used for this?

A) We need to use sorter, Expression and aggregator transformations to do this

1. Sort by ID

2.Take 2 variable ports one for id and one for name and store the values of id and keep on comparing with current id i.e. variable is having previous id. so,if previous id=current id then (variable name)||name otherwise only name. Assign the variable name to output port

3.Use aggregator and use last or max(len(name)) to get the result

6) How many repositories can v create in informatica?

A)2 types of repositories can be created which are standalone, Shared or Global repository.

7)Please let me know how we can implement the below scenario. In a single mapping, more than 500 sources(legacy,VSAM,relational) will be loading into only one target. Whenever I retrieve the data (any record) from target, i need to find the details that the record belongs to which source.

A1) if u r using informatica 8.0 n above u has a option to load the name of the file. This is in the sq itself just check the currently processed file name box. Remember this will give u the complete path of the file, hence u need to sub string it before loading it to target.

A2) Use 1 mapping and multiple sessions which are associated with the same mapping and use mapping parameters by passing file name as parameter to include in data_source_name audit column.

8) If we are using an aggregator but forget to mention the group by port .what will be the output?

A) It returns the last row.

9) why do we go for update strategy tr in SCD rather using the session properties?

A1)Session Properties like pre Source Rows INSERT,UPDATE,REJECT,DELETE ,,

Using Session Properties We can do single flow only.SCD applicable for Insert, Update,,at a time using Update Strategy trans only. Using Update Trans we can create SCD mapping easily.

A2) Actually it’s important to use a update strategy transformation in the SCD's as SCDs maintain some historical data specially type 2 dimensions. In this case we may need to flag rows from the same target for different database operations. Hence we have no choice but to use update strategy as at session level this will not be possible.

10)Why we need XMLs?

A1)Now a day’s all devices like Mainframes, PDA's, personal comp (desktops, laptops, handheld devices (smart devices) are connected to a huge amount of network and they all communicate with a common language called XML. It is not new version of HTML and is recreated right from the scratch. So internally many technologies like .NET, Java and many others communicate in the form of XML. The XML is  error free language and contains user defined tags. Can be created on any platform, language and environment and can be run on any of the above said things. for more details

Please watch www.w3cshools.com

A2)XML is much similar to HTML, with some added features. XML is used for storage and transport of data, where as HTML is used to display the data. XML is used to transfer the structured information over the web. XML is easy to use, its flexible. In xml we can create our own tags. XMl is independent of the platform. XML is an emerging technology, which is having bright future in next generation web applications.

11) why sequence generator should not directly connected to joiner transformation ?

A) Mainly sequence generator is used to generate an unique id dynamically. We cannot join this number against any column in other tables...So.. We cannot connect sequence generator with joiner also, Main reason is joiner is a Active transformation. Means it can alter the number of rows. So, if u connect sequence generator with joiner the outcome sequence will not be proper.

12)From Source 100 rows are coming, on target there are 5 m rows which options is better to match data.

1)Joiner

2)No cache

3)Static

4)Dynamic

A1) using the joiner is right I have faced similar kind of scenario in my project. if u use static it has to cache all the 5m records and then compare the each incoming records which will degrade the performance, hence designate the master record with less no of rows in joiner which is a good practice.

A2) Since target side  records are very huge  compared to the source, caching is not preferred. so No caching. Again with no caching we can accomplish this in 2 ways

 a) By Joiner

 b) Unconnected lookup but as unconnected lookup is not in answers is in the option, Joiner is the answer.

When u wants to update the target then we should use DYNAMIC cache

13)I have Employee table, 10 cols are connected to next transformation, only 4 cols are selected in SQL override. What would be passed to next column.

A1) No rows from the source qualifier transformation. It gives an error like" query should return exactly 10 fields to match fields projected from the source qualifier." . Because number of rows selected in sql override must equal to number of rows connected to the next transformation from source qualifier. If you ignore to validate sql query in source qualifier transformation mapping validates but at work flow level session fails with as error"variable not in select list......."

14)Can anyone explain me step by step how scd will work ?

A1)Selects all rows. Caches the existing target as a lookup table. Compares logical key columns in the source against corresponding columns in the target lookup table. Compares source columns against corresponding target columns if key columns match. Flags new rows and changed rows. Creates two data flows: one for new rows, one for changed rows. Generates a primary key for new rows. Inserts new rows to the target. Updates changed rows in the target, overwriting existing rows.

15) How can we load first and last record from a flat file source to target?

A1)We can use below configurations to achieve desired output.

Src-SQualifier-->

                        |

                        Agg trans without group clmn         -->Trgt

                                      --->

                        Filter with nextval=1 condition      -->Trgt_instance

                        |      

Sgen(NEXTVAL)-->

A2)Alternatively we can use the UNIX Pre-session command to read the 1st and last record from the file and place it in another file and use that new file as source to load the target table.

16)what is diff between grep and find?

A)grep is used for finding any string in the file.

syntax - grep <String> <filename>

example - grep 'compu' details.txt

display the whole line,in which line compu string is found.

find is used to find the file or directory in given path,

syntax - find <filename>

example - find compu*

display aal file name starting with compu

17)Diff  B/W 

MAP Parameter,

SESSION Paramater,

DataBase  connection session parameters.?

Its possible to Create 3parameters at a time?

If Possible which one will fire FIRST?

A1)A mapping parameter is set at the mapping level for values that do not change from session to session for example tax rates. Session parameter is set at the session level for values that can change from session to session, such as database connections for DEV, QA and PRD environments. The database connection session parameters can be created for all input fields to connection objects. For example, user name, password, etc. It is possible to have multiple parameters at a time. The order of execution is wf/s/m. We can pass all these three types of parameters by using Parameter file. We can declare all in one parameter file.

18)How to run two workflow(not a sessions) sequentially, what is the process. Plz explain detailed information.

A1) The best way is obviously to run WF1 and then call WF2 using PMCMD command in the last session of WF1(as a post session task).If you absolutely want to ensure that the second wf starts only after graceful completion of wf1 then simply add a command task for the pmcmd and use the pipe to validate that the previous task is completed properly.

A2)1. Create a KSH to call (pmcmd start workflow) two work flows one after another (or two statements in ksh). And if required, I can check successful run of previous workflow before running another.

2. Make use of command object in second work flow. This object will be based on ksh which can wait on file created by successful run of first work flow

3. Make use of even wait task as file watcher and watch a file created by first work-flow.

4. Crude way: Determine work-flow execution time. Schedule

two work-flows by considering execution time difference.

19) What is the monster dimension please giving me one example.

A) Monster dimensions require special handling and advanced planning, especially when you’re dealing with rapidly changing monster dimensions. Rapidly changing dimensions (i.e., dimensions whose content changes as a result of input errors or changes over time that you’re recording in place) aren’t too much of a challenge when the dimensions are small. However, monster dimensions consume large amounts of maintenance time and CPU cycles because of their size, so you need to plan carefully when you’re dealing with them. Let’s explore two different methods for managing rapidly changing monster dimensions: first, splitting the dimension, and second, creating a set of non-overlapping value ranges for each rapidly changing attribute and then creating rows that include all possible range combinations.

20) Which gives the more performance when compare to fixed width and delimited file? and why?

A) fixed width, because there are no delimiters to check so the performance will increase.

21) What are the challenges of Data warehousing in the future?

A) I think as we move into the future, the historical data size increases which impacts the performance for complex calculations or aggregations. Which also impacts at report level to drill down.

22) When we go for unconnected lookup transformation? And why?

A) When no of records are less and single return value then go for unconnected lkp, which improve the performance.

23) After we make a folder shared can it be reversed? Why?

A) Folder can no't be Reversed to previous status of non sharable.

24) How to list Top 10 salary, without using Rank Transmission?

A) Use sorter transformation with ascending for sequence and filter. If it is flat file ur answer is write, if it is relational source then go to source qualifier properties there u write the query like

 select distinct a.* from t1 a where 10=(select sal from t1 b where a.sal>b.sal)

 i think it is working.

25)How to join the two flat files using the joiner t/r if there is no matching port?

A)yes you can join two flat files using the joiner t/r even if you don't have any matching port for that you need to take one dummy column in source files and based on the dummy column you can join them.

26)I'd like the load to be triggered by client. By placement of a file or somehow. How is it done in Informatica? I'm using version 7.1.4.

A1)you could use an event wait task in the work flow as the first  task. Once the event wait task detects the file in the specified folder all other sessions tasks will run. Alternatively you could use UNIX command in the command task.

A2) You can use a pmcmd command to trigger the workflow using a shell script. You can also use the event wait task described in the above answer. Alternately you can use an UNIX script to work as an active listener. This will trigger the work flow once the presence of file is detected in a specific directory. A scheduler tool also will be useful. If you could describe more about your requirement one can add more thoughts into it.

27) what target override? what advantages it has compare to target update?

A1)Target Update (by using Update strategy) will work only when we have the Primary key in Target. Update override will happen based on this key. If we don't have the Primary key Update override will not work. To overcome the above issue...If our Target is not having any key column or if we want to update the Target based on a column other than Primary Key column then we need to go for Target Update override option available in Target properties.

28) what is cdc?how to use it in creation of mappings?

A)CDC is used when you want to pull the records which have changed or newly added in the OLTP system. Not always necessary to be a Date field. When there are 5 key columns and you want to check if any of them changed and would like to extract only those records with these 5 key column values updated then you will have use this concept of change data capture.

29) If we are using aggregator we enabled sorted input but the records are not sorted what happen?

A) This technique is used when you want a performance boost. There will be an aggregator cache created and will be indexed. Although you have not given a sorted input it will still work fine as it is indexed in cache. Please note this is not like a page fault where you have to bring the page from the secondary memory when not found in primary memory.

30) What is sorted input option in Aggregator transformation.

A)If you use sorted input to reduce the use of aggregate caches, you must presort data by group (as defined by the group by ports). To presort the data by group, add a Sorter transformation to the mapping.

31)Draw a workflow diagram for this situation. If s3 is true session s4 and s5 should concurrently Run.if any one s4 or s5 true it should run s1.If s1 success it should execute s2 otherwise exit. can anyone give me suggestion.  

A) 1.Create S3.

2. Connect it to S4 and S5 parallel. Check if PreviousTaskStatusS3 = succeeded in the connecting link.

3. Once S3 is successfully executed it will start S4 and S5

4. Connect s4 and s5 to session s1 and Check if PreviousTaskStatusS3 =

Succeeded in the connecting link and in general tab of session choose the option treat input rows as 'OR'  .

5. This will allow you to move ahead incase either of S4 or S5 is succeeded.

6.Connect it to S2 using the same PreviousTaskStatus = succeeded in the link.

32)How can we update without using update transformation is push down operation in informatica.

which lookup gives more tuning performance. if so why.

A1)without using update transformation also we can update. in session properties select update against treat source rows as. this would definitely help you.

push down is a function of 8.1 version of informatica. It reduces the load on informatica server.

unconnected obviously bcoz not connected with the data flow and uses only static cache. Also it can be called as many times in a mapping as a result of an expression.

A2)Just using the Update override won't get your work done. You will have to select the session property Treat source rows as 'Update'.

Push Down Optimization is an optimizing technique in informatica 8. Integration service pushes the transformation logic either to the source database or to the target database rather than executing the logic by itself.

33) I have encountered a NULL character in a delimited text input file while extracting data using Informatica 8.1.1. Can anybody please help as to how I can deal with this error? And also please tell me what the NULL character is ?

Answer:- A null character is x00 (hexidecimal). You need to use a pre-process script to remove them prior to reading the file into INFA. A null character is not a NULL value as others are implying.

A1)As Nick explained, Null character (x00 - hexadecimal) is not a Null value. You can write a pre-session unix command or script to remove all Null characters.
tr -d "\x00" < mainfile > newfile
or
sed 's/\x00/ /g' mainfile > newfile

Use new file as source in your mapping.

A2)Jatin's solution is common design pattern where there is a ?low-level? Pre processing step prior the the applicaton's data ETL functions.

I would add that in validating pure text (or any external files outside the firewall,) we used to at least have a "plan" regarding all control characters as well as characters with an ASCII value above decimal 250.

It is interesting that various platforms and applications have their idiosyncratic sensitivities to certain characters or operations that do not initially seem to have simple basis.

On our platform, keyboarding errors and dirty data could introduce both disabling printer escape sequences and critical database framing errors (via our dreaded ASCII 255 segment mark) so this was very important. Less serious corruption could result from embedded column and row separator characters.

Though this may sound a bit overboard it is very similar to the input validation and security checks performed for web page forms as a matter of course. All input needs validation and these da

In this case x00 null issues could be said to trace back to the early days of C. NULLs and null value issues can also be a confusing quagmire for SQL newbies with a similar long history that was more or less competently addressed.
I was wonder if anyone in the group might give a few on topic related comments regarding UNICODE?
I do not mean to hijack Sujay Sasalatti's thread but I believe the question was answered quite precisely.

A3)In this case x00 null issues could be said to trace back to the early days of C." as David mentioned is the root of the problem why INFA cannot deal with them. A null character in C and Java (INFA is written in both) is an end-of-string marker for a variable length string.

INFA version 7.x and earlier did not warn about null characters. What would happen instead is a port value that had an embedded null character would be truncated. And, until you understood what was going on, it was very frustrating because you could see the entire string when examining the source file through the browse pane, but only the truncated value when looking at the port in the debug window.

34)Is it possible to give oracle hints while executing sql's in Session pre or post session sql's
Below is the example.
Select /*+ index(abc) */ * from temp_table.
I see that Informatica is ignoring the hint /*+ index(abc) */ and is going for a Full table scan.
Any solution or ideas on how to get over the issue, to make it use index using hints.

A)You can use hints under SQL over ride in the source qualifier transformation.
You cannot use it under pre session and post session command. No effect.

35) How can we load first and last record from a flat file

source to target?

A1) We can use below configurations to achieve desired output. We need 1 SQ, 1 SGen, 2 Rank and 2 Trgt Defn(same Trt). I think it will be cheaper.

Src-SQualifier-->

                        |

                        Rank1(Top 1st)          -->Trgt1

                                      --->

                        Rank2(Bottom 1st)       -->Trgt2(same instance)

                        |      

SGen(NEXTVAL)-->

A2) Alternatively we can use the Unix pre session command to read the 1st and last record from the file and place it in another file and use that new file as source to load the

target table.

36) The question is "Data is passed from one active trans and one passive trans into a passive transformation. Is the mapping valid or invalid?"

A1)Mapping is invalid. We can never connect a data from Active and passive transformation TO A PASSIVE TRANSFORMATION. Can you connect output of a joiner and

Expression to an expression transformation?

A2)No, it is not possible to move data from ACTIVE & PASSIVE to PASSIVE. Either both transformation should be ACTIVE or PASSIVE in pipeline.

A3)for eg,when v connect filter transformation (active) and exp trfn (passive) to exp trfn.v get a notification saying "not allowed".

37)If  we are using an aggregator but forget to mention the group by port .what will be the output?

Answer:-No group by Port-The AggTrans will check for group by Column port up to last column.  No group by port was find, The AggTrans will return the last row only. Every Column row will check for Group by port done or not,  up to last row. Finally (By default) last row will return. Last Row still waiting for Group by port.,,,,That's why Group by port is must.

Answer:-It will behave like a passive transformation.  the rows are passed without any aggregation.

38) Explain about HLD and LLD ?

A1)HLD: It refers to the functionality to be achieved to meet the client requirement. Precisely speaking it is a diagrammatic representation of client’s operational systems, staging areas, dwh n data marts. also how n what frequency the data is extracted n loaded into the target database.

LLD: It is prepared for every mapping along with unit test plan. It contains the names of source definitions, target definitions, transformations used, column names, data types, business logic written n source to target field

matrix, session name, mapping name.

A2) For people who have been involved in software projects, they will constantly hear the terms, High Level Design (HLD) and Low Level Design (LLD). So what are the differences between these 2 design stages and when are they respectively used?

High – level Design gives the overall System Design in terms of Functional Architecture and Database design. It designs the overall architecture of the entire system from main module to all sub module. This is very useful for the developers to understand the flow of the system. In this phase design team, review team (testers) and customers plays a major role. For this the entry criteria are the requirement document that is SRS. And the exit criteria will be HLD, projects standards, the functional design documents, and the database design document. Further, High level design gives the overview of the development of product. In other words how the program is going to be divided into functions, modules, subdivision etc.

Low – Level Design (LLD): During the detailed phase, the view of the application developed during the high level design is broken down into modules and programs. Logic design is done for every program and then documented as program specifications. For every program, a unit test plan is created. The entry criteria for this will be the HLD document. And the exit criteria will the program specification and unit test plan (LLD). The Low Level Design Document gives the design of the actual program code which is designed based on the High Level

Design Document. It defines internal logic of corresponding sub module designers are preparing and mapping individual LLD’s to Every module. A good Low Level Design Document developed will make the program very easy to be developed by developers because if proper analysis is made and the Low Level Design Document is prepared then the code can be developed by developers directly from Low Level Design

Document with minimal effort of debugging and testing.

40) What is checksum terminology in informatica? Where do you use it ?

A)Its a validation rule If the data is altered outside the company firewall, the checksum will automatically detect the violation and deny validation of the data

41) what r the transformations that are not involved in mapplet?

A1)1.Normalizer 2. XML Source Qualifier 3.Target Definitions 4.Mapplet with in a Mapplet

A2)1.Normalizer transformations 2.COBOL sources  3.XML Source Qualifier transformations  4.XML sources  5.Target definitions  6.Other mapplets  7.Pre- and post- session stored procedures

42) How do u get the first record from 50,000 records ?

Answer:- use sequence generater after that filer it out from 1 to 50000 by using filter transformation with condition >= 50000. Then you will get records from 50000 only.

43) How can you eliminate duplicate values from look up without overriding sql?

A1) lookup itself eliminate duplicate rows by having options like First Value, Last Value. So whenever there is more than one row for matching look up condition then it gets eliminated by first value. Last Value option. Let me know if I am wrong.

A2)The main functionality is to avoid duplicate rows by selecting first or last value only when the record coming from the source has multiple matches with the look up cache.

44) By using Filter Transformation, How to pass rows that does not satisfy the condition (discarded rows) to another target?

A1) write opposite condition what mentioned in filter transformation1 in another filter t.f and pass the rows which are not satisfying filter1 but satisfy 2 in to target2

A2) Connect the ports of the filter transformation to the second target table and enable the 'FORWARD REJECTED ROWS' in the properties of the filter transformation. the rejected rows will be forwarded to this table.

45)I have Employee table, 10 cols are connected to next transformation, only 4 cols are selected in SQL override. What would be passed to next col.

A) no rows from the source qualifier transformation. It gives an error like" query should return exactly 10 fields to match fields projected from the source qualifier." . Because number of rows selected in sql override must equal to number of rows connected to the next transformation from source qualifier. (Murali—test it)

46) TWO FLAT FILES ARE THERE , EACH HAVING NO MATCHING COLUMNS . HOW CAN U JOIN THESE TWO USING JOINER TRANSFORMATION ?

A) If there is no matching column two flat files, that means two tables are different,no need to join the source definitions in the designer. There is possibility to join them. Create common columns in both expression transformations.

        seqgen

           |

sd-->sq-->expr

                 Joiner-->td

sd-->sq-->expr

            |

         seqgen

47) What is the method of loading 5 flat files of having same structure to a single target and which transformations I can use?

A1) This can be handled by using the file list in informatica.If we have 5 files in different locations on the server and we need to load in to single target table.In session properties we need to change the file type as Indirect.am taking a notepad and giving following paths and file names in this notepad and saving this notepad as emp_source.txt in the directory /ftp_data/webrep/

/ftp_data/webrep/SrcFiles/abc.txt

/ftp_data/webrep/bcd.txt

/ftp_data/webrep/srcfilesforsessions/xyz.txt

/ftp_data/webrep/SrcFiles/uvw.txt

/ftp_data/webrep/pqr.txt

In session properties i give /ftp_data/webrep/ in the directory path and file name as emp_source.txt and file type as Indirect.

A2) We can use Union Transformation to load multiple files into single target

48)By using Filter Transformation,How to pass rows that does not satisfy the condition(discarded rows) to another target?

A) You cannot pass the rows if condition is not satisfy .Connect the ports of the filter transformation to the second target table and enable the 'FORWARD REJECTED ROWS' in the properties of the filter transformation. The rejected rows will be forwarded to this table.

49)In scd type 1 what is the alternative to that lookup  transformation ?

A) Alternative to Look-up id Joiner. We need to import source structure of target in Source Analyzer and bring that into mapping and use it for comparison like Lookup. In the session u have to put update else insert

50) If we use only look-up transformation in a mapping ie, Source Qualifier-->Look-up --> Target. , here data are taking very long time to load in target. So what are steps to improve the performance in that mapping?

A) use order by clause in source qualifier. Remove un-necessary ports in that look-up. Try to restrict look-up cache based on the source data. Create order by clause in look-up override using the look-up condition columns. If look-up and source are in same database try to join in source qualifier. Drop the index on the target table before loading the data. Check index on source table, Check source table look tables are analyzed properly or not.

51) On look-up on any table we can get only a)any value or b)last value  but if i need both duplicate values How can I achieve?

A1)To get both the duplicate values, use JOINER (Master outer join)

52) By using joiner t/r we can join only 2 tables, SO if want join more than 2 flat files, How can I join?

A1)for n source we required n-1 joiner t/r. ex: if we have 3 flat file then we required 2 joiner t/r

A2) Use look-up also for the joining.

53)What are the uses of a Parameter file?

A)parameter file ---- we have parameters and variable can use than in mapping, session and work flow. session: to change the cache path. Work flow : to know the status of previous task. mapping: to maintain constant value or to use last updated value

54) How to eliminate 1st and last rows from the source and load the in between rows.

A)To eliminate first row, simply use a counter in Expression and then Filter. To eliminate the last row, make two pipelines with the same source In first pipeline, use AGGREGATOR without any Group By ports In second pipeline, join it with the above with JOINER. Use and Outer Join and then other than that Joined row .. load all the rows.

55)I Have 100 Records in a relational table and I want to load the record in 3 targets , first records goes to target 1 and second to target 2 and third to target 3 and so on ,what are the tx used in this and what are the logics ,can anyone help me?

A1)I have a solution for this.

   1) From source qualifier get the records to the Expression.

   2) Use one Sequence generator in which set the max value as 

      3  enable cycle option. Connect it to the expression.

   3) then use router & create 2 groups, 1st group condition

      as  Next value = 1 another as next value  = 2 and default.

   4) These should be connected to the 3 target tables.

      The requirement will be achieved.

A2)I had another simple solution ...

  1.use router transforamation .. generate 3 groups and use

   sequence generator ... next value connect to router

   transformation... and logic is

   mod(next,3)=1,mod(next,3)=2,mod(next,3)=0,,

   connct to 3 targets load it...

56) Find duplicate records in file.

A1)sort filename| uniq -c | awk ' $1 > 1 { print $2}'

Ex: $ cat a.txt

       1,murali,100

       2,krishna,200

       3,sasui,232

       1,murali,100

       4,krishd,200

$  sort a.txt

       1,murali,100

       1,murali,100

       2,krishna,200

       3,sasui,232

       4,krishd,200

$ sort a.txt | uniq -c

      2 1,murali,100

      1 2,krishna,200

      1 3,sasui,232

      1 4,krishd,200

$ sort a.txt | uniq -c | awk ' $1 > 1 { print $2}'

       1,murali,100

A2)

$ awk 'arr[$1]++' a.txt

       1,murali,100

57)In my sourse i have like

       ename,gender

       vasu,male

       geetha,female

       ram,male

       suma.female

       kesav,male

in my output i need 

       male,female

       vasu,geetha

       ram,suma

       kesav

A)

58) How can a Look-up with Dynamic Cache be replaced in a mapping? Explain.

A)A look-up with Dynamic cache can be replaced by an SQL override query in the transformation itself which will run each time there is load of data during the run of the Work flow.

59) Is informatica power center designer has 64 bit support?

A) Yes it does.

60) Can anyone tell me, when a flat file contains different kinds of delimiters available for each line ( For Ex: First line contains [,*&#$] and second line contains [%@$#%^].Like that all records having different kinds of delimiters.

How can we implement this kind of flat file in informatica?

A)Write a pre-process shell script by using the below 2nd

command and overwrite to another file, use overwritten file

as source for loading.

1)Example given below

$ cat multi_delimeter.txt

1,murali%100#10

2%krishna#200,20

3%sasi%232,10

1%murali%100,30

4,krish,200%20

2)

$ sed -e 's/,/|/g' -e 's/%/|/g' -e 's/#/|/g' multi_delimeter.txt

1|murali|100|10

2|krishna|200|20

3|sasi|232|10

1|murali|100|30

4|krish|200|20

61) In a simple mapping when I use aggregate  t/r without any condition or group by, I am getting output as only last record in the table . can anyone explain it please.........?

A1)In a mapping whatever the transformations used before aggregate transformation

till that point whatever data available in source it perform the logic and keep at

buffer and once come to the aggregate transformation it check for group by , as there is no group by placed at agg transformation it read till the last row and load last row in the target.

A2)When the records come from SQ each record from first one checks for the group by port but till last record it does not found .So by default as the last record considered as only record it stores in Agg T/R.

62) Suppose we have a (assume relational) source table

Product_Id Month Sales

1          Jan     x

1          Feb     x

.           .      .

.           .      .

1          Dec     x

2          Jan     x

2          Feb     x

.           .      .

.           .      .

2          Dec     x

3          Jan     x

3          Feb     x

.           .      .

.           .      .

3          Dec     x

.           .      .

.           .      .

and so on. Assume that there could be any number of product keys and for each product key the sales figures (denoted by 'x' are stored for each of the 12 months from Jan to Dec). So we want the result in the target table in the following form.

Product_id Jan Feb March.. Dec

1           x   x    x      x

2           x   x    x      x

3           x   x    x      x

.

.

So how will you design the ETL mapping for this case ,  explain in terms of transformations.

A)Use an aggregate transformation. Pass the ports (Product_id, Month, Sales) to aggregator, group by Product_ID, manually create 12 ports for month like Jan, Feb, Mar, etc. Include an expression for 12 ports,  Jan -> MAX(IIF(Month = Jan,Sales)), Feb -> MAX(IIF(Month = Feb, Sales)), etc. Without max, it will take last row..Move these ports to the next transformation or to the target. This should give the required output.

63) What will happen if we have a filter transformation used in a mapping and in the filter condition we specify 1. Yes nothing else, only the number 1. In other words, assuming we have 10 rows coming to this filter transformation as an input, how many rows will be output by this transformation when we have only 1 in the filter condition?

A)Yes Ur right

0 represent FALSE and

all NON-Zero numbers are TRUE.

Answer to above question is all 10 rows will be inserted.

64) how to convert the .doc docs into .txt format using informatica.

A)This is nothing but using new features available in informatica 8.1 and above i.e. using unstructured sources. This can be done using the B2B Data transformation tool

Provided by informatica.

65) Indexes on a Lookup Table?

A) When you have large lookup tables...

Cached lookups:You can improve performance by indexing the columns in the lookup ORDER BY. The session log contains the ORDER BY clause.

Uncached lookups. Because the Integration Service issues a SELECT statement for each row passing into the Lookup transformation, you can improve performance by indexing the columns in the lookup condition.

66) By default how oracle treat null values while sorting?

A) Treat null values as high. If we sort in Asc order all null values comes to bottom.

67) How the lookup transformation checks the null values in conditions?

A) The Integration Service matches null values. For example, if an input lookup condition column is NULL, the Integration Service evaluates the NULL equal to a NULL in the lookup. But in joiner transformation null does not match with null.

68) What are the performance tips you use when you configure the Lookup  transformation?

A) Add an index to the columns used in a lookup condition when you have lookup on big tables .Place conditions with an equality operator (=) first. Cache small lookup tables by overriding the query and using the required lookup ports. Join tables in source qualifier transformations if both tables from the same database rather than using a Lookup transformation. Use a persistent lookup cache for static lookups.

69)What is node in Informatica?

A)A node is a logical representation of a physical machine in the domain

When you install Power Center Services on a machine, you add the machine to the domain as a node. You can add multiple nodes to a domain Each node in the domain runs a Service Manager that manages domain operations on that node A node can be a gateway node or a worker node depending on the service. Gateway node-A gateway node is any node you configure to serve as a gateway for the domain, that is the master gateway. Worker node-A worker node is any node not configured to serve as a gateway. It can run application services, but it cannot serve as a gateway.

70)How the informatica server sorts the string values in Rank transformation?

A)We can run informatica server either in UNICODE data moment mode or ASCII data moment mode.
Unicode mode: in this mode informatica server sorts the data as per the sorted order in session.

ASCII Mode:in this mode informatica server sorts the date as per the binary order

71)What is power center repository?

A)Standalone repository: A repository that functions individually, unrelated and unconnected to other repositories.
Global repository: (PowerCenter only.) The centralized repository in a domain, a group of connected repositories. Each domain can contain one global repository. The global repository can contain common objects to be shared throughout the domain through global shortcuts.
Local repository: (PowerCenter only.) A repository within a domain that is not the global repository. Each local repository in the domain can connect to the global repository and use objects in its shared folders.

72) How many null values in unique key?

A) Many nulls can be inserted. Because we don't know how much null is that is why one null is not compared with another null.

73) Display records from 300 to 500 without using set operator?

A) select rownum,empid from emp group by rownum,empid having rownum between 300 and 500;

74) rank function.

A) SELECT deptno,

       ename,

       sal,

       RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"

FROM   emp

WHERE  deptno = 30;

        DEPTNO ENAME          SAL       rank

---------- ---------- ---------- ----------

        30 JAMES             950          1

        30 WARD             1250          2

        30 MARTIN           1250          2

        30 TURNER           1500          4

        30 ALLEN            1600          5

        30 BLAKE            2850          6

75) DENSE_RANK function

A) SELECT deptno,

       ename,

       sal,

       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) "rank"

FROM   emp

WHERE  deptno = 30;

    DEPTNO ENAME             SAL       rank

---------- ---------- ---------- ----------

        30 JAMES             950          1

        30 WARD             1250          2

        30 MARTIN           1250          2

        30 TURNER           1500          3

        30 ALLEN            1600          4

        30 BLAKE            2850          5

76) What is the difference between Connected and UnConnected Lookup Transformation.

Give me one or two examples please?

A) 1) Connected lookup receives input values directly from the

pipeline whereas Unconnected lookup Receives input values

from the result of a :LKP expression

in another transformation.

2) You can use a dynamic or static cache in connected

lookup whereas in connected lookup you can use static cache

only.

3) In connected lookup, cache includes all lookup columns

used in the mapping (that is, lookup source columns

included in the lookup condition and lookup source columns

linked as output ports to other transformations) whereas in

unconnected lookup, Cache includes all lookup/output ports

in the lookup condition and the lookup/return port.

4) Connected lookup can return multiple columns from the

same row or insert into the dynamic lookup cache whereas

Unconnected lookup designate one return port (R). Returns

one column from each row.

5) In connected lookup, if there is no match for the lookup

condition, the PowerCenter Server returns the default value

for all output ports. If you configure dynamic caching, the

PowerCenter Server inserts rows into the cache or leaves it

unchanged.

In unconnected lookup, If there is no match for the lookup

condition, the PowerCenter Server returns NULL.

6) Connected lookup pass multiple output values to another

transformation. Link lookup/output ports to another

transformation.

Unconnected lookup pass one output value to another

ransformation. The ookup/output/return port passes the alue

to the ransformation calling :LKP expression.

77) exists vs IN

A) The EXISTS function searches for the presence of a single row meeting the stated criteria as opposed to the IN statement which looks for all occurrences. For example:

TABLE1 - 1000 rows

TABLE2 - 1000 rows

(a)

SELECT t1.id

FROM   table1 t1

WHERE  t1.code IN (SELECT t2.code

                   FROM   table2 t2);

(b)

SELECT t1.id

FROM   table1 t1

WHERE  EXISTS (SELECT '1'

               FROM   table2 t2

               WHERE  t2.code = t1.code)

For query a, all rows in TABLE2 will be read for every row in TABLE1. The effect will be 1,000,000 rows read from items. In the case of query b, a maximum of 1 row from TABLE2 will be read for each row of TABLE1, thus reducing the processing overhead of the statement.

Rule of thumb:

If the majority of the filtering criteria are in the subquery then the IN variation may be more performant.

If the majority of the filtering criteria are in the top query then the EXISTS variation may be more performant.

I would suggest they you should try both variants and see which works the best.

Note. In later versions of Oracle there is little difference between EXISTS and IN operations.

78) When the informatica server marks that a batch is failed?

A)  A batch fails when the sessions in the workflow are checked with the property  "Fail if parent fails" and any of the session in the sequential batch fails.

79) I have a scenario like - how can i load 1st record to Trgt1,2nd->Trgt2, 3rd->Trgt3 and again the cycle has to repeat with loading 4th->Trgt1,5th->Trgt2,6th->Trgt3?

A) Take a sequence generator with Start Value=1,Current Value=1,End Value=3, Increment By=1  to assign a seqno to each row. Do not forget to enable the cycle option.after that take a Router with three groups of seqno=1,seqno=2 and seqno=3.

80)  Hello Everyone,i have one question on sequence generator.i have two targets and the records comes from the source will place in the targets like 1-10 rec in target1 and 11-20 rec in target2,21-30 rec in target1...etc so any one can help me?

A) increment by 1

end value 20

current value 0

check reset(to be on safe side)

in exp create to o/p ports

like first_10values

second_10values

then take router

in  that give first_10VALUES<11;

GIVE THAT CONNECT INTO ONE TARGET

AND THE DEFAULT INTO ANOTHER TARGET

81) What is the advantage of un-connected lookup ?

A) Say...If we have 8000 records with 2 fields. First field product_id and another one product_name.

But out of 8000 records, I dont have product name for some of the products. So in the connected lookup based on product_id checks it pass all data to next transformation. But for unconnected lookup we give

input values in expression transformation , while giving we can pass the values for looking only null values (ex: iif(isnull(product_name), :lkp.lkp_name,product_name))

82) if we pass one day it has to return the next coming Monday?

A) Expression:-

v1= to_char(DATE_FIELD,'D')

v2=  DECODE( v1,1, 1,

2, 7,3, 6,4,5,5,4,6,3,7,2)

output= ADD_TO_DATE(DATE_FIELD , 'DD', v2 )

83)  Which one is better performance wise joiner or lookup?

A) Are you lookuping flat file or database table? Generally sorted joiner is more effective on flat files than lookup because sorted joiner uses merge join and cashes less rows. Lookup cashes always whole file. If the file is not sorted it can be comparable. Lookups into database table can be effective if the database can return sorted data fast and the amount of data is small because lookup can create whole cash in memory. If database responses slowly or big amount of data are processed lookup cache initialization can be really slow (lookup waits for database and stores cashed data on discs). Then it can be better use sorted joiner which throws data to output as reads them on input.

84) I have called the same unconnected lookup in multiple expression transformations. For the first call and second call I have some gap. In this gap some records have been inserted in lookup table. How the data sync while reading 2^nd read/call?

A) We should use unconnected lookup with no cache option. It directly scan on the table.

85) How we call Router transformation is Active?

A) If we use two filter with the same conditions then it double the count. So that we called as Active.

86) How you can use workflow variable in mapping/session?

A) By using assignment task we can use.

87) What do you do in Bill Inmon Approach?

A) In Bill Inmon's approach, we try to create an Enterprise Data Warehouse using 3rd NF, and then

Data Marts are mainly STAR Schemas in 2nd NF.

88) Coming Monday in Oracle ?

A) select next_day(sysdate,'MONDAY') from dual;

89) How you can read Informatica 8.6 session log file in UNIX box?

A) By default in Informatica 8.6 the log files generates in binary mode with .bin extension.

We cannot see those in UNIX box. For this in 8.6 version, we have first option in session properties called “Write backend session log file”

90)  Finding all mappings which are having CHANNEL string in column used across mapping?

A) select UNIQUE OM.MAPPING_NAME from OPB_WIDGET_INST OWI,OPB_WIDGET_FIELD OWF,OPB_MAPPING OM where

upper(OWF.FIELD_NAME) like '%CHANNEL%' AND

OWI.WIDGET_ID=OWF.WIDGET_ID AND

OWI.MAPPING_ID=OM.MAPPING_ID;

91) finding procedure SP_ODS_XPONENT_PLAN_TERR in which mapping it has used?

A) SELECT M.MAPPING_NAME FROM ETL_ADMIN.OPB_EXPRESSION E,ETL_ADMIN.OPB_WIDGET_INST I,ETL_ADMIN.OPB_MAPPING M

 WHERE E.WIDGET_ID=I.WIDGET_ID AND I.MAPPING_ID=M.MAPPING_ID AND EXPRESSION LIKE '%SP_ODS_XPONENT_PLAN_TERR%';

92) How many "A" are there in the given string, write this logic in Informatica?

A) Replace with null where ever "A" is there then take length and do the minus from original length.

ex:- length("MURALI")- LENGTH( REPLACECHR ( 1, "MURALI" , 'A', NULL ))

93)  If the table contains the below data,

SALE_DATE            SEQ    SALE_AMT

10/18/2010 00:00:00  1      100.2

10/18/2010 00:00:00  2      120.3

10/18/2010 00:00:00  3      130.4

12/18/2010 00:00:00  1      230.1

12/18/2010 00:00:00  2      330.5

12/18/2010 00:00:00  3      430.7

11/12/2010 00:00:00  1      530.6

11/12/2010 00:00:00  2      630.2

11/12/2010 00:00:00  4      230.9

generate as below by using the SQL query.

10/18/2010 00:00:00  100.2 120.3 130.4

12/18/2010 00:00:00  230.1 330.5 430.7

11/12/2010 00:00:00  530.6 630.2  230.9

A) select sale_date,

max(case when seq=1 then SALE_AMT else null end) c1,

max(case when seq=2 then SALE_AMT else null end) c2,

max(case when seq=3 then SALE_AMT else null end) c3,

max(case when seq=4 then SALE_AMT else null end) c4

 from norm_test group by sale_date;

94) Get table count without using count function?

A) select max(rownum) from table;

95) One Session must run three times and continue to the next session. What code you write.

A) Use first session three times (Session instance) in the workflow and call parameters if required.

96) how can you validate the data values with footer information?

I have file like the below

a.txt

--------------

header

1,200,murali

2,100,krishna

3,123,piyush

4,233,Emmanuel

5,233,Emmanuel

6,233,Emmanuel

7,233,Emmanuel

8,233,Emmanuel

9,233,Emmanuel

10,200,murali

60

A) tail +2 a.txt|sed -n '$!p' >b.txt

v1=`awk -F"," 'BEGIN {x=0} {x+=$1} END {print x}' b.txt`

v2=`tail -1 a.txt|awk -F"," '{print $1}'`

if [ $v1 = $v2 ]

then

exit 0

else

exit 1

fi

97) source

-------

10

10

10

20

20

30

30

30

30

30

Target

------

10

101

102

20

201

30

301

302

303

304

A) Query:-

----------

select to_char(e.deptno) from (select deptno,row_number() over (partition by deptno order by deptno) rnm from emp) e where e.rnm=1

union

select deptno||row_number() over (partition by deptno order by deptno) from

(select deptno,row_number() over (partition by deptno order by deptno) rnm from emp) e where e.rnm<>1  ;

98) Difference between Decode and Case functions?

A1) DECODE takes different type of datatype expressions while CASE does not:

SQL> SELECT  DECODE(2,1,1,'2','2') FROM  DUAL

Result:-   2

----------

SQL> SELECT  CASE 2 WHEN 1 THEN 1 WHEN '2' THEN '2'  END  FROM  DUAL

Result:-

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

A2) * DECODE can be used Only inside SQL statement But CASE can be used any where even as a parameter        of a function/procedure

·          DECODE can only compare discrete values (not ranges) continuous data had to be contorted into discreet values using functions like FLOOR and SIGN. In version 8.1 Oracle introduced the searched CASE statement which allowed the use of operators like > and BETWEEN (eliminating most of the contortions) and allowing different values to be compared in different branches of the statement (eliminating most nesting).

·           CASE is almost always easier to read and understand and therefore it's easier to debug and maintain.

·           Another difference is CASE is an ANSI standard where as Decode is proprietary for Oracle.

·           Performance wise there is not much differences. But Case is more powerful than Decode.

99) Normalizer transformation is not involved in Mapplet.. why???

A) Mapplet is a reusable logic that you can use across different mappings.

Normalizer is a dynamic transformation which converts rows to columns or vice-versa,

so that is dependent on the input to the normalizer, it is not fixed logic that you

can reuse in other mappings.

100) Suppose i have 10000 records. First time I have to load 1 to 1000 records and second run I have load 1000 to 2000 records and third load I have to load 2000 to 3000 rows .How will achieve?

A) $$Variable1=0    

   $$Variable2=0

Expression Transformation:

$$Varibale2=$$varibale1*1000;                         

Filer: rownum>Variable2 & rownum<=Variable2+1000;     

      

Expression Transformation:

$$varibale1=$$Variabl1+1;

101) In which scenario un-connected lookup is faster?

A) If the source data is huge and has 90% of the data is null for the corresponding lookup condition column, then if we use connected lookup we have to check for all the data (It is irrespective of null data). But in unconnected we can check only not null data by putting the condition by using is_null function in expression transformation. So in unconnected lookup it saves the time in checking 90% null data.

Note:-un-connected lookup is not faster because of not connected pipe lines.

102) HOW TO JOIN THE 2 FLAT FILES WITHOUT USING JOINER AND LOOKUP?

A) Use join UNIX command in pre-session command for joining both the files and write to a file. Use single SQ trans in mapping to read that joined flat file data.

103) How does u identify or filter out a 0 byte file available in a folder by using UNIX command?

A) List all the empty files only in your home directory.

# find . -maxdepth 1 -empty

List only the non-hidden empty files only in the current directory.

# find . -maxdepth 1 -empty -not -name ".*"

104) write a query for how to eliminate the duplicate rows without using distinct?

A) select * from emp

       union

  select * from emp;

105) What is Echo $$ and Echo $0

A) $$ for process login id for the corresponding shell and $0 is for the currently running script name.

106) chmod 000 file_name.txt   then what will happen?

A) no permissions for that particular file.

----------    1 MUDAYAGI mkpasswd        0 Nov 19 17:24 b.txt

from root we can give permissions to that file/script.

107) In which scenario un-connected lookup is faster?

A) If the source data is huge and has 90% of the data is null for the corresponding lookup condition column, then if we use connected lookup we have to check for all the data ( It is irrespective of null data). But in unconnected we can check only not null data by putting the condition by using is_null function in expression transformation. So in unconnected lookup it saves the time in checking 90% null data.

Note:-un-connected lookup is not faster because of unconnected pipe lines.

108) One Session must run three times and continue to the next session. What code you write?

A) By using session instance we can achieve this.

Use the session instances of first session 3 times in the workflow and call parameters if required.

109)  If the table contains the below data

SALE_DATE         SEQ   SALE_AMT

--------------------------------------

10/18/2010 00:00:00     1     100.2

10/18/2010 00:00:00     2     120.3

10/18/2010 00:00:00     3     130.4

12/18/2010 00:00:00     1     230.1

12/18/2010 00:00:00     2     330.5

12/18/2010 00:00:00     3     430.7

11/12/2010 00:00:00     1     530.6

11/12/2010 00:00:00     2     630.2

11/12/2010 00:00:00     4     230.9

generate as below by using the SQL query.

10/18/2010 00:00:00  100.2    120.3       130.4

12/18/2010 00:00:00  230.1    330.5       430.7

11/12/2010 00:00:00  530.6    630.2       230.9

A)    select sale_date,

                  max(case when seq=1 then SALE_AMT else null end) c1,

            max(case when seq=2 then SALE_AMT else null end) c2,

            max(case when seq=3 then SALE_AMT else null end) c3,

            max(case when seq=4 then SALE_AMT else null end) c4

      from norm_test

      group by sale_date;

Note:- By using normalizer transformation we can do the reverse way. (Changing rows as columns)

110)  How many "A" are there in the given string , write this in Informatica logic?

A)  By using REPLACECHR replace with null where ever "A" is there then take the original string length and do the minus with length of null substituted string.

ex:- LENGTH("MURALI")- LENGTH( REPLACECHR ( 1, "MURALI" , 'A', NULL ))

111) I know one procedure name and I wanted to find out all the mappings which uses that procedure.

How you can list out the mapping names (Dependent informatica mapping objects on that procedure)?

A)  Bu using informatica OPB tables.

Ex:- SELECT M.MAPPING_NAME

     FROM

        ETL_ADMIN.OPB_EXPRESSION E,

        ETL_ADMIN.OPB_WIDGET_INST I,

        ETL_ADMIN.OPB_MAPPING M

     WHERE

         E.WIDGET_ID=I.WIDGET_ID

      AND I.MAPPING_ID=M.MAPPING_ID

      AND EXPRESSION LIKE '%SP_ODS_XPONENT_PLAN_TERR%';

Note:-  Here procedure name is SP_ODS_XPONENT_PLAN_TERR .

112) By default Informatica 8.6 session log file can generate in binary mode.

  Then how you can read Informatica 8.6 session log file in UNIX box?

A)  By default in Informatica 8.6 the log files generates in binary mode with .bin extension.

We cannot read those in UNIX box. For this in 8.6 versions, we have first option in session properties called “Write backend session log file”. If we check in that option then log generates normally.

113) Coming Monday in Oracle?

A) select next_day(sysdate,'MONDAY') from dual;

114)I have a scenario like - how can I load 1st record to Trgt1,2nd->Trgt2, 3rd->Trgt3 and again the cycle has to repeat with loading 4th->Trgt1,5th->Trgt2,6th->Trgt3?

A) Take a sequence generator with Start Value=1, Current Value=1,End Value=3, Increment By=1  to assign a seqno to each row. Do not forget to enable the cycle option. after that take a Router with three groups of seqno=1,seqno=2 and seqno=3 conditions and connect 3 groups to 3 targets.

115)Find duplicate records in file.

A)  Ex:- Flat file a.txt

      1,murali,100

      2,krishna,200

      3,sasui,232

      1,murali,100

      4,krishd,200

Command 1

$ sort a.txt | uniq -c | awk ' $1 > 1 { print $2}'

Result:-    1,murali,100

Command 2

$ awk 'arr[$1]++' a.txt

Result:-      1,murali,100

116)How you can filter out numeric data from numeric + char data filed.

 Ex: Column1

     murali234

     562364322

     krishna

     udayagiri1292

     90293343

A)  Select * from table where upper(column1)<> lower(coulmn1);

117) why we load the data into aggregated tables?

A) For the reporting purpose. Performance degrades If the reporting queries directly uses fact tables.

That is why on detail level fact tables we do some aggregation and store those in aggregate tables for ready use in reporting queries.

118) Can we use update strategy transformation while using BULK load option?

A) For update strategy transformation we should have primary keys in target table. But Bulk load cannot support any keys on target tables. So in this case we can create primary key only at informatica level in target definition and not at database level.

119) How you can check the no of fields in comma delimited flat file?

A) awk -F"," '{print NF}' a.txt|uniq -c

ex:- file name is a.txt

1,200,murali

1,200,murali

1,200,murali

1,200,murali

1,200,murali

1,200,murali

1,200,murali,krishna

1,200

Command awk -F"," '{print NF}' a.txt gives for each record how many fields it has

awk -F"," '{print NF}' a.txt|uniq -c  will give row wise distinct field numbers.

Result:-

        6 3   (3 fields records are 6)

      1 4    (4 fields record is 1)

      1 2    (2 fields record is 1)

120) Find Common records from two files in UNIX?

A)  grep -Ff file1 file2

121) What does tail +2 command?

A) It prints from 2^nd record to till end of the record.