1. While importing the relational source definition from database, what are the Metadata of the source you import.
A:
2. How many ways you can update a relational source definition and what are they.
A: Two ways 1. Edit the definition 2. Reimport the defintion .
3. Where would U place the flat file to import the flat file definition to the Designer.
A: on the local folder on the local hard drive of the system.
4. To provide support for the mainframes source data, which files are used as a source definition.
A:
5. Which transformation should u need while using the cobol source as source definition.
A: Normalization Transformation.
6. How can U create or Import FF definition into the warehouse designer?
A: U can not create or import flat file defintion in to warehouse designer directly.
Instead U must analyze the file in source analyzer,then drag it into the warehouse designer.
When U drag the flat file source defintion into warehouse desginer workspace,the warehouse designer
creates a relational target defintion not a file defintion.If u want to load to a file,configure the session to write to a flat file.
When the informatica server runs the session, it creates and loads the flatfile.
----OR---
1) Manually create the flat file target definition in warehouse designer
2)create target definition from a source definition. This is done my dropping a source definition in warehouse designer.
3)Import flat file definitionusing a flat file wizard. ( file must be local to the client machine)
7. What is a Transformation.
A:Transformation plays an important role in Datawarehouse. Transformation are used when data is moved from source to destination.
Depending upon crieteria transformations are done.
Some of the transformations are Aggregater,Lookup,Filter,Source Qualifier,Sequence Generator,Expression
8. What are the designer tools for creating transformations.
A:
9. What are the connected and Un connected transformation.
A: Connected lookup Unconnected lookup
Receives input values diectly from the pipeline Receives input values from the result of a lkp expression in a another transformation.
You can use a dynamic or static cache U can use a static cache.
Cache includes all lookup columns used in the mapping. Cache includes all lookup out put ports in the lookup condition and the lookup/return port.
Support user defined default values Does not support user defiend default values
10.How many ways you create ports.
A: Two ways
1.Drag the port from another transforamtion
2.Click the add buttion on the ports tab.
11.What are the methods for creating resuable transformation.
A: Two methods
1.Design it in the transformation developer.
2.Promote a standard transformation from the mapping designer.After U add a transformation to the mapping , U can promote it to the status of reusable transformation.
Once U promote a standard transformation to reusable status,U can demote it to a standard transformation at any time.
If u change the properties of a reusable transformation in mapping,U can revert it to the original reusable transformation properties by clicking the revert button.
12.What are the mapping parameters and mapping variables.
13. What aggregate cache aggregator transformation.
A: The aggregator stores data in the aggregate cache until it completes aggregate calculations.
When u run a session that uses an aggregator transformation,the informatica server creates index and
data caches in memory to process the transformation. If the informatica server requires more space,it stores overflow values in cache files
14. What r the differences between joiner transfirmation and source qualifier transformation?
A: U can join hetrogenious data Sources in joiner transformation which we can not achieve in source qualifier transformation.
U need matching keys to join two relational sources in source qualifier transformation.Where as u doesn’t need matching keys to join two sources.
Two relational sources should come from same datasource in sourcequalifier.U can join relatinal sources which r coming from diffrent sources also
15. What are the joiner caches?
A: Joiner Cache will use in Joiner transformation to improve the performance. While using joiner cache informatica server first read the data
from master source and built data index & data cache in the master rows. After building the cache joiner transformation
reads records from detail source to performs joins
16. What are the types of look up caches.
A: Persistent cache: U can save the lookup cache files and reuse them the next time the informatica server processes a
lookup transformation configured to use the cache.
Recache from database: If the persistent cache is not synchronized with he lookup table,
U can configure the lookup transformation to rebuild the lookup cache.
Static cache: U can configure a static or readonly cache for only lookup table.By default informatica server creates a static cache.
It caches the lookup table and lookup values in the cache for each row that comes into the transformation.when the lookup condition
is true,the informatica server does not update the cache while it prosesses the lookup transformation.
Dynamic cache: If u want to cache the target table and insert new rows into cache and the target,u can create a look up transformation
to use dynamic cache.The informatica server dynamically inerts data to the target table.
Shared cache: U can share the lookup cache between multiple transactions.U can share unnamed cache between transformations inthe same maping.
17. Which transformation should we use to normalize the COBOL and relational sources?
A: Normalization Transformation.
18. How the informatica server sorts the string values in rank transformation.
19. What are the rank caches.
20. What is the rank index in Rank Transformation.
A: Rank transformation is one of the transformation that give ranks to the rows
for example if u take sal to give rank for sal who got highest sal .
for this for every row of sal has rank with highest to lower sal the highest may be first rank and rest of them decreasing the rank.
21. Describe two levels in which update stragety tranformation sets.
A: Within a session. When you configure a session, you can instruct the Informatica Server to either treat all records
in the same way (for example, treat all records as inserts), or use instructions coded into the session mapping to flag
records for different database operations.
Within a mapping, you use the Update Strategy transformation to flag records
22. What are the different types of type 2 dimension mapping.
23. How the informatica server increases the session performance through partitioning the source.
24. Can u copy the session to a different folder or repository.
A: Yes. By using copy session wizard u can copy a session in a different folder or repository.
But that target folder or repository should consists of mapping of that session.
If target folder or repository is not having the maping of copying session ,
You should have to copy that maping first before u copy the session
25. What are the transformations that restricts the partitioning of the sessions.
A: Normalizer transformaiton which is used to normalize the data. Since cobol sources r oftenly consists of Denormailzed data.
26. Explain about Recovering sessions.
27. Explain about perform recovery.
A: When the Informatica Server starts a recovery session, it reads the OPB_SRVR_RECOVERY table and notes the row ID
of the last row committed to the target Database. The Informatica Server then reads all sources again and starts processing
from the next row ID. For example, if the Informatica Server commits 10,000 rows before the session fails, when you run recovery,
the Informatica Server bypasses the rows up to 10,000 and starts loading with row 10,001.
By default, Perform Recovery is disabled in the Informatica Server setup. You must enable Recovery in the Informatica
Server setup before you run a session so the Informatica Server can create and/or write entries in the OPB_SRVR_RECOVERY table.
1. What are some of the important differences when designing data marts versus data warehouse.
A: http://opensourceanalytics.com/2006/03/14/data-mart-vs-data-warehouse-the-great-debate/
2. What is the most recent versions of informatica that you have used and what were the new features of that version that you have used.
A: Informatica 8.1.1 is the Newest version.
Features of Informatica 8
1. The architecture of Power Center 8 has changed a lot; PC8 is service-oriented for modularity, scalability and flexibility.
2. The Repository
computers in a network (so called nodes), even redundantly.
3. Management is centralized, that means services can be started and stopped on nodes via a central web interface.
4. Client Tools access the repository via that centralized machine, resources are distributed dynamically.
5. Running all services on one machine is still possible, of course.
6. It has a support for unstructured data which includes spreadsheets, email, Microsoft Word files, presentations and .PDF documents.
It provides high availability, seamless fail over, eliminating single points of failure.
7. It has added performance improvements (To bump up systems performance, Informatica has added "push down optimization" which moves data transformation processing to the native relational database I/O engine whenever its is most appropriate.)
8. Informatica has now added more tightly integrated data profiling, cleansing, and matching capabilities.
9. Informatica has added a new web based administrative console.
10. Ability to write a Custom Transformation in C++ or Java.
11. Midstream SQL transformation has been added in 8.1.1, not in 8.1.
12. Dynamic configuration of caches and partitioning
13. Java transformation is introduced.
14. User defined functions
15. PowerCenter 8 release has "Append to Target file" feature.
and 1. Data lineage 2. Push down optimization option 3. Mapping templates. 4. Extended expression library and user defined functions etc..
3. SQL Server question: Assume you have 2 tabels, one is the Customer table and the other is Customer order History table with the following columns:
a. Customer table has Customer number, Customer name, Adress, Zip Code, Credit Limit.
b. Customer order history table has the order number, order date, Customer number, Quantity, Price.
Write the necassary SQL statements to list all Customers names and their lifetime total order value.
4. Explain type 2 SCD logic.
A: In type-1 dimesnion we have only the current dimension data. We overwrite the the old dimension data with the new dimension.
In type-2 , we basically keep the entire history of data and for we add additional row to the changed dimension as a new row with the changed values.
In short it keeps the full historical data what happened before
there are 3 scd types:
scd type1: Only current details
scd type2: History + current details
scd type3: Initial + current details
5. How would you diagnose a poorly performing mapping.
A: Looking at a log file we can diagnose the performance of the mapping and the other method to see it is, Debugging method.
6. What is the difference between connected and unconneccted lookup.
A: Connected lookup tranformation gets the input directly from the mapping pipeline, where as unconnected lookup transformation gets the
input from another expression transaction.
the differences between connected and unconned lookups are as follows:
Connected lookup:- 1. Receives input values directly from the pipe line.
2. You can use a dynamic or static cache.
3. Cache includes all lookup columns used in the mapping.
4. Support user defined default values.
Unconnected lookup:-
1. Receives input values from the result of an lkp expression in another transformation.
2. You can use a static cache.
3. Cache includes all lookup out put ports in the lookup condition and the lookup/return port.
4. Does not support user defined default values.
the main advantages of the unconnected lookup is if suppose u can use the same lkp transformations 2 or more times in a
same mapping its waste of time. in that situations, we can create unconnected lkp transformation and call that expression wherever u want.
7. Why use joiner.
A: To joing 2 different database files or one Flat file and a relational file.
8. Why would you over ride the SQL in source qualifier.
A:
9. Describe why data is staged.
A:
10. What is the difference between informatica 7.1 and informatica 6.2.
A: See Above/ Below answer for Diffrences between 7.1 and 8.1
11. What is the difference between UNION and UNION ALL.
A: The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values including duplicates.
12. What is the difference between unconnected lookup and connected lookup.
A: Connected lookup Unconnected lookup
Receives input values diectly from the pipeline Receives input values from the result of a lkp expression in a another transformation.
You can use a dynamic or static cache U can use a static cache.
Cache includes all lookup columns used in the mapping. Cache includes all lookup out put ports in the lookup condition and the lookup/return port.
Support user defined default values Does not support user defiend default values
13. What is the difference between Dynamic Cache and Static Cache.
A: Dynamic Cache: it is used to treat the source data either insert or update or unchanged.
Uncached Lookup: when we do uncached the session will during loading the data from source to target .
It will uncached to each and every row coming from the lookup.
14. Limitations to joiner tranformation.
A: 1.Both the pipelines begin with the same original data spouece
2.Both input pipelines originate from the SQ transformation
3.Both input pipelines originate from the same normalizer transformation.
4.Either input pipelines contains an update transformation
5.Either input pipelines contains a connected or unconnected sequence generator transformation
15. What is the difference between 9i and 10G.
A: 10 G supports grid computing, ASM (Automatic storage management) and Memory management.
Oralce 10g is higher version of 9i , Oracle 10g has added a follwoing features
Transparent Data Encryption
A sync commits
CONNECT ROLE can not only connect
Passwords for DB Links are encrypted
New asmcmd utility for managing ASM storage
Grid computing - an extension of the clustering feature (Real Application Clusters)
Manageability improvements (self-tuning features)
Performance and scalability improvements
Automated Storage Management (ASM)
Automatic Workload Repository (AWR)
Automatic Database Diagnostic Monitor (ADDM)
Flashback operations available on row, transaction, table or database level
Ability to UNDROP a table from a recycle bin
Ability to rename tablespaces
Ability to transport tablespaces across machine types (E.g Windows to Unix)
New 'drop database' statement
New database scheduler - DBMS_SCHEDULER
DBMS_FILE_TRANSFER Package
Support for bigfile tablespaces that is up to 8 Exabytes in size
Data Pump - faster data movement with expdp and impdp.
In Oracle 9i after drop we can't rollback but we can do it in 10g.
Moreover, 10g has additional 149 features than 9i.
16. What was the purpose of your project.
A: Go according to current situation.
17. What is the team size and your role in it.
A: My team size is 8 ( 3 Developers, one Data Modeller, one Business analyst, 2 Testers and one Team Lead)
My role in this project is as a Developer.
18. What is the between informatica 7.1 and 6.2 in the workflow manager.
A: In 7.1 We use look up on flat files. there is union transformation
19. How will you do performence tuning in a joiner transformation.
A:
20. What are mapplets.
A:Its a set of reusable Transformations. Which can be used in multiple mappings.
21. What are the transformations not supported in mapplet.
A: Lookup Transformation, Joiner transformations, Normalizer transformations, Non reusable sequence generator transformations
22. Where did you use the joiner tranformation.
A: Where ever a relational database need to be joined. from differnent Sources.
23. What are the main considerations you do for the performance tuning in a joiner transformation.
A:
24. Where and how did you use the Session Parameters.
A: Session parameters r like maping parameters,represent values U might want to change between
sessions such as database connections or source files.
25. What is Index and Data caches and which transformation use them.
A:
26. How do Power center client communicate with the respitory server.
A:
27. What are Cursors. What are differnet kinds of cursors.
A: Its Privetae SQL Area. A cursor is a mechanism by which you can assign a name to a "select statement" and manipulate
the information within that SQL statement.
Explicit and Implict Cursor.
28. How do you use Autosys. What version did you use.
A: Autosys is a total job scheduling solution that allows you to define these events, dependencies, time schedules, alerts, etc
I Version i used was is 4.5
29. What are the parameters you specify with PMCMD command.
A:
30. What transformations did you use to eliminate the duplicate rows in informatica.
A:
31. Where did you use Aggregator transformation in your project.
A:
32. Where you doing commits at your source level.
A:
33. What are active and passive transformations. Give examples.
A:
34. What r the mapping paramaters and maping variables?
A: Maping parameter represents a constant value that U can define before running a session.A mapping parameter retains the same value throughout the entire session.
When u use the maping parameter ,U declare and use the parameter in a maping or maplet.Then define the value of parameter in a parameter file for the session.
Unlike a mapping parameter,a maping variable represents a value that can change throughout the session.The informatica server saves the value of maping
variable to the repository at the end of session run and uses that value next time U run the session.
35. What is Source Qualifier?
A: When you add a relational or a flat file source definition to a mapping, you need to connect it to a Source Qualifier transformation.
The Source Qualifier represents the rows that the Informatica Server reads when it executes a session
or
Source Qualifier is a default transformation.
The PowerCenter 7 Mapping Design examination is composed of the thirteen sections listed below. In order to ensure that you are prepared for the test, review the subtopics associated with each section. The Informatica documentation is an excellent source of information on the material that will be covered in the examination. If you are thoroughly knowledgeable in the areas mentioned in this Skill Set Inventory, you will do well on the examination.
The examination is designed to test for "expert level" knowledge. Informatica strongly urges you to attain a complete understanding of these topics before you attempt to take the examination. Hands-on experience with the software is the best way to gain this understanding.
1. Designer configuration
- Be familiar with the rules for using shared and non-shared folders.
- Understand the meaning of each of the Designer configuration options.
- Know what Designer options can be configured separately for each client machine.
- Be familiar with the Designer toolbar functions, such as Find.
2. Transformation ports
- Know the rules for linking transformation ports together.
- Know the rules for using and converting the Informatica data types.
- Know what types of transformation ports are supported and the uses for each.
- Be familiar with the types of data operations that can be performed on a port level.
3. Source and Target definitions
- Understand how editing source and target definitions affects associated objects such as Mappings and Mapplets.
- Understand how the repository stores referential integrity.
- Know how to edit flat file definitions at any time.
- Know the types of source and target definitions supported.
- Know how to determine if a Session Task is considered to have heterogeneous targets.
- Understand the rules and limitations of overriding target types.
4. Validation
Know all the possible reasons why an expression may be invalid.
5. Transformation language
- Be familiar with all transformation language functions and key words.
- Know how the Informatica server evaluates expressions.
- Be able to predict the output or result of a given expression.
6. Source Qualifier transformation Understand how the Source Qualifier uses data types. Know how the default query is generated and the rules for modifying it. Understand how to use the Source Qualifier to perform various types of joins.
7. Aggregator transformation
- Know how to use Informatica aggregate functions.
- Understand how to be able to use a variable port in an Aggregator transformation.
- Be able to predict the output of a given Aggregator.
- Know the rules associated with defining and using aggregate caches.
- Know how to calculate an optimum cache size.
8. Sorter and Sequence Generator transformations
- Know the capabilities and limitations of the Sorter transformation.
- Know when a Sorter transformation does the data sort with respect to the parent Workflow.
- Understand how the Sorter transformation uses hardware resources.
- Understand the meaning and use of the Distinct Output Rows property.
- Understand the difference in the ports used in the Sequence Generator and how each can be used.
9. Lookup transformation
Know the advantages and disadvantages of connected and unconnected Lookups.
10. Joiner transformation
- Know what types of sources can be joined with the Joiner transformation.
- Be familiar with the limitations of the Joiner transformation.
- Understand the supported join types and options available for controlling the join.
11. Update Strategy transformation
- Know how the Update Strategy transformation works in conjunction with session properties.
- Understand how transformations that are "downstream" of the Update Strategy are affected.
- Be familiar with the Update Strategy properties and options.
- Know what can happen to a given row for each different type of row operation.
12. Filter and Router transformations
- A. Understand how to work with the Router and Filter Transformations.
13. Reusable logic
- Be familiar with the rules and limitations regarding Mapplets.
- Know how to use Mapplet Output transformations and Output groups.
- Know the rules regarding active and passive Mapplets.