Informatica Repository Query to Find All the Source and Target connections in Your Respository

The below Informatica repository query can be used to get the Source Connections, Target connections of all the instances used in the informatica.

Apart from that we have details of the Folder, mapping name ,session name , and the connection name as shown in Informatica.

SELECT DISTINCT 

C.SUBJECT_AREA FOLDER_NAME, 
C.MAPPING_NAME MAPPING_NAME, 
C.SESSION_NAME SESSION_NAME, 
E.READER_WRITER_TYPE, 
E.INSTANCE_NAME, 
E.CNX_NAME, 

CASE 
WHEN E.WIDGET_TYPE = 2 
THEN 'TARGET CONNECTION' 
ELSE 
CASE 
WHEN E.WIDGET_TYPE IN (1, 3, 56, 45, 55, 84) 
THEN 
'SOURCE CONNECTION' 
ELSE 
NULL 
END 
END CNX_TYPE 

FROM INFA_REPO.REP_VERSION_PROPS A, 
     INFA_REPO.REP_USERS B, 
     INFA_REPO.REP_LOAD_SESSIONS C, 
     INFA_REPO.REP_REPOSIT_INFO D, 
     INFA_REPO.REP_SESS_WIDGET_CNXS E 

WHERE ( A.USER_ID = B.USER_ID 
AND C.SESSION_ID <> D.REPOSITORY_ID 
AND C.SESSION_ID = A.OBJECT_ID 
AND C.SUBJECT_ID = A.SUBJECT_ID 
AND A.OBJECT_TYPE = 68 
AND C.SESSION_ID = E.SESSION_ID 
AND C.SESSION_VERSION_NUMBER = 
E.SESSION_VERSION_NUMBER)

Using the Copy As Command in Informatica Designer

In Informatica once mapping is created and if we want to overwrite on the same mapping then we need to create a copy of that mapping as a backup. For creating a copy we need to use Copy as command. When we create a mapping using Copy as command then another mapping same as that of the original is created. This reduces the complexity in duplicating a mapping

To use the Copy As command to copy a mapping:

• Open a mapping in the Mapping Designer
• Click Mappings > Copy As.
• It will prompt for entering a new name. Enter a new mapping name
• Click OK.

What is a Pipeline in Informatica

Pipeline is nothing but the flow of different components in Informatica.A mapping in Informatica may contain Source, Transformations , Targets connected together, all these together will be considered as a pipeline. There will be many such pipelines in a single mapping .

One pipeline can be connected to another pipeline also; in that case it will be considered as a single pipeline

As seen in the below figure, Source, Transformations, Target are there in a pipeline.

To order the pipeline, there is a option in Designer i.e. Mappings>Target> Load Order

Reusable Transformations in Informatica

What is reusable transformation?

Transformations that can be reused in multiple mappings or mapplets within a folder, repository, or repository domain. This is developed using Transformation Developer to create this.Instead of creating a new transformation we can make the transformation Reusable and use different instances of the same reusable transformations in to different mapplets and mappings. There is no limit to the number of reusable transformations you can use.

Informatica Basic Features- Power Centre ,Power Mart

Informatica is one of the most powerful ETL tool with 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).

It 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.

 

General Layout of mapping :

SOURCE --> SOURCE QUALIFIER -->TRANSFORMATIONS ---> TARGET

 

Products:

Comprised of the flagship Informatica PowerCenter® and Informatica PowerMart® software products, as well as expanded connectivity solutions that extend even across firewalls, this data integration platform helps companies integrate and analyze data from virtually any corporate system, including ERP, CRM, supply chain, procurement, eBusiness, mainframe and legacy systems. The Informatica platform provides real-time performance, scalability and extensibility to handle the analytic requirements of even the largest enterprises.

They have different products.

• Power Center
• Power Mart
• Power Connect
• Power Channel
• Power Plug

Power Center & Power Mart :

The most 2 important products are Power Center & Power Mart. Power Mart is a subset of Power Center. Power Center has better load balancing.

Power Center : is the world-standard enterprise data integration platform for the creation and real-time management of data warehouse infrastructures and analytic applications.

• Distributed metadata (Repository)
• Global & Local Repositories (can register more than one Informatica server & can share the information in the global repository). Multiple repositories, many servers.
• Can connect to varied sources like Peoplesoft, SAP etc.
• Cost is more
• Has bridges which can transport meta data from other tools (like Erwin)

Power Mart : is a departmental data integration platform and can be expanded as a company's needs grow to an enterprise level with Informatica PowerCenter 5

• Only Local Repository. (Can’t register other repositories). Single repository, one server.
• Cannot connect to varied sources like Peoplesoft, SAP etc.
• Cost is less

Mappings : defines the flow of data from source to target database. A mapping contains more than one transformation. After getting the Source & Target, we define the mappings. The definitions of the mappings are stored in the repository.

Informatica Repository Server : A server used for managing the information with in the meta data repository. All the clients will be communicated with the Repository server. The information in the repository is managed by Repository Server.

When ever we are working with client tool, the first task is logon to repository. We need to have a valid user to logon. The communication between the client tools & Repository Server is TCP/IP

Informatica Server : is responsible for loading of data.Parts of the Informatica Server :

1.. Load Manager

2. Data Transformation Manager

3. Reader

4. Writer

Informatica Repository : There are 2 types of repositories. Global & Local repositories(with reference to Power Center). With Power Mart only one local repository. The information of all local repositories are located in Global repository. Shared information can be put in global repository.

Folders : To organize the files (like containers). Use to organize the information with in the repository. There can be one or more folders with in a repository. Each folder is independent of the other. Whenever we are going to work, work with in the folder. A folder contains a pre-defined set of sub folders.

 

• Sources (Contains the source database object definitions)
• Targets (Target db object definitions)
• Dimensions (Dimension definitions)
• Cubes (Cube definitions)
• Transformations (contains reusable transformation definitions)
• Mapplets (A mapplet is a reusable part of a mapping which contains one or more reusable transformations. Contains the mapplet definitions)
• Mappings (Mapping definitions)
• Business Components (Business is classified as far as the business usage)
• Sessions (contains session definitions. A session is a task based on a mapping)

Informatica PowerCenter : has different tools

 Repository Manager

Can create and manage repository and repository objects(Users& Folders). Can take back up and perform recoveries. Can perform security related issues. Permissions etc.

Two different admin users created for repository are Administrator and the database user of repository schema

Two default user groups created in the repository are Administrator and Public

 

Designer

• define / create the source database objects
• define / create target database objects
• define transformations
• define mapplets
• define mappings
• validate the mappings & transformations
• debug the sessions
• compare different objects
• define the dimensions & cubes

Source Database : Basically from where we are going to retrieve the data. Source is an existing one. The source is read only. Ex : Flat files, XML files, Cobol files, Oracle, SQL Server, Sybase, Informix, DB/2, ERP(SAP, People Soft), CRM, ODBC.From the client we connect to the source through ODBC and get the meta data definitions of the source and then store in the repository. If a connection is not possible, we has to be manually defined.

Target Database : Actual Warehouse database. Objects may/may not exists. Can have read, write access to the target database.

Connection : defines the communication path. (ODBC, TCP/IP, Native)

Transformations : are the programs which are used to transform the data from one form to other. 14/15 transformations.Here are some transformations:

Aggregator - to do things like "group by".

Source Qualifier - ...

Expression - to use various expressions.

External procedure - ...

Filter - to filter data.

Joiner - to make joins between separate databases, file, ODBC sources.

Lookup - to create local copy of the data.

Normalizer - to transform denormalized data into normalized data.

Rank - to select only top (or bottom) ranked data.

Sequence Generator - to generate unique IDs for target tables.

Source Qualifier - to filter sources (SQL, select distinct, join, etc.)

Stored Procedure - to run stored procedures in the database - and capture their returned values.

Update Strategy - to flag records in target for insert,delete, update (defined inside a mapping).

Meta Data : Data about data / information about the data.

MetaData Repository : basically where the meta data definitions are stored. It can be located in any RDBMS (Oracle, SQL Server, DB/2, Sybase, Informix, ODBC).We call this Repository as Power Center / Power Mart / Informatica Repository).