Archive for the ‘Informatica Interview Questions’ Category

Archive for the ‘Informatica Interview Questions’ Category 

 

What is a FACT LESS FACT TABLE? Where we use Factless Fact?

We know that fact table is a collection of many facts and measures having multiple keys joined with one or more dimension tables. Facts contain both numeric and additive fields. But factless fact table are different from all these.
A factless fact table is fact table that does not contain fact. They contain only dimensional keys and it captures events that happen only at information level but not included in the calculations level. just an information about an event that happen over a period.
A factless fact table captures the many-to-many relationships between dimensions, but contains no numeric or textual facts. They are often used to record events or coverage information. Common examples of factless fact tables include:

• Identifying product promotion events (to determine promoted products that didn’t sell)
• Tracking student attendance or registration events
• Tracking insurance-related accident events
• Identifying building, facility, and equipment schedules for a hospital or university

Factless fact tables are used for tracking a process or collecting stats. They are called so because, the fact table does not have aggregate table numeric values or information. There are two types of factless fact tables: those that describe events, and those that describe conditions. Both may play important roles in your dimensional models.
Factless fact tables for events
The first type of factless fact table is a table that records an event. Many event-tracking tables in dimensional data warehouses turn out to be factless. Sometimes there seem to be no facts associated with an important business process. Events or activities occur that you wish to track, but you find no measurements. In situations like this, build a standard transaction-grained fact table that contains no facts.
For eg.

The above fact is used to capture the leave taken by an employee. Whenever an employee takes leave a record is created with the dimensions. Using the fact FACT_LEAVE we can answer many questions like

• Number of leaves taken by an employee
• The type of leave an employee takes
• Details of the employee who took leave

Factless fact tables for conditions
Factless fact tables are also used to model conditions or other important relationships among dimensions. In these cases, there are no clear transactions or events. It is used to support negative analysis report. For example a Store that did not sell a product for a given period.  To produce such report, you need to have a fact table to capture all the possible combinations.  You can then figure out what is missing.
For eg, fact_promo gives the information about the products which have promotions but still did not sell

Comparision of Repository Objects using Workflow Manager?

This  fact answers the below questions:

• To find out products that have promotions.
• To find out products that have promotion that sell.
• The list of products that have promotion but did not sell.

This kind of factless fact table is used to track conditions, coverage or eligibility.  In Kimball terminology, it is called a “coverage table.”
Note:
We may have the question that why we cannot include these information in the actual fact table .The problem is that if we do so then the fact size will increase enormously.
Factless fact table is crucial in many complex business processes. By applying you can design a dimensional model that has no clear facts to produce more meaningful information for your business processes. Factless fact table itself can be used to generate the useful reports.

In order to compare two repository objects of the same type we can use Workflow manager.Using this we can identify the difference between two objects like difference between two Email task.Comparison can be done across Folders and repositories.
Following objects can be compared but we cannot compare objects of different types. For example, you cannot compare an Email task with a Session task.

• Tasks
• Sessions
• Worklets
• Workflows

Steps for Comparing Objects

• Open the folders that contain the Repository objects you want to compare.
• Open the Workflow Manager
•  For tasks, Click Tasks > Compare,For Worklets:Worklets > Compare,For Workflows: Workflow > Compare.
• You can find a dialog box opened.Click Browse to select an object.
• And then Click Compare.

Checking Dependencies in Informatica Mappings

 

In Informatica we may have to remove the repository objects like mapping or sessions.In such cases we have to check the dependency before removing because one mapping may be dependent on many others, or one workflow can be dependent on many other workflows.Other wise it may cause problems.
To check the dependencies for a mapping you can directly go to the mapping and Click on “Check Dependencies”. This will give you all the dependencies related to a mapping.Here you can find source,target,mappings everything related to the mapping.This will also help in tracing the history of the mapping like the staging part and the source part of the mappings
Similarly you can check for the session and other repository objects in Workflow Manager and Designer. Also you can check the dependencies while checking the object history or checkouts.

Session Log Parameter in Informatica?

 

Session Log Parameter
The Workflow Manager provides a built-in session parameter named $PMSessionLogFile. Use $PMSessionLogFile in the session properties to change the name or location of the session log between runs. When you use $PMSessionLogFile in the session properties, define the parameter in the parameter file.
Changing the Session Log Name
You can use $PMSessionLogFile to change the session log name between sessions. In the General Options settings of the Properties tab, enter $PMSessionLogFile in the Session Log Filename field. Then define $PMSessionLogFile in the parameter file. When the PowerCenter Server runs the session, it creates a session log in the directory listed in the Session Log File Directory field and names the session log as instructed by the parameter file. If a session log with the same name already exists, the PowerCenter Server overwrites the existing file.
For example, in a session, you leave Session Log File Directory set to its default value, the $PMSessionLogDir server variable. For Session Log File Name, you enter the session parameter $PMSessionLogFile. In the parameter file, you set $PMSessionLogFile to “TestRun.txt”. When you registered the PowerCenter Server, you defined $PMSessionLogDir as C:/Program Files/Informatica/PowerCenter Server/SessLogs. When the PowerCenter Server runs the session, it creates a session log named TextRun.txt in the C:/Program Files/Informatica/PowerCenter Server/SessLogs directory.
Steps for Using $PMSessionLogFile
Use $PMSessionLogFile when you want to change the name and/or location of a session log between session runs.
To use the session log parameter:

1. In the session properties, click the General Options settings of the Properties tab.
2. Enter $PMSessionLogFile in the Session Log File field.
3. If you want $PMSessionLogFile to represent both the session log name and directory, clear the Session Log File Directory field.
4. Enter a parameter file and directory in the Parameter File Name field.
5. Click OK.

Before you run the session, create the parameter file in the specified directory and define $PMSessionLogFile.

 

How to Change the Session Log Name in Informatica Workflow?

Whenever we run the session in Informatica, integration service creates session log file. By default Session Log File Directory defaults to the service process variable, $PMSessionLogDir and the Session Log File Name defaults to $PMSessionLogFile.
In order to change the session log name you can set the $PMSessionLogFileto Session_test.txt in the parameter file. When you make this change in the parameter file and run the session, the integration service creates a session log file named Session_test.txt

How do we create Custom Error Log in Informatica and Which function is used?

 

ERROR:
The ERROR function causes the Informatica Server to skip a record and throws an error message defined by the user. It is available in the Designer.
ERROR( string )
Example : The following example shows how you can reference a mapping that calculates the average salary for employees in all departments of your company, but skips negative values. The following expression nests the ERROR function in an IIF expression so that if the Informatica Server finds a negative salary in the Salary port, it skips the row and displays an error:
IIF( SALARY < 0, ERROR (‘Error. Negative salary found. Row skipped.’, EMP_SALARY )
SALARY RETURN VALUE
10000 10000
-15000 ‘Error. Negative salary found. Row skipped.’
The below example combines two special functions, a test Function and a conversion function.
IIF(IS_DATE(DATE_PROMISED,’MM/DD/YY’),TO_DATE(DATE_PROMISED),
ERROR(‘Invalid Date’))

What is CURRVAL in Sequence Generator Transformation?

 

CURRVAL = NEXTVAL + Increment by Value property
If you connect CURRVAL port without connecting the NEXTVAL, then the CURRVAL will give you constant value.
If you connect CURRVAL, Informatica process one row at a time – can be a performance issue.

What is NEXTVAL in Sequence Generator Transformation?

 

NEXTVAL – generates sequence numbers when you connect this port to a transformation.
If you want to load 2 targets with the same sequence number
- Connect the NEXTVAL port to a transformation and then connect that transformation to the 2 targets.
If you want to load 2 targets with the unique sequence number
- Connect the NEXTVAL port to two different targets or two different pipelines which has those 2 targets.

What is purpose of Number of Cached Values in Sequence Generator Transformation?

 

Non-Reusable – If the Value is 0 – It doesnt Cache Values. Informatica reads the start value form the repository and then keeps generating the sequence values. At the ene of the session it updates the current value to Start Value or Lastgeneratedsequence+1 (based on the Reset Option)
If the Value is > 0 then Informatica reads the start value and caches the number of values based on the Number of Cached Values and then updates the Current value in the repository. It will again go to the Repository if all the values are used. At the end of the session it throws away any unremaining Sequence Numbers which were not used.

What Commit Interval does?

 

In INFA the Commit Interval Property help Integration Service commits rows to targets based on the commit interval.

 

If the session fails for some reason then,the Integration Service rolls back to the last commit point, The roll back depends on the commit interval configured in the session.

 

In your case, set the Commit interval = 90, and verify it.

 

There are two Commit types in INFA

1.Target Commit Interval

2.Source Commit Interval.