January 21, 2015
Here are a few points which will get you started with Informatica Power center Performance Tuning .Some of these tips are very general in nature please consult your project members before implementing them in your projects.
1) Optimize the Input Query if it’s Relational (e.g. Oracle table) source –
- Reduce no.of rows queried by using where conditions instead of Filter/Router transformations later in the mapping. Since you choose fewer rows to start with, your mapping will run faster
- Maker sure appropriate Indexes are defined on necessary columns and analyzed. Indexes must be especially defined on the columns in the ‘where’ clause of your input query.
- Eliminate columns that you do not need for your transformations
- Use ‘Hints’ as necessary.
- Use sort order based on need
Note: For # i results will vary based on how the table columns are Indexed/queried…etc.
2) Use the Filter transformation as close to the SQ Transformation as possible.
3) Use sorted input data for Aggregator or Joiner Transformation as necessary.
4) Eliminate un-used columns and redundant code in all the necessary transformations.
5) Use Local variables as necessary to improve the performance.
6) Reduce the amount of data caching in Aggregator.
7) Use parameterized input query and file for flexibility.
8) Changed memory related settings at workflow/session level as necessary.
9) When use multiple condition columns in Joiner/Lookup transformation make sure to
use numeric data type column as first condition.
10) Use persistent cache if possible in Lookup transformations.
11) Go through the sessions Logs CLOSELY to find out any issues and change accordingly
12) Use overwrite queries in Lookup transformation to reduce the amount of data cached.
13) Make sure the data type and sizes are consistent throughout the mapping as much
14) For Target Loads use Bulk Load as and when possible.
15) For Target Loads use SQL * Load with DIRECT and UNRECOVERABLE option for large volume of data loads.
16) Use Partitioning options as and when possible. This is true for both Informatica and Oracle. For Oracle, a rule of thumb is to have around 10M rows per partition
17) Make sure that there are NO Indexes for all “Pre/Post DELETE SQLs” used in all
18) Use datatype conversions where ever possible
e.g: 1) Use ZIPCODE as Integer instead of Character this improves the speed of the lookup Transformation comparisons.
2) Use the port to port datatype conversions to improve the session performance.
19) Use operators instead of functions
e.g: For concatenation use “||” instead of CONCAT
20) Reduce the amount of data writing to logs for each transformation by setting
log settings to Terse as necessary only.
21) Use Re-usable transformations and Mapplets where ever possible.
22) In Joiners use less no.of rows as Master ports.
23) Perform joins in db rather than using Joiner transformation where ever possible.
November 24, 2014
Hey Folks, As Discussed in our earlier post this our subsequent post regarding Informatica Interview Questions. please subscribe to get the free copy of PDF with answers and leave a comment.
Informatica Questions And Answers :-
1) What is the difference between reusable transformation & shortcut created ?
2) Which one is true for mapplets ( can u use source qyalifier, can u use sequence generator, can you use target) ?
3) What are the ways to recover rows from a failed session ?
4) Sequence generator, when u move from development to production how will you reset ?
5) What is global repository ?
6) How do u set a variable in incremental aggregation ?
7) What is the basic functionality of pre-load stored procedure ?
8) What are the different properties for an Informatica Scheduler ?
9) In a concurrent batch if a session fails, can u start again from that session ?
10) When you move from development to production then how will you retain a variable value ?
11) Performance tuning( what was your role) ?
12) what are conformed dimensions?
13) Can you avoid static cache in the lookup transformation? I mean can you disable caching in a lookup transformation?
14) What is the meaning of complex transformation?
15) In any project how many mappings they will use(minimum)?
16) How do u implement un-connected Stored procedure In a mapping?
17) Can you access a repository created in previous version of Informatica?
18) What happens if the info. Server doesn’t find the session parameter in the parameter file?
19) How did you handle performance issues If you have data coming in from multiple sources, just walk through the process of loading it into the target
20) How will u convert rows into columns or columns into rows
21) What are the steps involved in the migration from older version to newer version of Informatica Server?
22) What are the main features of Oracle 11g with context to data warehouse?
24) How to run a session, which contains mapplet?
25) Differentiate between Load Manager and DTM?
26) What are session parameters ? How do you set them?
27) What are variable ports and list two situations when they can be used?
28) Describe Informatica Architecture in Detail ?
29) How does the server recognise the source and target databases.
30) What is the difference between sequential batch and concurrent batch and which is recommended and why?
31) A session S_MAP1 is in Repository A. While running the session error message has displayed
‘server hot-ws270 is connect to Repository B ‘. What does it mean?
32) How do you do error handling in Informatica?
33) How can you run a session without using server manager?
34) Consider two cases:
1. Power Center Server and Client on the same machine
2. Power Center Sever and Client on the different machines
what is the basic difference in these two setups and which is recommended?
35) Informatica Server and Client are in different machines. You run a session from the server manager by specifying the source and target databases. It displays an error. You are confident that everything is correct. Then why it is displaying the error?
36) What is the difference between normal and bulk loading? Which one is recommended?
37) What is a test load?
38) How can you use an Oracle sequences in Informatica? You have an Informatica sequence generator transformation also. Which one is better to use?
39) What are Business Components in Informatica?
40) What is the advantage of persistent cache? When it should be used.
41) When will you use SQL override in a lookup transformation?
Please provide your name and email address for your free download.
November 21, 2014
Hi Folks , from this post onwards we will be posting some Top Informatica Questions that are generally being asked in Interviews.
1. How do u change parameter when u move it from development to production. ?
2. How does the session recovery work. ?
3. why use shortcuts(Instead of making copies). ?
4. where is the reject loader and how to use it. ?
5. Do you have to change the reject file before using reject
loader utility. ?
6. Differences between current and previous versions. ?
7. Debugger – what are the modules, what are the options
you can specify when using debugger, can you change the
expression condition dynamically when the debugger is running ?
8. Mapplets – can you use an active transformation in a Mapplet ?
9. What are active transformations?
10. Can u use flat files in Mapplets ?
11. How many transformations can be used in mapplets. Is there any specified limit ?
12. Can a joiner be used in a mapplet?
13. How can you join 3 tables? Why cant you use a single Joiner to join 3 tables
14. Global and Local shortcuts. Advantages.
15. Mapping variables, parameters syntax, if you create mapping variables
and parameters in mapplet can u use them in the mapping?
16. Have you worked with/created Parameter file
17. What’s the layout of parameter file (what does a parameter file contain?)?
18. Why do we use Mapping Parameter and mapping variable?
19. Session Recovery. 1000 rows in the source of which 500 passed
through and then I killed the session. Can you perform a recovery and how ?
20. What are the modules in Power Center ?
21. filter transformation in the condition one of the data is NULL would the record be dropped.?
22. Implement all three SCD’s using Informatica and with different approaches. ?
Please Leave your comment if you have any query
Subscribe US!!! to get the free PDF copy of Answers
September 4, 2014
In our quest to find something useful for our users across the web , today we would like to showcase to you some really awesome content compiled by http://www.disoln.org . These set of videos provide all possible Informatica tutorials for beginners that is needed . The video series has been divided into many sections (and subsections) ,
Deals with basic introduction to Informatica and its essential components . This section then continues to give introductions to basic components and finally the last video winds up with a complete functional informatica workflow.
Informatica Tutorial 1.1 – Client Components Introduction -Introduction to Informatica PowerCenter Client components, Includes Designer, Workflow Manager, Workflow Monitor and Repository Manager.
Informatica Tutorial 1.2 – Client Configuration (Add Repository)-This Video shows the configuration required for the Powercenter Client before it can get connected to Repository Server.
Informatica Tutorial 1.3 – Working with Source Analyzer -This Video shows the Step-by-Step process for creating different type of source definition using source analyzer in Powercenter.
Informatica Tutorial 1.4 – Working with Target Designer -This Tutorial Video shows the Step-by-Step process for creating Target definition using Target analyzer in Informatica Powercenter.
Informatica Tutorial 1.5 – Create My First Mapping -This Tutorial Video shows the Step-by-Step process for creating your first Informatica mapping.
Informatica Tutorial 1.6 – Create My First Workflow -This Tutorial Video shows the Step-by-Step process for creating your first Informatica Workflow.
Informatica Tutorial 1.7 – Schedule and Monitor Workflow-This Tutorial Video shows the process for creating your Informatica Workflow, Schedule and Monitor the Workflow.
Informatica Tutorial 1.8 – Aggregator Transformation & Multiple Data Sources -This Tutorial Video shows the process for creating mapping with multiple data sources, create Expression and Aggregator Transformation.
Informatica Tutorial 1.9 – Work with Flat File, LookUp & Filter Transformation -This Tutorial Video shows the process for creating mapping with Fixed Width File data source, create LookUp and Filter Transformation
These set of Informatica tutorials videos would suffice for any beginner who is trying to learn this awesome tool!! For practical informatica related scenarios you could check out this page .
May 21, 2014
We have decided to come up with some common scenarios in Informatica , this would be an ongoing post where-in we would be adding common issues faced by an informatica developer.We would be introducing complex scenarios as we move ahead. Please feel free to discuss these scenarios in the comment section below .
1)How to Concat row data through informatica?
Using Dynamic Lookup on Target table:
If record doen’t exit do insert in target .If it is already exist then get corresponding Ename vale from lookup and concat in expression with current Ename value then update the target Ename column using update strategy.
Using Var port Approch:
Sort the data in Source Qualifier based on EmpNo column then Use expression to store previous record information using Var port after that use router to insert a record if it is first time if it is already inserted then update Ename with concat value of prev name and current name value then update in target.
2) How to send Unique (Distinct) records into One target and duplicates into another target?
Using Dynamic Lookup on Target table:
If record doen’t exit do insert in target_1 .If it is already exist then send it to Target_2 using Router.
Using Var port Approch:
Sort the data in sq based on EmpNo column then Use expression to store previous record information using Var ports after that use router to route the data into targets if it is first time then sent it to first target if it is already inserted then send it to Tartget_2.
Below is the solution for processing multiple flat files into table using informatica.
3) How to Process multiple flat files to single target table through informatica if all files are same structure?
We can process all flat files through one mapping and one session using list file.
First we need to create list file using unix script for all flat file the extension of the list file is .LST.
This list file it will have only flat file names.
At session level we need to set
source file directory as list file path
And source file name as list file name
And file type as indirect.
This is also One of the advanced feature in Informatica 8.
4) How to populate file name to target while loading multiple files using list file concept.
In informatica 8.6 by selecting Add currently processed flatfile name option in the properties tab of source definition after import source file defination in source analyzer.It will add new column as currently processed file name.we can map this column to target to populate filename.
5)How to load unique or distinct records from flat file to target?
Ans:Using Aggregator we can load unique records by setting group by on all columns this is one approach
Another method is we can use sorter after source qualifier in sorter we can use distinct option.
6) How to load first record and last record in to target from a file using Informatica?
Create one mapping variable like $$Record_Count and create one Sequence Generator transformation with reset option then use filter transformation .
In filter transformation put condition like below.
Seq.NEXT Value =1 or Seq.NEXT Value =$$Record_Count
Step 1. Use Unix script to create/update parameter file with file record count (wc –l) .this parameter file will supply the value to mapping variable $$Record_Count
Below is the order of the tasks.
Wf——-àcommand task——-àmain session
Command task—To execute unix script
7)How to add a lengthy query in the source qualifier,if the query length exceeds 32K characters ?
If you are trying to use a very length query in the SQ over-ride, there seems to be a character limit for this. This is something around 32K characters.
This must be mainly due to limitation of saving this query as metadata in underlying database repository.
This issue can be solved by writing this query as a parameter value in the param file.
Since the query will be fetched dynamically, the limitation will not be an issue.
Note – Ensure that the query is written in one single line in the parameter file.