Informatica 10 Oline Training

To Learn Informatica SQL Basics are enough

Informatica is a very powerful tool for Reporting in a datawarehousing. ETL Informatica Online Training Course at MonsterCourses will help you make smarter generate reports easily. You will master the various techniques to convert raw data into meaningful information.

* Understanding the concepts of Data Warehousing

* Ability to design and develop complex mappings

* Configuring Workflows as in real time environment

* Troubleshooting errors/failures

* Gain hands-on experience with multiple exercises and various real time scenarios and a Demo Project

* Build confidence to excel your career as an efficient Informatica Developer/Consultant

What is Informatica

If you want to set up a Data Warehouse – then you will love to have Informatica software. It will greatly simplify DW design, and numerous routine tasks related to data transformation and migration (ETL – Extract, Transform, and Load), day-to-day maintenance and management.

Informatica has a simple visual interface. You do most of the work by simply dragging and dropping with your mouse in the Designer. This graphical approach makes it also very easy to understand what is going on (it is “self-documenting” in a sense).

Informatica can communicate with all major databases, can move/transform data between them. It can move huge volumes of data in a very effective way. It can throttle the transactions (do big updates in small chunks to avoid long locking and filling the transactional log). It can effectively do joins between tables in different databases on different servers. The tasks are performed by Informatica Server (Unix or MS Windows). You get a client application called “Server Manager” to work with the server.

You design your processes in a client application called “Designer“. This is where you you tell what the source databases and tables will be, what will be the targets, and how you move/transform the data.

Informatica uses its own database called “Metadata Repository Database”, or simply a Repository. Repository stores the data (rules) needed for data extraction, transformation, loading, and management. You get a client application “Repository Manager” to work with the repository.

Informatica comes in different packages:

• Informatica PowerCenter license – has all options, including distributed metadata, ability to organize repositories into a data mart domain and share metadata accross repositories.
• PowerMart PowerMart – a limited license (all features except distributed metadata and multiple registered servers).

Working with Informatica
Here are the pieces of the puzzle:
• source database(s), target database(s), repository metadata database
• Informatica server
• PC-based client software (Designer, Server Manager, Repository Manager)
Setting everything up is also straighforward. Once the server components are installed and configured, you install the client applications, configure ODBC, register the Informatica Server in the Server Manager. Create a Repository, create users and groups, edit users profiles. Add source and target definitions, set up mapping between the sources and targets, create a session for each mapping – and run sessions (resulting in writing data to targets).

Repository Manager
The Repository Manager allows you navigate through multiple repositories and folders inside the repositories. Navigating it is very similar to navigating standard MS Windows Explorer. You have expandable tree on the left (Navigator Window) – and list of details of the objects in the selected folder (Main Window).

Repository Login
Folders main contain Nodes (subfolders) – Sessions, Batches, Sources, Targets, Transformations, Mapplets (reusable sets of transformations) and Mappings. They in turn may contain corresponding individual repository objects – sessions, batches, sources, targets, transformations, mapplets and mappings, as well as shortcuts, batches, and session logs.
Interface is simple and intuitive, For example, to see the properties of an object – right-click on it – and select Properties. To create a new repository – choose Repository-Create Repository (you have to run in admin mode to be able to do this). Etc. You can reorder the columns in the main window by dragging, and sort by any column (just click on the corresponding header). The set of columns in the main window is different for each kind of the node or object.
Note: PowerMart Repositories are standalone. PowerCenter repositories can be standalone, local, or global.

• Repositories – create, backup, copy, restore, upgrade, and delete repositories.
• Users & Groups – (choose Security menu) – Create, edit, and delete repository users and groups, assign and revoke repository privileges (on a group or user level), and folder permissions, view locks – and unlock objects, versions, and folders. Privelege types:
o Session Operator
o Use Designer
o Browse Repository
o Create Sessions and Batches
o Administer Repository
o Administer Server
o Super User

• Folders – (choose Folder menu) – create/edit/delete folders inside repositories, copy within a repository or between repositories. Folders can be shared or not shared.
• Reports – add/remove reports
• Import/export repository connection info
• Analyze source/target, mapping, shortcut dependencies.
• Search by keyword
• View properties of repository objects
• Customize the Repository Manager (add, edit, remove repositories in the Navigator, edit repository connection info, view/hide windows)
Below the Navigator and the Main Window you may see two more windows:
• Dependency window – to see dependencies for a selected object (source-target, mapping, shortcut dependencies).
• Output window – shows what is happening.

Designer Client Application
Designer consists of several tools (choose Tools menu):
• Source Analyzer – (choose Sources menu) to import or create source definitions for flat file, Cobol, ERP, and Relational Databases. Note – double-click on the title-bar opens a pop-up to edit definitions.
• Warehouse Designer – to import or create target definitions (choose Targets – Generate/Execute SQL, or Targets-Create to create manually).
• Transformation Developer – to create reusable transformations.
• Mapplet Designer – to create mapplets (reusable sets of transformations)
• Mapping Designer – to create mappings (m_somename).
Windows:
• Navigator – to connect and word with multiple repositories and folders, copy objects and create shortcuts.
• Workspace – to view/edit sources, targets, mapplets, transformations, and mappings.
• Output window and Status bar
• Overview (choose View-Overview) – optional window – to simplify wiewing workbooks containing large mappings or large number of objects.

Performance tuning in Informatica?

Answer:

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 often affect on session performance. So avoid network connections.

Flat files: If u’r using flat files stored on a machine other than the informatica server, move those files to the machine that consists of informatica server.

Relational data sources: 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 data passes. Removing of staging areas may improve session performance.

You can run the multiple informatica servers against the same repository. Distributing the session load to multiple informatica servers may improve session performance.

Run the informatica server in ASCII data movement mode improves the session performance. Because ASCII data movement 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 you 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 session by using concurrent batches will also reduce the time of loading the data. So concurrent batches may also increase the session performance.

Partitioning the session improves the session performance by creating multiple connections to sources and targets and loads data in paralel pipe lines.

What is Session and Batches?
Answer:
Session – A Session Is A set of an instruction 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.

Question:
What is Router transformation?
Answer:
Router transformation allows you to use a condition to test data. It is similar to filter transformation. It allows the testing to be done on one or more conditions.

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

How can you access the remote source into your session?
Answer:
Relational source: To access relational source that is situated in a remote place, u need to configure database connection to the data source.
File Source: To access the remote source file you must configure the FTP connection to the host machine before you create the session.
Heterogeneous: When U’r maping contains more than one source type, the server manager creates a hetrogenous session that displays source options for all types.

What is a command that used to run a batch?

pmcmd is used to start a batch.

Where should you place the flat file to import the flat file definition to the designer?
Answer:
Place it in local folder.

Why we use partitioning the session in informatica?
Answer:
Partitioning achieves the session performance by reducing the time period of reading the source and loading the data into target.

What is parameter file?
Answer:
Parameter file is to define the values for parameters and variables used in a session. A parameter file is a file created by text editor such as word pad or notepad. You can define the following values in parameter file: –
Mapping parameters
Mapping variables
session parameters.