April 14, 2015
Informatica is the most important (and popular) tool in the Data Integration Industry. Actually, it’s a culmination of several different “Client Soft wares”: you need to master Mapping Designer , Workflow Monitor and good old Workflow Monitor if you want to master Informatica.
INFORMATICA IS ONE OF THE BEST ETL TOOLS OUT THERE, AND WE HAVE THE PERFECT INFORMATICA TUTORIAL GUIDE BOOK WITH THE BEST RESOURCES AROUND THE WEB.
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 5, 2014
Cloud Computing has definitely emerged as one of the most upcoming technologies , where organizations are considering to upgrade their infrastructure to support cloud computing.We have put together a list of Cloud Computing Pros and Cons that a company should be aware of in regards to cloud computing.
Cloud Computing Pros
Cloud Computing is highly cost efficient in terms of maintenance . Traditional desktop software contribute to extra cost when adding multiper user licences which increases the cost to company. While cloud technology is comparatively lot cheaper than maintaining your own hardware.There are many one-time-payment, pay-as-you-go and other scalable options available.
Unlimited Storage (Almost !!)
You need not worry about running out of storage space , the cloud service provider will take care of all storage concerns.You just need to request for additional space.
Backup and high availability is perhaps one of the greatest advantages of computing on cloud. Since all data is available in cloud ,it is much easier to maintain backups rather than having data on a physical device.
Since data is always available , it can be accessed from any part of the world (as long as you have a internet connection!!) .Time zone difference are taken care of as well.
Cloud computing drastically reduces the carbon emissions for an organization . Studies show that total carbon emission reduces by upto 35%.
Cloud Computing Cons:
This perhaps is the biggest threat when it comes to Cloud computing.All the enterprise data is at the helm of a third party vendor which could pose major threat if the vendor is not reliable.
While moving onto cloud , organizations would have the need to replace their existing IT infrastructure so that it is compatible on cloud.This could prove to be a costly affair.
It could happen that the cloud service vendor does not have proper standard for cloud computing. IEEE has set standards in cloud computing for optimum quality .In order avoid pitfalls due to standardization , the company needs to investigate if the provider uses standard technology.
Yes cloud computing does have certain disadvantages associated with it , but the Pros outweighs the cons . These risks can be easily mitigated if the company involved takes steps to manage these risks. However the immense benefits that cloud computing offers is over whelming and extremely beneficial . Cloud computing and BI is definitely the way of the future.
September 4, 2014
Business Intelligence is on the verge of huge change. For a long time it has been limited to only the larger organizations . With the advent of open-source technologies , BI has become much more accessible . In today’s post we would be discussing some key Business Intelligence benefits to create awareness of this wonderful concept and as to why one should leverage BI capabilities in their enterprise solutions.
1) Stay ahead of competition
Arm your team with compelling BI reporting and examination so they can stay in front of the pack, and exploit patterns and new open doors as they arrive. Visually intelligent dashboards gives your group access to sales financial and operational parameters of your product/organization as well as your competitor.
2) Foresee and react to patterns
Examine organization and competitor information to set competitive objectives, and track achievements against key performance indicators for the business . Analyze trends and mitigate business risks. Apply hypothetical variables to your information to foresee possible outcomes , historical informational data caters to creation of a predictive model.
3) Empower Decision Making with on-demand data .
Business Intelligence lets your team, from field reps to top level administration, access dynamic data and empower them with business perspective by offering deep insights into data , enabling better decision making.
4) Visual dashboards and custom-made reports at your fingertips.
Visually stunning and detailed information could be conveyed to the significant individuals via reporting dashdboards with custom reports , with ability to drill down and dissect the information to gain insights. Furthermore BI reports has provisions to customize reports to fit each users needs through simple drag drop functionality.
5) Reduce wastage , efficient manage resources.
BI helps to get rid of nonaligned data systems and eliminate resource wastage. Eliminates wasting time and resources spent on correcting data errors and manually compiling spreadsheet based reports , so your resources can add maximum value to the organization growth by focusing more on the business growth.
6) Track and monitor Customer behavior
Business Intelligence helps you to identify what customers are buying, what are trends of the buying customers. You can monitor customer behavior and use data insights to drive loyalty and maximize profit by identifying up-sell and cross-sell opportunities. BI also provides opportunities to identify conversions for a potential lead to become a customer
7) Ability to mine data , discover your true manufacturing costs
Business Intelligence software has the ability to create automated alerts to notify managers of incidents that require action. This brings more efficiency in managing procurement , managing inventory , delivery which reduces cost and mitigates risks.
8) Discovering New Questions and Their Answers
Business intelligence is data driven in nature , trending patterns helps us identify and ask new questions leading to a data-driven discovery. Else it could work the other way round where-in you may have certain questions which could be answered using historical data in the warehouse.
9) Mobile Device Accesibility
BI enables easy integration with various mobile devices like I-pads,laptops , mobiles. Hence reps/directors can have access to reports on-site on -fields or in office.
10) Efficient Data Management
BI enables integration of enterprise wide disparate data sources into a single unified data-warehouse, to ensure consistency and accuracy. This data is cleansed quality data , which makes data management very easy.
These points here just scratch the surface when it comes to Business Intelligence benefits , if you feel strongly feel about other benefits you want to point out please feel free to comment below and let us know.
Check this very awesome video out which outlines BI concepts in a very interesting way.
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 .