Informatica Product Line

Informatica is a powerful ETL tool from Informatica Corporation, a leading provider of enterprise data integration software and ETL softwares.

The important products provided by Informatica Corporation is provided below:

Power Center Power Mart Power Exchange Power Center Connect Power Channel Metadata Exchange Power Analyzer Super Glue

Power Center & Power Mart: Power Mart is a departmental version of Informatica for building, deploying, and managing data warehouses and data marts. Power center is used for corporate enterprise data warehouse and power mart is used for departmental data warehouses like data marts. Power Center supports global repositories and networked repositories and it can be connected to several sources. Power Mart supports single repository and it can be connected to fewer sources when compared to Power Center. Power Mart can extensibily grow to an enterprise implementation and it is easy for developer productivity through a codeless environment.

Power Exchange: Informatica Power Exchange as a stand alone service or along with Power Center, helps organizations leverage data by avoiding manual coding of data extraction programs. Power Exchange supports batch, real time and changed data capture options in main frame(DB2, VSAM, IMS etc.,), mid range (AS400 DB2 etc.,), and for relational databases (oracle, sql server, db2 etc) and flat files in unix, linux and windows systems.

Power Center Connect: This is add on to Informatica Power Center. It helps to extract data and metadata from ERP systems like IBM's MQSeries, Peoplesoft, SAP, Siebel etc. and other third party applications.

Power Channel: This helps to transfer large amount of encrypted and compressed data over LAN, WAN, through Firewalls, tranfer files over FTP, etc.

Meta Data Exchange: Metadata Exchange enables organizations to take advantage of the time and effort already invested in defining data structures within their IT environment when used with Power Center. For example, an organization may be using data modeling tools, such as Erwin, Embarcadero, Oracle designer, Sybase Power Designer etc for developing data models. Functional and technical team should have spent much time and effort in creating the data model's data structures(tables, columns, data types, procedures, functions, triggers etc). By using meta deta exchange, these data structures can be imported into power center to identifiy source and target mappings which leverages time and effort. There is no need for informatica developer to create these data structures once again.

Power Analyzer: Power Analyzer provides organizations with reporting facilities. PowerAnalyzer makes accessing, analyzing, and sharing enterprise data simple and easily available to decision makers. PowerAnalyzer enables to gain insight into business processes and develop business intelligence.

With PowerAnalyzer, an organization can extract, filter, format, and analyze corporate information from data stored in a data warehouse, data mart, operational data store, or otherdata storage models. PowerAnalyzer is best with a dimensional data warehouse in a relational database. It can also run reports on data in any table in a relational database that do not conform to the dimensional model.

Super Glue: Superglue is used for loading metadata in a centralized place from several sources. Reports can be run against this superglue to analyze meta data.

Note:This is not a complete tutorial on Informatica. We will add more Tips and Guidelines on Informatica in near future. Please visit us soon to check back. To know more about Informatica, contact its official website www.informatica.com 

ETL Terms

Source System:

Source System is a database, application, file, or other storage facility from which the data in a data warehouse is derived. Some of them are Flat files,Oracle Tables, Microsoft SQL server tables, COBOL Sources, XML files.

Mapping: 

Mapping is the definition of the relationship and data flow between source and target objects. It is a pictorial representation about the flow of data from source to target.

Metadata:

Metadata describes data and other structures, such as objects, business rules, and processes.
Metadata contains all the information about the source tables, target tables, the transformations, so that it will be useful and easy to perform transformations during the ETL process. A repository contains metadata.

Staging Area:

Staging area is place where you hold temporary tables on data warehouse server. Staging tables are connected to work area or fact tables. We basically need staging area to hold the data, and perform data cleansing and merging, before loading the data into warehouse.

Data Cleansing:

It is the process of resolving inconsistencies and fixing the anomalies in source data, typically as part of the ETL process. The data cleansing technology improves data quality by validating, correctly naming and standardizing data. For example, a person's address may not be same in all source systems because of typos and postal code, city name may not match with address. These errors can be corrected by using data cleansing process and standardized data can be loaded in target systems (data warehouse).

Transformation:

Transformation is the process of manipulating data. Any manipulation beyond copying is a transformation. Examples include cleansing, aggregating, and integrating data from multiple sources. A transformation is a repository object that generates, modifies, or passes data.

Target System:

Target System is a database, application, file, or other storage facility to which the "transformed source data" is loaded in a data warehouse.

What is Web Services

• Web services convert the applications into Web format, are published  can be used by authorized applications.
• Web services are can be used across any platform and programing langauge. For example, Java can talk with Perl, Windowsapplications cancommunicate with UNIX applications.

• Web Services are developed to enable interaction between two different platforms.  It processes the real time data very fast with accuracy.

How it works:

Web services works based on 4 elements

• XML
• SOAP
• UDDI
• WSDL

XML is used to tag (covert data into XML format <city>Bangalore</city>) the data.
SOAP is used to transfer the data.
WSDL is used for describing the services available .
UDDI is used for listing what services are available

1. A service provider describes its service using WSDL. This definition is published to a directory of services. The directory could use Universal Description, Discovery, and Integration (UDDI). Other forms of directories can also be used.
 

2. A service consumer issues one or more queries to the directory to locate a service and determine how to communicate with that service.
 

3. Part of the WSDL provided by the service provider is passed to the service consumer. This tells the service consumer what the requests and responses are for the service provider.

4. The service consumer uses the WSDL to send a request to the service provider.

5. The service provider provides the expected response to the service consumer.

Informatica Software Architecture

Informatica ETL product, known as Informatica Power Center consists of 3 main components.

1. Informatica PowerCenter Client Tools: 
These are the development tools installed at developer end. These tools enable a developer to

•Define transformation process, known as mapping. (Designer)
•Define run-time properties for a mapping, known as sessions (Workflow Manager)
•Monitor execution of sessions (Workflow Monitor)
•Manage repository, useful for administrators (Repository Manager)
•Report Metadata (Metadata Reporter)

2. Informatica PowerCenter Repository:
Repository is the heart of Informatica tools. Repository is a kind of data inventory where all the data related to mappings, sources, targets etc is kept. This is the place where all the metadata for your application is stored. All the client tools and Informatica Server fetch data from Repository. Informatica client and server without repository is same as a PC without memory/harddisk, which has got the ability to process data but has no data to process. This can be treated as backend of Informatica.

3. Informatica PowerCenter Server: 
Server is the place, where all the executions take place. Server makes physical connections to sources/targets, fetches data, applies the transformations mentioned in the mapping and loads the data in the target system.

Informatica PowerCenter Components

Informatica PowerCenter is not just a tool but an end-to-end data processing and data integration environment. It facilitates organizations to collect, centrally process and redistribute data. It can be used just to integrate two different systems like SAP and MQ Series or to load data warehouses or Operational Data Stores (ODS).

Now Informatica PowerCenter also includes many add-on tools to report the data being processed, business rules applied and quality of data before and after processing.

To facilitate this PowerCenter is divided into different components:

• PowerCenter Domain: As Informatica says “The Power Center domain is the primary unit for management and administration within PowerCenter”. Doesn’t make much sense? Right… So here is a simpler version. Power Center domain is the collection of all the servers required to support Power Center functionality. Each domain has gateway (called domain server) hosts. Whenever you want to use Power Center services you send a request to domain server. Based on request type it redirects your request to one of the Power Center services.
• PowerCenter Repository: Repository is nothing but a relational database which stores all the metadata created in Power Center. Whenever you develop mapping, session, workflow, execute them or do anything meaningful (literally), entries are made in the repository.
• Integration Service: Integration Service does all the real job. It extracts data from sources, processes it as per the business logic and loads data to targets.
• Repository Service: Repository Service is the one that understands content of the repository, fetches data from the repository and sends it back to the requesting components (mostly client tools and integration service)
• PowerCenter Client Tools: The PowerCenter Client consists of multiple tools. They are used to manage users, define sources and targets, build mappings and mapplets with the transformation logic, and create workflows to run the mapping logic. The PowerCenter Client connects to the repository through the Repository Service to fetch details. It connects to the Integration Service to start workflows. So essentially client tools are used to code and give instructions to PowerCenter servers.
• PowerCenter Administration Console: This is simply a web-based administration tool you can use to administer the PowerCenter installation.

There are some more not-so-essential-to-know components discussed below:

• Web Services Hub: Web Services Hub exposes PowerCenter functionality to external clients through web services.
• SAP BW Service: The SAP BW Service extracts data from and loads data to SAP BW.
• Data Analyzer: Data Analyzer is like a reporting layer to perform analytics on data warehouse or ODS data.
• Metadata Manager: Metadata Manager is a metadata management tool that you can use to browse and analyze metadata from disparate metadata repositories. It shows how the data is acquired, what business rules are applied and where data is populated in readable reports.
• PowerCenter Repository Reports: PowerCenter Repository Reports are a set of prepackaged Data Analyzer reports and dashboards to help you analyze and manage PowerCenter metadata.

Why do we need ETL Tools?

Business Case: The company has over 100+ years of history & presence in almost all the industries. Over these years company’s management style has been changed from book keeping to SAP. This transition was not a single day transition. In transition, from book keeping to SAP, they used a wide array of technologies, ranging from mainframes to PCs, data storage ranging from flat files to relational databases, programming languages ranging from Cobol to Java.This transformation resulted into different businesses, or to be precise different sub businesses within a business, running different applications, different hardware and different architecture. Technologies are introduced as and when invented & as and when required.

This directly resulted into the scenario, like HR department of the company running on Oracle Applications, Finance running SAP, some part of process chain supported by mainframes, some data stored on Oracle, some data on mainframes, some data in VSM files & the list goes on. If one day company requires a consolidated reports of assets, there are two ways.

First completely manual, generate different reports from different systems and integrate them.
Second fetch all the data from different systems/applications, make a Data Warehouse, and generate reports as per the requirement.
Obviously second approach is going to be the best.

Now to fetch the data from different systems, making it coherent, and loading into a Data Warehouse requires some kind of extraction, cleansing, integration, and load. ETL stands for Extraction, Transformation & Load.

ETL Tools provide facility to Extract data from different non-coherent systems, cleanse it, merge it and load into target systems.

XQuery Select nodes and FLWOR expression

Look at the following path expression:

doc("books.xml")/bookstore/book[price>30]/title

The expression above will select all the title elements under the book elements that are under the bookstore element that have a price element with a value that is higher than 30.

FLWOR expression

FLWOR is an acronym for "For, Let, Where, Order by, Return".

• The for clause selects all book elements under the bookstore element  into a variable called $x.
• The where clause selects only book elements with a price element with a value greater than 30.
• The order by clause defines the sort-order. Will be sort by the title element.
• The return clause specifies what should be returned. Here it returns the title elements.

Example FOR clause

The for clause binds a variable to each item returned by the in expression. The for clause results in iteration. There can be multiple for clauses in the same FLWOR expression.

To loop a specific number of times in a for clause, you may use the to keyword:

for $x in (1 to 5)

return <test>{$x}</test>

Result:

<test>1</test>

<test>2</test>

<test>3</test>

<test>4</test>

<test>5</test>

The at keyword can be used to count the iteration:

for $x at $i in doc("books.xml")/bookstore/book/title

return <book>{$i}. {data($x)}</book>

Result:

<book>1. Everyday Italian</book>

<book>2. Harry Potter</book>

<book>3. XQuery Kick Start</book>

<book>4. Learning XML</book>

It is also allowed with more than one in expression in the for clause. Use comma to separate each in expression:

for $x in (10,20), $y in (100,200)

return <test>x={$x} and y={$y}</test>

Result:

<test>x=10 and y=100</test>

<test>x=10 and y=200</test>

<test>x=20 and y=100</test>

<test>x=20 and y=200</test>

Example LET Clause

The let clause allows variable assignments and it avoids repeating the same expression many times. The let clause does not result in iteration.

let $x := (1 to 5)

return <test>{$x}</test>

Result:

<test>1 2 3 4 5</test>

Example WHERE Clause

The where clause is used to specify one or more criteria for the result

where $x/price>30 and $x/price<100

Example ORDER BY Clause

The order by clause is used to specify the sort order of the result. Here we want to order the result by category and title

for $x in doc("books.xml")/bookstore/book
order by $x/@category, $x/title
return $x/title

Result:

<title lang="en">Harry Potter</title>
<title lang="en">Everyday Italian</title>
<title lang="en">Learning XML</title>

<title lang="en">XQuery Kick Start</title>

Example RETURN Clause

The return clause specifies what is to be returned

for $x in doc("books.xml")/bookstore/book
return $x/title

Result:

<title lang="en">Everyday Italian</title>
<title lang="en">Harry Potter</title>
<title lang="en">XQuery Kick Start</title>

<title lang="en">Learning XML</title>

Example FLWOR

for $x in doc("books.xml")/bookstore/book
where $x/price>30
order by $x/title
return $x/title
for - (optional) binds a variable to each item returned by the in expression
let - (optional)
where - (optional) specifies a criteria
order by - (optional) specifies the sort-order of the result

return - specifies what to return in the result