Services Behind Scene

INTEGRATION SERVICE PROCESS
The Integration Service starts an Integration Service process to run and monitor workflows. The Integration Service process accepts requests from the Power Center Client and from pmcmd. It performs the following tasks:

• Manages workflow scheduling.
• Locks and reads the workflow.
• Reads the parameter file.
• Creates the workflow log.
• Runs workflow tasks and evaluates the conditional links connecting tasks.
• Starts the DTM process or processes to run the session.
• Writes historical run information to the repository.
• Sends post-session email in the event of a DTM failure.

LOAD BALANCER
The Load Balancer is a component of the Integration Service that dispatches tasks to achieve optimal performance and scalability. When we run a workflow, the Load Balancer dispatches the Session, Command, and predefined Event-Wait tasks within the workflow.
The Load Balancer dispatches tasks in the order it receives them. When the Load Balancer needs to dispatch more Session and Command tasks than the Integration Service can run, it places the tasks it cannot run in a queue. When nodes become available, the Load Balancer dispatches tasks from the queue in the order determined by the workflow service level.
DTM PROCESS
Informatica online training - Informatica Jobs
When the workflow reaches a session, the Integration Service process starts the DTM process. The DTM is the process associated with the session task. The DTM process performs the following tasks:

• Retrieves and validates session information from the repository.
• Performs pushdown optimization when the session is configured for pushdown optimization.
• Adds partitions to the session when the session is configured for dynamic partitioning.
• Expands the service process variables, session parameters, and mapping variables and parameters.
• Creates the session log.
• Validates source and target code pages.
• Verifies connection object permissions.
• Runs pre-session shell commands, stored procedures, and SQL.
• Sends a request to start worker DTM processes on other nodes when the session is configured to run on a grid.
• Creates and run mapping, reader, writer, and transformation threads to extract, transform, and load data.
• Runs post-session stored procedures, SQL, and shell commands.
• Sends post-session email.

PROCESSING THREADS
The DTM allocates process memory for the session and divides it into buffers. This is also known as buffer memory. The default memory allocation is 12,000,000 bytes.
The DTM uses multiple threads to process data in a session. The main DTM thread is called the master thread.
The master thread can create the following types of threads:

•  Mapping Threads: One mapping thread for each session.
•  Pre- and Post-Session Threads: One thread created.
•  Reader Threads: One thread for each partition
•  Transformation Threads: One thread for each partition
•  Writer Threads: One thread for each partition

CODE PAGES and DATA MOVEMENT
A code page contains the encoding to specify characters in a set of one or more languages. An encoding is the assignment of a number to a character in the character set.
The Integration Service can move data in either ASCII or Unicode data movement mode. These modes determine how the Integration Service handles character data.
We choose the data movement mode in the Integration Service configuration settings. If we want to move multi byte data, choose Unicode data movement mode.
ASCII Data Movement Mode: In ASCII mode, the Integration Service recognizes 7-bit ASCII and EBCDIC characters and stores each character in a single byte.
Unicode Data Movement Mode: Use Unicode data movement mode when sources or targets use 8-bit or multi byte character sets and contain character data.

Try your Hand’s on Admin-Console

Try your Hand’s on Admin-Console
Repository Manager Tasks:

• Add domain connection information
• Add and connect to a repository
• Work with Power Center domain and repository connections
• Search for repository objects or keywords
• View object dependencies
• Compare repository objects
• Truncate session and workflow log entries
• View user connections
• Release locks
• Exchange metadata with other business intelligence tools

Add a repository to the Navigator, and then configure the domain connection information when we connect to the repository.
1.Adding a Repository to the Navigator :
1. In any of the Power Center Client tools, click Repository > Add.

2. Enter the name of the repository and a valid repository user name.
3. Click OK.
Before we can connect to the repository for the first time, we must configure the Connection information for the domain that the repository belongs to.

2.Configuring a Domain Connection

1. In a Power Center Client tool, select the Repositories node in the Navigator.
2. Click Repository > Configure Domains to open the Configure Domains dialog box.
3. Click the Add button. The Add Domain dialog box appears.
4. Enter the domain name, gateway host name, and gateway port number.
5. Click OK to add the domain connection.

Informatica online training - Informatica Jobs
3.Connecting to a Repository

1. Launch a Power Center Client tool.
2. Select the repository in the Navigator and click Repository > Connect, or double-click the repository.
3. Enter a valid repository user name and password.
4. Click Connect.

Click on more button to add, change or view domain information.

4.Viewing Object Dependencies
Before we change or delete repository objects, we can view dependencies to see the impact on other objects. For example, before you remove a session, we can find out which workflows use the session. We can view dependencies for repository objects in the Repository Manager, Workflow Manager, and Designer tools.
Steps:

1. Connect to the repository.
2. Select the object of use in navigator.
3. Click Analyze and Select the dependency we want to view.

5.Validating Multiple Objects
We can validate multiple objects in the repository without fetching them into the workspace. We can save and optionally check in objects that change from invalid to valid status as a result of the validation. We can validate sessions, mappings, mapplets, workflows, and worklets.
Steps:

1. Select the objects you want to validate.
2. Click Analyze and Select Validate
3. Select validation options from the Validate Objects dialog box
4. Click Validate.
5. Click a link to view the objects in the results group.

6.Comparing Repository Objects
We can compare two repository objects of the same type to identify differences between the objects. For example, we can compare two sessions to check for differences. When we compare two objects, the Repository Manager displays their attributes.
Steps:

1. In the Repository Manager, connect to the repository.
2. In the Navigator, select the object you want to compare.
3. Click Edit > Compare Objects.
4. Click Compare in the dialog box displayed.

7.Truncating Workflow and Session Log Entries
When we configure a session or workflow to archive session logs or workflow logs, the Integration Service saves those logs in local directories. The repository also creates an entry for each saved workflow log and session log. If we move or delete a session log or workflow log from the workflow log directory or session log directory, we can remove the entries from the repository.
Steps:

1. In the Repository Manager, select the workflow in the Navigator window or in the Main window.
2. Choose Edit > Truncate Log. The Truncate Workflow Log dialog box appears.
3. Choose to delete all workflow and session log entries or to delete all workflow and session log entries with an end time before a particular date.
4. If you want to delete all entries older than a certain date, enter the date and time.
5. Click OK.

8.Managing User Connections and Locks
In the Repository Manager, we can view and manage the following items:
Repository object locks: The repository locks repository objects and folders by user. The repository creates different types of locks depending on the task. The Repository Service locks and unlocks all objects in the repository.
User connections: Use the Repository Manager to monitor user connections to the repository. We can end connections when necessary.
Types of locks created:
1. In-use lock: Placed on objects we want to view
2. Write-intent lock: Placed on objects we want to modify.
3. Execute lock: Locks objects we want to run, such as workflows and sessions
Steps:

1. Launch the Repository Manager and connect to the repository.
2. Click Edit > Show User Connections or Show locks
3. The locks or user connections will be displayed in a window.
4. We can do the rest as per our need.

9.Managing Users and Groups

1. In the Repository Manager, connect to a repository.
2. Click Security > Manage Users and Privileges.
3. Click the Groups tab to create Groups. or
4. Click the Users tab to create Users
5. Click the Privileges tab to give permissions to groups and users.
6. Select the options available to add, edit, and remove users and groups.

There are two default repository user groups:
Administrators: This group initially contains two users that are created by default.
The default users are Administrator and the database user that created the repository. We cannot delete these users from the repository or remove them from the Administrators group.
Public: The Repository Manager does not create any default users in the Public group.
10 Working with Folders
We can create, Edit or delete folder as per our need.

1. In the Repository Manager, connect to a repository.
2. Click Folder > Create.

Enter the following information:

3. Click ok.
Informatica online training - Informatica Jobs

Difference Between 7.1 and 8.6

1. Target from Transformation: In Informatica 8X we can create target from Transformation by dragging transformation in Target designer
2. Pushdown optimization: Uses increased performance by pushing Transformation logic to the database by analyzing the transformations and Issuing SQL statements to sources and targets. Only processes any Transformation logic that it cannot push to the database.
3. New function in expression editor: New function have been introduced in Informatica 8X like reg_extract and reg_match
4. Repository query available in both versioned and non versioned Repositories previously it was available only for versioned repository.
5. UDF (User defined function) similar to macro in excel
6. FTP: We can have partitioned FTP targets and Indirect FTP File source (with file list).
7. Propagating Port Descriptions: In Informatica 8 we can edit a port description and propagate the description to other transformations in the mapping.
8. Environment SQL Enhancements: Environment SQL can still be used to Execute an SQL statement at start of connection to the database. We can Use SQL commands that depend upon a transaction being opened during The entire read or write process. For example, the following SQL command Modifies how the session handles characters: Alter session set
NLS_DATE_FORMAT=’DD/MM/YYYY';”.
9. Concurrently write to multiple files in a session with partitioned targets.
10. Flat File Enhancements:

• Reduced conversion of data types
• Delimited file performance has improved
• Flat file now can have integer and double data types
• Data can be appended to existing flat files

Informatica power center 8 is having the following features which makes it more powerful, easy to use and manage when compared to previous versions.

• Supports Service oriented architecture
• Access to structured, unstructured and semi structured data
• Support for grid computing
• High availability
• Pushdown optimization
• Dynamic partitioning
• Metadata exchange enhancements
• Team based Development
• Global Web-based Admin console
• New transformations
• 23 New functions
• User defined functions
• Custom transformation enhancements
• Flat file enhancements
• New Data Federation option
• Enterprise GRID

Steps to install Informatica Power Center Server 8.6

Steps to install Informatica Power Center Server 8.6

 

Steps to install Informatica Power Center Server 8.6

Pre-requisites:
1)Window xp sp2 ( You can also install the same on any high end OS ).
2)At least one database(here we assume you to have Oracle XE pre installed ).
3)2 GB Ram Recommended

Informatica 8.6 Server Installation:
1) Select the sever folder from the informatica installation CD.
2)There will find set up, click on the set up file.
3)click on Next button.





4)Select the license key by using browse button.It will be available the fallowing location:…\Informatica 8.6\RAWANA
5)Click on Next button




6)Click on Next button.




7)Select complete radio button and click on Next button.
8)Leave the defaults and select next button.

9)Click on Install button

10) Choose create new domain radio button, and click on Next button.

• Select database type
• Database URL: ComputerName:portno

Eg:windows-4dc4631:1521

• To find the computer name.. Click on Start->Right click on My computer->Properties->Computer Name.
• Provide the database credentials along with the Service name.
• Find database service name:
• Control Panel–Administrative tools–Services

• Here XE is the service name.
• For Oracle 10g users you will find the service name like :OracleServiceORCL then service name would be ORCL.

Note:SQL Server users they can give the name of the server name in the above field instead of service name.

• Click on Test connection:If this succeed then it will take you to the further step.

11. Leave the defaults, enter password and confirm password and click on next button.

12.Uncheck the check box and click on next button.

13. Click on Done.
14.After finishing installation need to run the services
Run the Informatica services:
windows+r->services.msc-> Locate the Informatica services –> Start them.

Configure the repository service and Integration service:

• To create these two above, please follow the below steps.

• Give your Credentials

• Select administrative Console. Make sure Domain is running.

• Make sure node is also running.From create drop down select repository service.

1)Enter any service name.
2)Select License.
3)Select database type as oracle (if your sys contains oracle db)
4)Select node for which you want to create repository.
5)Leave the code page as default.
6)Connection string if ORCL for oracle users(Make sure connection string should be same as the service name what is given while installing server)
7)Enter Database Credentials
8)Leave other defaults as well.
9)Click on create button.
10)Enable the repository service.

• Now Create content for the repository by selecting drop down shown below.

Note:Make sure Processes are enabled and repository service is enabled.
Creation of Integration service:


1)Give any name for Integration sevice.
2)Assign node to the IS.
3)Assign Repository to the IS.
4)Enter the rep.username and password.
Note:make this username and password should same as username and password of the Repository service.
5.Select data movement mode as ASCII.

In the process tab of the IS, enable the process.
Before enable, edit this section and select “Code page” as shown above.
Now Install client.
Open Informatica Repository Manager.


Select the RS from the RHS pane and click on OK.


Connect to the repository service.

Enter the repository username and password.

Informatica Interview Questions on Sorter Transformation - Quontra Solutions

Informatica Interview Questions on Sorter Transformation - Quontra Solutions

1. What is a sorter transformation?

Sorter transformation is used to sort the data. You can sort the data either in ascending or descending order according to a specified sort key.

2. Why sorter is an active transformation?

As sorter transformation can suppress the duplicate records in the source, it is called an active transformation.

3. How to improve the performance of a session using sorter transformation?

Sort the data using sorter transformation before passing in to aggregator or joiner transformation. As the data is sorted, the integration service uses the memory to do aggregate and join operations and does not use cache files to process the data.

Informatica online training - Informatica Jobs

Generate rows based on a column value - Informatica

Q) How to generate or load values in to the target table based on a column value using informatica etl tool.

I have the products table as the source and the data of the products table is shown below.

Table Name: Products
Product  Quantity
-----------------
Samsung  NULL
Iphone   3
LG       0
Nokia    4

Now i want to duplicate or repeat each product in the source table as many times as the value in the quantity column. The output is

product  Quantity
----------------
Iphone   3
Iphone   3
Iphone   3
Nokia    4
Nokia    4
Nokia    4
Nokia    4

The Samsung and LG products should not be loaded as their quantity is NULL, 0 respectively.

Now create informatica workflow to load the data in to the target table?
Solution:
Informatica online training - Informatica Jobs
Follow the below steps

• Create a new mapping in the mapping designer
• Drag the source definition in to the mapping
• Create the java transformation in active mode
• Drag the ports of source qualifier transformation in to the java transformation.
• Now edit the java transformation by double clicking on the title bar of the java transformation and go to the "Java Code" tab.
• Enter the below java code in the "Java Code" tab.

if (!isNull("quantity"))
{
  double cnt = quantity;
  for (int i = 1; i <= quantity; i++)
  {
    product = product;
    quantity = quantity;
    generateRow();
  }
}

• Now compile the java code. The compile button is shown in red circle in the image.
• Connect the ports of the java transformation to the target.
• Save the mapping, create a workflow and run the workflow.

Flat file header row, footer row and detail rows to multiple tables

Assume that we have a flat file with header row, footer row and detail rows. Now Lets see how to load header row into one table, footer row into other table and detail rows into another table just by using the transformations only.

First pass the data from source qualifier to an expression transformation. In the expression transformation assign unique number to each row (assume exp_count port). After that pass the data from expression to aggregator. In the aggregator transformation don't check any group by port. So that the aggregator will provide last row as the default output (assume agg_count port).

Now pass the data from expression and aggregator to joiner transformation. In the joiner select the ports from aggregator as master and the ports from expression as details. Give the join condition on the count ports and select the join type as master outer join. Pass the joiner output to a router transformation and create two groups in the router. For the first group give the condtion as exp_count=1, which gives header row. For the second group give the condition as exp_count=agg_count, which gives the footer row. The default group will give the detail rows.Informatica online training - Informatica Jobs