Informatica Data Warehousing Interview Questions And Answers

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

pmcmd is used to start a batch.

By using server manager or pmcmd.

EDW is Enterprise Datawarehouse which means that its a centralised DW for the whole organization.

this apporach is the apporach on Imon which relies on the point of having a single warehouse/centralised where the kimball apporach says to have seperate data marts for each vertical/department.

Advantages of having a EDW:

1. Golbal view of the Data
2. Same point of source of data for all the users acroos the organization.
3. able to perform consistent analysis on a single Data Warehouse.

to over come is the time it takes to develop and also the management that is required to build a centralised database.

Static cache:
We can not insert or update the cache.

Dynamic cache:
We can insert rows into the cache as we pass to the target.

Batch--- is a group of any thing

Different batches ----Different groups of different things.

There are two types of batches
1. Concurrent
2. Sequential

Manages the session and batch scheduling: Whe you start the informatica server the load maneger launches and queries the repository for a list of sessions configured to run
on the informatica server.When you configure the session the loadmanager maintains list of list of sessions and session start times.When you sart a session loadmanger fetches the session information from the repository to perform the validations and verifications prior to starting DTM process.

Locking and reading the session: When the informatica server starts a session lodamaager locks the session from the repository.Locking prevents you starting the session again and again.

Reading the parameter file: If the session uses a parameter files,loadmanager reads the parameter file and verifies that the session level parematers are declared in the file
Verifies permission and privelleges: When the sesson starts load manger checks whether or not the user have privelleges to run the session.

Creating log files: Loadmanger creates logfile contains the status of session.

As per my knowledge there is no such restriction to use this number of sources or targets inside a mapping.

Question is " if you make N number of tables to participate at a time in processing what is the position of your database. I organization point of view it is never encouraged to use N number of tables at a time, It reduces database and informatica server performance

Session - A Session Is A set of instructions that tells the Informatica Server How And When To Move Data From Sources To Targets. After creating the session, we can use
either the server manager or the command line program pmcmd to start or stop the session.
Batches - It Provides A Way to Group Sessions For Either Serial Or Parallel
Execution By The Informatica Server.
There Are Two Types Of Batches :
Sequential - Run Session One after the Other.
Concurrent - Run Session At The Same Time.

power center powermart

no.of repository n No. n No.

applicability high end WH low&mid range WH

global repository supported not supported

local repository supported supported

ERP support available not available

As explained above informatcia server has 3 methods to recovering the sessions.Use performing recovery to load the records from where the session fails.

Aggregator transformation allows you to perform aggregate calculations, such as averages and sums.

Reusable transformations can be used in multiple mappings.When you need to incorporate this transformation into maping, U add an instance of it to maping.Later if you change the definition of the transformation ,all instances of it inherit the changes.Since the instance of reusable transforamation is a pointer to that transforamtion, You can change the transforamation in the transformation developer,its instances automatically reflect these changes.This feature can save you great deal of work.

A reusable Transformation is a reusable metadata object , defined with business logic using single Transformation.

Two ways:-
1.Drag the port from another transforamtion
2.Click the add buttion on the ports tab.

Target Types are File, Relational and ERP.

When you edit,schedule the sesion each time,informatica server directly communicates the repository to check whether or not the session and users are valid.All the metadata of sessions and mappings will be stored in repository.

repository always stores the meta data which stores all informations about trasformations, sessions, mappings,
scheduling and user validation of the process.

Maping parameter represents a constant value that You can define before running a session.A mapping parameter retains the same value throughout the entire session.
When you 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 you run the session.

Unit testing are of two types

1. Quantitaive testing

2.Qualitative testing

Steps.

1.First validate the mapping

2.Create session on themapping and then run workflow.

Once the session is succeeded the right click on session and go for statistics tab.

There you can see how many number of source rows are applied and how many number of rows loaded in to targets and how many number of rows rejected.This is called Quantitative testing.

If once rows are successfully loaded then we will go for qualitative testing.

Steps

1.Take the DATM(DATM means where all business rules are mentioned to the corresponding source columns) and check whether the data is loaded according to the DATM in to target table.If any data is not loaded according to the DATM then go and check in the code and rectify it.

This is called Qualitative testing.

This is what a devloper will do in Unit Testing.

while importing flat file definetion just specify the scale for a neumaric data type. in the mapping, the flat file source supports only number datatype(no decimal and integer). In the SQ associated with that source will have a data type as decimal for that number port of the source.

source ->number datatype port ->SQ -> decimal datatype.Integer is not supported. hence decimal is taken care.

Import the field as string and then use expression to convert it, so that we can avoid truncation if decimal places inj source itself.

Normalizer: It is a transormation mainly using for cobol sources,

it's change the rows into coloums and columns into rows

Normalization:To remove the retundancy and inconsitecy

Normalizer Transformation can be used to obtain multiple columns from a single row.

The basic diff b/w inbetween informatica8.0and informatica7.0 is that in 8.0series informatica corp has introduces powerexchnage
concept

There r 3 types of dimensions

1.star schema

2.snowflake schema

3.glaxy schema

3 types.That are

1. SCD(slowly changing dimension) type1
2. SCD type2
3. SCD type3

The reject rows say for example due to unique key constrain is all pushed by session into the $PMBadFileDir (default relative path is <INFA_HOME/PowerCenter/server/infa_shared/BadFiles) which is configured with path at Integration Service level. Every Target will have property saying Reject filename which gives the file in which rejects rows are stored.

You should configure the mapping with the least number of transformations and expressions to do the most amount of work possible. You should minimize the amount of data moved by deleting unnecessary links between transformations.
For transformations that use data cache (such as Aggregator, Joiner, Rank, and Lookup transformations), limit connected input/output or output ports. Limiting the number of connected input/output or output ports reduces the amount of data the transformations store in the data cache.

You can also perform the following tasks to optimize the mapping:

Configure single-pass reading.
Optimize datatype conversions.
Eliminate transformation errors.
Optimize transformations.
Optimize expressions. You should configure the mapping with the least number of transformations and expressions to do the most amount of work possible. You should minimize the amount of data moved by deleting unnecessary links between transformations.
For transformations that use data cache (such as Aggregator, Joiner, Rank, and Lookup transformations), limit connected input/output or output ports. Limiting the number of connected input/output or output ports reduces the amount of data the transformations store in the data cache.

Java Transformation available in the 8x version and it is not available in 7x version.