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

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.

What is Data warehousing Architecture..?

Data warehousing includes two Phases 1) ETL 2) Reporting
The following Image shows both Phases….



The following Diagram shows a Detailed ETL PHASE….



We have data sources ODS1, ODS2, ODS3 and so on… These contain Operational Data or Real time data… These systems may locate in various places across geographical regions..
We extract the data from these data sources and we keep the data in Staging area for a while and we apply Data Transformations such as Data formatting, Data Cleansing, and Data aggregations. Then we load the transformed data from Staging area into Data warehouse,

A Data warehouse is just a Database contains Fact tables and Dimension Tables to form Star Schema..

What is Informatica and its Basic Interview Questions

Introduction to Informatica

Informatica is a tool, supporting all the steps of Extraction, Transformation and Load process. Now a days Informatica is also being used as an Integration tool.
Informatica is an easy to use tool. It has got a simple visual interface like forms in visual basic. You just need to drag and drop different objects (known as transformations) and design process flow for Data extraction transformation and load. These process flow diagrams are known as mappings. Once a mapping is made, it can be scheduled to run as and when required. In the background Informatica server takes care of fetching data from source, transforming it, & loading it to the target systems/databases.
Important Components of Informatica are:
Power Exchange
Power Centre
Power Centre Connect
Power Channel
Metadata Exchange
Power Analyser
Super Glue
Components of Informatica Client
Repository Manager : Manage the Informatica repository from the client machine
Designer: The Designer helps you create source definitions, target definitions, and
transformations to build a mapping
Workflow Manager : Allows user to execute a mapping created in the designer
Workflow Monitor : Tool that allows you to monitor workflows and tasks

                                                  Basic Interview Questions On Informatica:

What is Data Integration?
Data Integration is the process of combining data residing at different sources and providing the user with a unified view of these data.
Explain PowerCenter Repository?
Repository consist of database tables that store metadata. Metadata describes different types of objects , such as mappings or transformations , that you can create using PowerCenter Client tools. The interation service uses repository objects to extract , transform and load data. The repository also stores administrative information such as user names, passwords , permissions and previleges. When any task is performed through PowerCenter Client application such as creating users, analyzing sources , developing mapping or mapplets or creating workflows , Metadata is added to repository tables.
What is a Mapping?
A mapping is a set of source and target definitions linked by transformation objects that define the rules for data transformation. Mappings represent the data flow between sources and targets. When the Integration Service runs a session, it uses the instructions configured in the mapping to read, transform, and write data.
What is a mapplet?
A mapplet is a reusable object that contains a set of transformations and enables to reuse that transformation logic in multiple mappings.
While importing the relational source definition from database, what are the metadata of source you import?
Source name
Database location
Column names
Data types
Key constraints
How many ways you can update a relational source definition and what are they?
Two ways
1. Edit the definition
2. Re import the definition
Where should U place the flat file to import the flat file definition to the designer?
Place it in local folder
To provide support for Mainframes source data, which files r used as a source definitions?
COBOL files
Which transformation should u need while using the COBOL sources as source definitions?
Normalizer transformation is used to normalize the data. Since COBOL sources r often consists of Demoralized data.

Informatica Scenario Based Interview Questions with Answers

Informatica Scenarios

I have listed the following informatica scenarios which are frequently asked in the informatica interviews. These informatica scenario interview questions helps you a lot in gaining confidence in interviews.
1. How to generate sequence numbers using expression transformation?
Solution:
In the expression transformation, create a variable port and increment it by 1. Then assign the variable port to an output port. In the expression transformation, the ports are:
V_count=V_count+1
O_count=V_count
2. Design a mapping to load the first 3 rows from a flat file into a target?
Solution:
You have to assign row numbers to each record. Generate the row numbers either using the expression transformation as mentioned above or use sequence generator transformation.
Then pass the output to filter transformation and specify the filter condition as O_count <=3
3. Design a mapping to load the last 3 rows from a flat file into a target?
Solution:
Consider the source has the following data.
col
a
b
c
d
e
Step1: You have to assign row numbers to each record. Generate the row numbers using the expression transformation as mentioned above and call the row number generated port as O_count. Create a DUMMY output port in the same expression transformation and assign 1 to that port. So that, the DUMMY output port always return 1 for each row.
In the expression transformation, the ports are
V_count=V_count+1
O_count=V_count
O_dummy=1
The output of expression transformation will be
col, o_count, o_dummy
a, 1, 1
b, 2, 1
c, 3, 1
d, 4, 1
e, 5, 1
Step2: Pass the output of expression transformation to aggregator and do not specify any group by condition. Create an output port O_total_records in the aggregator and assign O_count port to it. The aggregator will return the last row by default. The output of aggregator contains the DUMMY port which has value 1 and O_total_records port which has the value of total number of records in the source.
In the aggregator transformation, the ports are
O_dummy
O_count
O_total_records=O_count
The output of aggregator transformation will be
O_total_records, O_dummy
5, 1
Step3: Pass the output of expression transformation, aggregator transformation to joiner transformation and join on the DUMMY port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.
In the joiner transformation, the join condition will be
O_dummy (port from aggregator transformation) = O_dummy (port from expression transformation)
The output of joiner transformation will be
col, o_count, o_total_records
a, 1, 5
b, 2, 5
c, 3, 5
d, 4, 5
e, 5, 5
Step4: Now pass the ouput of joiner transformation to filter transformation and specify the filter condition as O_total_records (port from aggregator)-O_count(port from expression) <=2
In the filter transformation, the filter condition will be
O_total_records – O_count <=2
The output of filter transformation will be
col o_count, o_total_records
c, 3, 5
d, 4, 5
e, 5, 5
4. Design a mapping to load the first record from a flat file into one table A, the last record from a flat file into table B and the remaining records into table C?
Solution:
This is similar to the above problem; the first 3 steps are same. In the last step instead of using the filter transformation, you have to use router transformation. In the router transformation create two output groups.
In the first group, the condition should be O_count=1 and connect the corresponding output group to table A. In the second group, the condition should be O_count=O_total_records and connect the corresponding output group to table B. The output of default group should be connected to table C.
5. Consider the following products data which contain duplicate rows.
A
B
C
C
B
D
B
Q1. Design a mapping to load all unique products in one table and the duplicate rows in another table.
The first table should contain the following output
A
D
The second target should contain the following output
B
B
B
C
C
Solution:
Use sorter transformation and sort the products data. Pass the output to an expression transformation and create a dummy port O_dummy and assign 1 to that port. So that, the DUMMY output port always return 1 for each row.
The output of expression transformation will be
Product, O_dummy
A, 1
B, 1
B, 1
B, 1
C, 1
C, 1
D, 1
Pass the output of expression transformation to an aggregator transformation. Check the group by on product port. In the aggreagtor, create an output port O_count_of_each_product and write an expression count(product).
The output of aggregator will be
Product, O_count_of_each_product
A, 1
B, 3
C, 2
D, 1
Now pass the output of expression transformation, aggregator transformation to joiner transformation and join on the products port. In the joiner transformation check the property sorted input, then only you can connect both expression and aggregator to joiner transformation.
The output of joiner will be
product, O_dummy, O_count_of_each_product
A, 1, 1
B, 1, 3
B, 1, 3
B, 1, 3
C, 1, 2
C, 1, 2
D, 1, 1
Now pass the output of joiner to a router transformation, create one group and specify the group condition as O_dummy=O_count_of_each_product. Then connect this group to one table. Connect the output of default group to another table.
Q2. Design a mapping to load each product once into one table and the remaining products which are duplicated into another table.
The first table should contain the following output
A
B
C
D
The second table should contain the following output
B
B
C
Solution:
Use sorter transformation and sort the products data. Pass the output to an expression transformation and create a variable port,V_curr_product, and assign product port to it. Then create a V_count port and in the expression editor write IIF(V_curr_product=V_prev_product, V_count+1,1). Create one more variable port V_prev_port and assign product port to it. Now create an output port O_count port and assign V_count port to it.
In the expression transformation, the ports are
Product
V_curr_product=product
V_count=IIF(V_curr_product=V_prev_product,V_count+1,1)
V_prev_product=product
O_count=V_count
The output of expression transformation will be
Product, O_count
A, 1
B, 1
B, 2
B, 3
C, 1
C, 2
D, 1
Now Pass the output of expression transformation to a router transformation, create one group and specify the condition as O_count=1. Then connect this group to one table. Connect the output of default group to another table