69)How can u complete unrcoverable sessions?
Under certain circumstances, when a session does not complete, you need to truncate the target tables and run the session from the beginning. Run the session from the beginning when the Informatica Server cannot run recovery or when running recovery might result in inconsistent data.
70)What is the difference between connected and unconnected stored procedures.
Unconnected:
The unconnected Stored Procedure transformation is not connected directly to the flow of the mapping. It either runs before or after the session, or is called by an expression in another transformation in the mapping.
connected:
The flow of data through a mapping in connected mode also passes through the Stored Procedure transformation. All data entering the transformation through the input ports affects the stored procedure. You should use a connected Stored Procedure transformation when you need data from an input port sent as an input parameter to the stored procedure, or the results of a stored procedure sent as an output parameter to another transformation.
71)Can we eliminate duplicate rows by using filter and router transformation ?if so
explain me in detail .
U can use SQL query for uniqness if the source is Relational But if the source is Flat file then u should use Shorter or Aggregatot transformation
72)Can u start a batches with in a batch?
U can not. If u want to start batch that resides in a batch,create a new independent batch
and copy the necessary sessions into the new batch.
73)in the concept of mapping parameters and variables, the variable value will be saved
to the repository after the completion of the session and the next time when u run
the session, the server takes the saved variable value in the repository and starts
assigning the next value of the saved value. for example i ran a session and in the
end it stored a value of 50 to the repository.next time when i run the session, it
should start with the value of 70. not with the value of 51.
how to do this.
u can do onething after running the mapping,, in workflow manager
start-------->session.
right clickon the session u will get a menu, in that go for persistant values, there u will
find the last value stored in the repository regarding to mapping variable. then remove it
and put ur desired one, run the session... i hope ur task will be done
74)How can U create or import flat file definition in to the warehouse designer?
U can create flat file definition in warehouse designer.in the warehouse designer,u can
create new target: select the type as flat file. save it and u can enter various columns for
that created target by editing its properties.Once the target is created, save it. u can import
it from the mapping designer.
75)What r the different types of Type2 dimension maping?
Type2
1. Version number
2. Flag
3.Date
76)What are the mapings that we use for slowly changing dimension table?
Type1: Rows containing changes to existing dimensions are updated in the target by overwriting the existing dimension. In the Type 1 Dimension mapping, all rows contain current dimension data.
Use the Type 1 Dimension mapping to update a slowly changing dimension table when you do not need to keep any previous versions of dimensions in the table.
Type 2: The Type 2 Dimension Data mapping inserts both new and changed dimensions into the target. Changes are tracked in the target table by versioning the primary key and creating a version number for each dimension in the table.
Use the Type 2 Dimension/Version Data mapping to update a slowly changing dimension table when you want to keep a full history of dimension data in the table. Version numbers and versioned primary keys track the order of changes to each dimension.
Type 3: The Type 3 Dimension mapping filters source rows based on user-defined comparisons and inserts only those found to be new dimensions to the target. Rows containing changes to existing dimensions are updated in the target. When updating an existing dimension, the Informatica Server saves existing data in different columns of the same row and replaces the existing data with the updates.
77)How many ways you create ports?
Two ways:-
1.Drag the port from another transforamtion
2.Click the add buttion on the ports tab.
78)How many ways you can update a relational source defintion and what are they?
Two ways:-
1. Edit the definition
2. Reimport the defintion.
79)What are the basic needs to join two sources in a source qualifier?
Two sources should have primary and Foreign key relation ships.
Two sources should have matching data types.
80)What are the different options used to configure the sequential batches?
Two options
Run the session only if previous session completes sucessfully. Always runs the session.
81)What are the methods for creating reusable transforamtions?
Two methods:-
1.Design it in the transformation developer.
2.Promote a standard transformation from the mapping designer.After you add a transformation to the mapping , You can promote it to the status of reusable transformation. Once you promote a standard transformation to reusable status,You can demote it to a standard transformation at any time. If you change the properties of a reusable transformation in mapping,You can revert it to the original reusable transformation properties by clicking the revert button.
82)What r the active and passive transforamtions?
Transformations can be active or passive. An active transformation can change the number of rows that pass through it, such as a Filter transformation that removes rows that do not meet the filter condition.
A passive transformation does not change the number of rows that pass through it, such as an Expression transformation that performs a calculation on data and passes all rows through the transformation.
83)What is tracing level and what r the types of tracing level?
Tracing level represents the amount of information that informatcia server writes in a log file.
Types of tracing level:-
Normal
Verbose
Verbose init
Verbose data
84)Compare Data Warehousing Top-Down approach with Bottom-up approach
Top down
ODS-->ETL-->Datawarehouse-->Datamart-->OLAP
Bottom up
ODS-->ETL-->Datamart-->Datawarehouse-->OLAP
85)How can u work with remote database in informatica?did u work directly by using
remote connections?
To work with remote datasource u need to connect it with remote connections.But it is not preferable to work with that remote source directly by using remote connections .Instead u bring that source into U r local machine where informatica server resides.If u work directly with remote source the session performance will decreases by passing less amount of data across the network in a particular time.
86)Why use the lookup transformation?
To perform the following tasks:-
Get a related value. For example, if your source table includes employee ID, but you want to include the employee name in your target table to make your summary data easier to read. Perform a calculation. Many normalized tables include values used in a calculation, such as gross sales per invoice or sales tax, but not the calculated value (such as net sales). Update slowly changing dimension tables. You can use a Lookup transformation to determine whether records already exist in the target.
87)In certain mapping there are four targets tg1,tg2,tg3 and tg4.
tg1 has a primary key,tg2 foreign key referencing the tg1's primary key,tg3 has
primary key that tg2 and tg4 refers as foreign key,tg2 has foreign key referencing
primary key of tg4 ,the order in which the informatica will load the target?
2]How can I detect aggregate tranformation causing low performance?
To optimize the aggregator transformation, you can use the following options.
Use incremental aggregation
Sort the ports before you perform aggregation
Avoid using aggregator transformation after update strategy, since it might be confusing.
88)Explain use of update strategy transformation
To flag source records as INSERT, DELETE, UPDATE or REJECT for target database.
Default flag is Insert. This is must for Incremental Data Loading.
89)How do you load the time dimension.
Time Dimension will generally load manually by using PL/SQL , shell scripts, proc C etc......
90)What are the types of data that passes between informatica server and stored
procedure?
Three types of data:-
Input/Out put parameters
Return Values
Status code.
91)What is update strategy transformation?
This transformation is used to maintain the history data or just most recent changes in to target table.
92)Why sorter transformation is an active transformation?
This is type of active transformation which is responsible for sorting the data either in the ascending order or descending order according to the key specifier. the port on which the sorting takes place is called as sortkeyport properties if u select distinct eliminate duplicates case sensitive valid for strings to sort the data null treated low null values are given least priority
93)How to append the records in flat file(Informatica) ? Where as in Datastage we have
the options
i) overwrite the existing file
ii) Append existing file
This is not there in Informatica v 7. but heard that its included in the latest version 8.0
where u can append to a flat file. Its about to be shipping in the market.
94)In which condtions we can not use joiner transformation(Limitaions of joiner
transformation)?
This is no longer valid in version 7.2 Now we can use a joiner even if the data is coming from the same source.
95)If you have four lookup tables in the workflow. How do you troubleshoot to improve
performance?
There r many ways to improve the mapping which has multiple lookups.
1) we can create an index for the lookup table if we have permissions(staging area).
2) divide the lookup mapping into two (a) dedicate one for insert means: source - target,,
these r new rows . only the new rows will come to mapping and the process will be fast .
(b) dedicate the second one to update : source=target,, these r existing rows. only the
rows which exists allready will come into the mapping.
3)we can increase the chache size of the lookup.
96)How many types of dimensions are available in informatica?
There r 3 types of dimensions
1.star schema
2.snowflake schema
3.glaxy schema
97)Where should U place the flat file to import the flat file defintion to the designer?
There is no such restrication to place the source file. In performance point of view its
better to place the file in server local src folder. if you need path please check the server
properties availble at workflow manager.
It doesn't mean we should not place in any other folder, if we place in server src folder by
default src will be selected at time session creation.
98)What is difference b/w Informatica 7.1 and Abinitio
There is a lot of diffrence between informatica an Ab Initio
In Ab Initio we r using 3 parllalisim
but Informatica using 1 parllalisim
In Ab Initio no scheduling option we can scheduled manully or pl/sql script
but informatica contains 4 scheduling options
Ab Inition contains co-operating system
but informatica is not
Ramp time is very quickly in Ab Initio campare than Informatica
Ab Initio is userfriendly than Informatica
99)How many types of facts and what are they?
Factless Facts:Facts without any measures.
Additive Facts:Fact data that can be additive/aggregative.
Non-Additive facts: Facts that are result of non-additon
Semi-Additive Facts: Only few colums data can be added.
Periodic Facts: That stores only one row per transaction that happend over a period of time.
Accumulating Fact: stores row for entire lifetime of event.
100)How to use the unconnected lookup i.e., from where the input has to be taken and
the output is linked? What condition is to be given?
The unconnected lookup is used just like a function call. in an expression output/variable port or any place where an expression is accepted(like condition in update strategy etc..),call the unconnected lookup...something like :LKP.lkp_abc(input_port).......(lkp_abc is the name of the unconnected lookup...(plz check the exact syntax)).....give the input value just like we pass parameters to functions, and it'll return the output after looking up.
101)What r the circumstances that infromatica server results an unreciverable session?
The source qualifier transformation does not use sorted ports.If u change the partition information after the initial session fails. Perform recovery is disabled in the informatica server configuration.
If the sources or targets changes after initial session fails.If the maping consists of sequence generator or normalizer transformation. If a concuurent batche contains multiple failed sessions.
102)What is power center repository?
The PowerCenter repository allows you to share metadata across repositories to create a data mart domain. In a data mart domain, yoYou can create a single global repository to store metadata used across an enterprise, and a number of local repositories to share the global metadata as needed.
103)Two relational tables are connected to SQ Trans,what are the possible errors it will
be thrown?
The only two possibilities as of I know is
Both the table should have primary key/foreign key relation ship
Both the table should be available in the same schema or same database
104)Which transformation should we use to normalize the COBOL and relational
sources?
The Normalizer transformation normalizes records from COBOL and relational sources, allowing you to organize the data according to your own needs. A Normalizer transformation can appear anywhere in a data flow when you normalize a relational source. Use a Normalizer transformation instead of the Source Qualifier transformation when you normalize a COBOL source. When you drag a COBOL source into the Mapping Designer workspace, the Normalizer transformation automatically appears,
creating input and output ports for every column in the source
105)What are the Differences between Informatica Power Center versions 6.2 and 7.1,
also between Versions 6.2 and 5.1?
The main difference between informatica 5.1 and 6.1 is that in 6.1 they introduce a new thing called repository server and in place of server manager(5.1), they introduce workflow manager and workflow monitor.
106) What is Load Manager?
The load Manager is the Primary Informatica Server Process. It performs the following tasks:-
Manages session and batch scheduling.
Locks the session and read session properties.
Reads the parameter file.
Expand the server and session variables and parameters.
Verify permissions and privileges.
Validate source and target code pages.
Create the session log file.
Create the Data Transformation Manager which execute the session.
107)What is the default join that source qualifier provides?
The Joiner transformation supports the following join types, which you set in the
Properties tab:
Normal (Default)
Master Outer
Detail Outer
Full Outer
108)How can we partition a session in Informatica?
The Informatica® PowerCenter® Partitioning option optimizes parallel processing on
multiprocessor hardware by providing a thread-based architecture and built-in data partitioning.
GUI-based tools reduce the development effort necessary to create data partitions and streamline ongoing troubleshooting and performance tuning tasks, while ensuring data integrity throughout the execution process. As the amount of data within an organization expands and real-time demand for information grows, the PowerCenter Partitioning option enables hardware and applications to provide outstanding performance and jointly scale to handle large volumes of data and users.
109)What is Datadriven?
The Informatica Server follows instructions coded into Update Strategy transformations within the session mapping to determine how to flag rows for insert, delete, update, or reject. If the mapping for the session contains an Update Strategy transformation, this field is marked Data Driven by default.
110)What is meant by lookup caches?
The informatica server builds a cache in memory when it processes the first row af a data in a cached look up transformation.It allocates memory for the cache based on the amount you configure in the transformation or session properties.The informatica server stores condition values in the index cache and output values in the data cache.
111)Define Informatica Repository?
The Informatica repository is a relational database that stores information, or metadata, used by the Informatica Server and Client tools. Metadata can include information such as mappings describing how to transform source data, sessions indicating when you want the Informatica Server to perform the transformations, and connect strings for sources and targets.The repository also stores administrative information such as usernames and passwords, permissions and privileges, and product version. Use repository manager to create the repository.The Repository Manager connects to the repository database and runs the code needed to create the repository tables.Thsea tables stores metadata in specific format the informatica server,client tools use.
112)Performance tuning in Informatica?
The goal of performance tuning is optimize session performance so sessions run during the available load window for the Informatica Server.Increase the session performance by following. The performance of the Informatica Server is related to network connections. Data generally moves across a network at less than 1 MB per second, whereas a local disk moves data five to twenty times faster. Thus network connections ofteny affect on session performance.So aviod netwrok connections. Flat files: If u’r flat files stored on a machine other than the informatca server, move those files to the machine that consists of informatica server. Relational datasources: Minimize the connections to sources ,targets and informatica server to improve session performance.Moving target database into server system may improve session performance. Staging areas: If u use staging areas u force informatica server to perform multiple datapasses. Removing of staging areas may improve session performance. You can run the multiple informatica servers againist the same repository.Distibuting the
session load to multiple informatica servers may improve session performance. Run the informatica server in ASCII datamovement mode improves the session performance.Because ASCII datamovement mode stores a character value in one byte.Unicode mode takes 2 bytes to store a character.
If a session joins multiple source tables in one Source Qualifier, optimizing the query may improve performance. Also, single table select statements with an ORDER BY or GROUP BY clause may benefit from optimization such as adding indexes. We can improve the session performance by configuring the network packet size,which allows data to cross the network at one time.To do this go to server manger ,choose server configure database connections. If u are target consists key constraints and indexes u slow the loading of data.To improve the session performance in this case drop constraints and indexes before u run the session and rebuild them after completion of session.Running a parallel sessions by using concurrent batches will also reduce the time of loading the data.So concurent batches may also increase the session performance.Partittionig the session improves the session performance by creating multiple connections to sources and targets and loads data in paralel pipe lines. In some cases if a session contains a aggregator transformation ,You can use incremental aggregation to improve session performance. Aviod transformation errors to improve the session performance. If the sessioin containd lookup transformation You can improve the session performance by enabling the look up cache. If U’r session contains filter transformation ,create that filter transformation nearer to the sources or You can use filter condition in source qualifier. Aggreagator,Rank and joiner transformation may oftenly decrease the session performance .Because they must group data before processing it.To improve session performance in this case use sorted ports option.
113)What is the procedure to write the query to list the highest salary of three
employees?
The following is the query to find out the top three salaries
in ORACLE:--(take emp table)
select * from emp e where 3>(select count (*) from emp where
e.sal>emp.sal) order by sal desc.
in SQL Server:-(take emp table)
select top 10 sal from emp
114)Can i run the mapping with out starting the informatica server?
The difference between cache and uncacheed lookup iswhen you configure the lookup transformation cache lookup it stores all the lookup table data in the cache when the first input record enter into the lookup transformation, in cache lookup the select statement executes only once and compares the values of the input record with the values in the cachebut in uncache lookup the the select statement executes for each input record entering into the lookup transformation and it has to connect to database each time entering the new record
115)What are the types of maping wizards that are to be provided in Informatica?
The Designer provides two mapping wizards to help you create mappings quickly and easily. Both wizards are designed to create mappings for loading and maintaining star schemas, a series of dimensions related to a central fact table. Getting Started Wizard. Creates mappings to load static fact and dimension tables, as well as slowly growing dimension tables.Slowly Changing Dimensions Wizard. Creates mappings to load slowly changing dimension tables based on the amount of historical dimension data you want to keep and the method you choose to handle historical dimension data.
116)What is the Rankindex in Ranktransformation?
The Designer automatically creates a RANKINDEX port for each Rank transformation. The Informatica Server uses the Rank Index port to store the ranking position for each record in a group. For example, if you create a Rank transformation that ranks the top 5 salespersons for each quarter, the rank index numbers the salespeople from 1 to 5.
117)In a filter expression we want to compare one date field with a db2 system field
CURRENT DATE.
Our Syntax: datefield = CURRENT DATE (we didn't define it by ports, its a system
field ), but this is not valid (PMParser: Missing Operator)..
The db2 date formate is "yyyymmdd" where as sysdate in oracle will give "dd-mm-yy"
so conversion of db2 date formate to local database date formate is compulsary. other
wise u will get that type of error
Print this post
No comments:
Post a Comment