Informatica Tool Product Overview

Introduction

Power Center provides an environment that allows you to load data into a centralized location, such as a data warehouse or operational data store (ODS). You can extract data from multiple sources, transform the data according to business logic you build in the client application, and load the transformed data into file and relational targets.
PowerCenter also provides the ability to view and analyze business information and browse and analyze metadata from disparate metadata repositories.
PowerCenter includes the following components:

♦PowerCenter domain. The Power Center domain is the primary unit for management and administration within PowerCenter. The Service Manager runs on a PowerCenter domain. The Service Manager supports the domain and the application services. Application services represent server-based functionality and include the Repository Service, Integration Service, Web Services Hub, and SAP BW Service. For more information, see “PowerCenter Domain”

♦PowerCenter repository. The PowerCenter repository resides in a relational database. The repository database tables contain the instructions required to extract, transform, and load data. For more information, see “PowerCenter Repository”

♦Administration Console. The Administration Console is a web application that you use to administer the PowerCenter domain and PowerCenter security. For more information, see “Administration Console”

♦PowerCenter Client. The PowerCenter Client is an application used to 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 modify repository metadata. It connects to the Integration Service to start workflows. For more information, see “PowerCenter Client”

♦Repository Service. The Repository Service accepts requests from the PowerCenter Client to create and modify repository metadata and accepts requests from the Integration Service for metadata when a workflow runs. For more information, see “Repository Service”

♦Integration Service. The Integration Service extracts data from sources and loads data to targets. For more information, see “Integration Service”

♦Web Services Hub. Web Services Hub is a gateway that exposes PowerCenter functionality to external clients through web services. For more information, see “Web Services Hub”

♦SAP BW Service. The SAP BW Service extracts data from and loads data to SAP BW.

♦Data Analyzer. Data Analyzer is a web application that provides a framework to perform business analytics on corporate data. With Data Analyzer, you can extract, filter, format, and analyze corporate information from data stored in a data warehouse, operational data store, or other data storage models. For more information, see “Data Analyzer”

♦Metadata Manager. Metadata Manager is a metadata management tool that you can use to browse and analyze metadata from disparate metadata repositories. Metadata Manager helps you understand and manage how information and processes are derived, the fundamental relationships between them, and how they are used. For more information, see “Metadata Manager”

♦PowerCenter Repository Reports. PowerCenter Repository Reports are a set of prepackaged Data Analyzer reports and dashboards to help you analyze and manage PowerCenter metadata. For more information, see “PowerCenter Repository Reports”

Figure shows the PowerCenter components:

Sources
PowerCenter accesses the following sources:
♦Relational. Oracle, Sybase ASE, Informix, IBM DB2, Microsoft SQL Server, and Teradata.
♦File. Fixed and delimited flat file, COBOL file, XML file, and web log.
♦Application. You can purchase additional PowerExchange products to access business sources such as Hyperion Essbase, WebSphere MQ, IBM DB2 OLAP Server, JMS, Microsoft Message Queue, PeopleSoft, SAP NetWeaver, SAS, Siebel, TIBCO, and webMethods.
♦Mainframe. You can purchase PowerExchange to access source data from mainframe databases such as Adabas, Datacom, IBM DB2 OS/390, IBM DB2 OS/400, IDMS, IDMS-X, IMS, and VSAM.
♦Other. Microsoft Excel, Microsoft Access, and external web services.

Targets
PowerCenter can load data into the following targets:
♦Relational. Oracle, Sybase ASE, Sybase IQ, Informix, IBM DB2, Microsoft SQL Server, and Teradata.
♦File. Fixed and delimited flat file and XML.
♦Application. You can purchase additional PowerExchange products to load data into business sources such as Hyperion Essbase, WebSphere MQ, IBM DB2 OLAP Server, JMS, Microsoft Message Queue, mySAP, PeopleSoft EPM, SAP BW, SAS, Siebel, TIBCO, and webMethods.
♦Mainframe. You can purchase PowerExchange to load data into mainframe databases such as IBM DB2 OS/390, IBM DB2 OS/400, IMS, and VSAM.
♦Other. Microsoft Access and external web services.
You can load data into targets using ODBC or native drivers, FTP, or external loaders.


PowerCenter Domain
PowerCenter has a service-oriented architecture that provides the ability to scale services and share resources across multiple machines. PowerCenter provides the PowerCenter domain to support the administration of the PowerCenter services. A domain is the primary unit for management and administration of services in PowerCenter.

A domain contains the following components:

♦One or more nodes. A node is the logical representation of a machine in a domain. A domain may contain more than one node. The node that hosts the domain is the master gateway for the domain. You can add other machines as nodes in the domain and configure the nodes to run application services such as the Integration Service or Repository Service. All service requests from other nodes in the domain go through the master gateway.
A nodes runs service processes, which is the runtime representation of an application service running on a node.

♦Service Manager. The Service Manager is built in to the domain to support the domain and the application services. The Service Manager runs on each node in the domain. The Service Manager starts and runs the application services on a machine.

♦Application services. A group of services that represent PowerCenter server-based functionality. The application services that run on each node in the domain depend on the way you configure the node and the application service.
You can use the PowerCenter Administration Console to manage the domain
If you have the high availability option, you can scale services and eliminate single points of failure for services. The Service Manager and application services can continue running despite temporary network or hardware failures. High availability includes resilience, failover, and recovery for services and tasks in a domain.

Figure shows a sample domain with three nodes:

This domain has a master gateway on Node 1. Node 2 runs an Integration Service, and Node 3 runs the Repository Service.

Service Manager
The Service Manager is built in to the domain and supports the domain and the application services. The Service Manager performs the following functions:
♦Alerts. Provides notifications about domain and service events.
♦Authentication. Authenticates user requests from the Administration Console, PowerCenter Client, Metadata Manager, and Data Analyzer.
♦Authorization. Authorizes user requests for domain objects. Requests can come from the Administration Console or from infacmd.
♦Domain configuration. Manages domain configuration metadata.
♦Node configuration. Manages node configuration metadata.
♦Licensing. Registers license information and verifies license information when you run application services.
♦Logging. Provides accumulated log events from each service in the domain. You can view logs in the Administration Console and Workflow Monitor.
♦User management. Manages users, groups, roles, and privileges.
For more information about the Service Manager, see the Administrator Guide.
Application Services
When you install PowerCenter Services, the installation program installs the following application services:
♦Repository Service. Manages connections to the PowerCenter repository. For more information, see “Repository Service”
♦Integration Service. Runs sessions and workflows. For more information, see “Integration Service”
♦Web Services Hub. Exposes PowerCenter functionality to external clients through web services. For more information, see “Web Services Hub”
♦SAP BW Service. Listens for RFC requests from SAP BW and initiates workflows to extract from or load to SAP BW.

PowerCenter Repository
The PowerCenter repository resides in a relational database. The repository stores information required to extract, transform, and load data. It also stores administrative information such as permissions and privileges for users and groups. PowerCenter applications access the repository through the Repository Service.
You administer the repository through the PowerCenter Administration Console and command line programs.
You can develop global and local repositories to share metadata:
♦Global repository. The global repository is the hub of the repository domain. Use the global repository to store common objects that multiple developers can use through shortcuts. These objects may include operational or application source definitions, reusable transformations, mapplets, and mappings.
♦Local repositories. A local repository is any repository within the domain that is not the global repository. Use local repositories for development. From a local repository, you can create shortcuts to objects in shared folders in the global repository. These objects include source definitions, common dimensions and lookups, and enterprise standard transformations. You can also create copies of objects in non-shared folders.
PowerCenter supports versioned repositories. A versioned repository can store multiple versions of an object. PowerCenter version control allows you to efficiently develop, test, and deploy metadata into production.
You can view repository metadata in the Repository Manager. Informatica Metadata Exchange (MX) provides a set of relational views that allow easy SQL access to the PowerCenter metadata repository. For more information, see “Using Metadata Exchange (MX) Views” in the Repository Guide. You can also view metadata using PowerCenter Repository Reports by creating a Reporting Service in the Administration Console.

Administration Console
The Administration Console is a web application that you use to administer the PowerCenter domain and PowerCenter security.
Domain Page
You administer the PowerCenter domain on the Domain page of the Administration Console. Domain objects include services, nodes, and licenses.
You can complete the following tasks in the domain:
♦Manage application services. Manage all application services in the domain, such as the Integration Service and Repository Service.
♦Configure nodes. Configure node properties, such as the backup directory and resources. You can also shut down and restart nodes.
♦Manage domain objects. Create and manage objects such as services, nodes, licenses, and folders. Folders allow you to organize domain objects and manage security by setting permissions for domain objects.
♦View and edit domain object properties. You can view and edit properties for all objects in the domain, including the domain object.
♦View log events. Use the Log Viewer to view domain, Integration Service, SAP BW Service, Web Services Hub, and Repository Service log events.
Other domain management tasks include applying licenses and managing grids and resources. For more information about managing a domain through the Administration Console

Figure shows domain page of the PowerCenter Administration Console




Security Page
You administer PowerCenter security on the Security page of the Administration Console. You manage users and groups that can log in to the following PowerCenter applications:
♦Administration Console
♦PowerCenter Client
♦Metadata Manager
♦Data Analyzer
You can complete the following security tasks:
♦Manage native users and groups. Create, edit, and delete native users and groups.
♦Configure LDAP authentication and import LDAP users and groups. Configure a connection to an LDAP directory service. Import users and groups from the LDAP directory service.
♦Manage roles. Create, edit, and delete roles. Roles are collections of privileges. Privileges determine the actions that users can perform in PowerCenter applications.
♦Assign roles and privileges to users and groups. Assign roles and privileges to users and groups for the domain, Repository Service, Metadata Manager Service, or Reporting Service.
♦Manage operating system profiles. Create, edit, and delete operating system profiles. An operating system profile is a level of security that the Integration Services uses to run workflows. The operating system profile contains the operating system user name, service process variables, and environment variables. You can configure the Integration Service to use operating system profiles to run workflows.

Figure shows Security Page of the PowerCenter Administration Console




PowerCenter Client
The PowerCenter Client application consists of the following tools that you use to manage the repository, design mappings, mapplets, and create sessions to load the data:
♦Designer. Use the Designer to create mappings that contain transformation instructions for the Integration Service. For more information about the Designer, see “PowerCenter Designer”
♦Data Stencil. Use the Data Stencil to create mapping templates that can be used to generate multiple mappings.
♦Repository Manager. Use the Repository Manager to assign permissions to users and groups and manage folders. For more information about the Repository Manager, see “Repository Manager”
♦Workflow Manager. Use the Workflow Manager to create, schedule, and run workflows. A workflow is a set of instructions that describes how and when to run tasks related to extracting, transforming, and loading data. For more information about the Workflow Manager, see “Workflow Manager”
♦Workflow Monitor. Use the Workflow Monitor to monitor scheduled and running workflows for each Integration Service. For more information about the Workflow Monitor, see “Workflow Monitor”


PowerCenter Designer
The Designer has the following tools that you use to analyze sources, design target schemas, and build source-to-target mappings:
♦Source Analyzer. Import or create source definitions.
♦Target Designer. Import or create target definitions.
♦Transformation Developer. Develop transformations to use in mappings. You can also develop user-defined functions to use in expressions.
♦Mapplet Designer. Create sets of transformations to use in mappings.
♦Mapping Designer. Create mappings that the Integration Service uses to extract, transform, and load data.
You can display the following windows in the Designer:
♦Navigator. Connect to repositories and open folders within the Navigator. You can also copy objects and create shortcuts within the Navigator.
♦Workspace. Open different tools in this window to create and edit repository objects, such as sources, targets, mapplets, transformations, and mappings.
♦Output. View details about tasks you perform, such as saving your work or validating a mapping.

 Figure shows the default Designer windows:




Data Stencil
Use Data Stencil to create mapping templates using Microsoft Office Visio. When you work with a mapping template, you use the following main areas:
♦Data Integration stencil. Displays shapes that represent PowerCenter mapping objects. Drag a shape from the Data Integration stencil to the drawing window to add a mapping object to a mapping template.
♦Data Integration toolbar. Displays buttons for tasks you can perform on a mapping template. Contains the online help button.
♦Drawing window. Work area for the mapping template. Drag shapes from the Data Integration stencil to the drawing window and set up links between the shapes. Set the properties for the mapping objects and the rules for data movement and transformation.

Figure shows the Data Stencil Window:



Repository Manager
Use the Repository Manager to administer repositories. You can navigate through multiple folders and repositories, and complete the following tasks:
♦Manage user and group permissions. Assign and revoke folder and global object permissions.
♦Perform folder functions. Create, edit, copy, and delete folders. Work you perform in the Designer and Workflow Manager is stored in folders. If you want to share metadata, you can configure a folder to be shared.
♦View metadata. Analyze sources, targets, mappings, and shortcut dependencies, search by keyword, and view the properties of repository objects.
  
The Repository Manager can display the following windows:
♦Navigator. Displays all objects that you create in the Repository Manager, the Designer, and the Workflow Manager. It is organized first by repository and by folder.
♦Main. Provides properties of the object selected in the Navigator. The columns in this window change depending on the object selected in the Navigator.
♦Output. Provides the output of tasks executed within the Repository Manager.

Figure shows the windows in the Repository Manager:
 


Repository Objects
You create repository objects using the Designer and Workflow Manager client tools. You can view the following objects in the Navigator window of the Repository Manager:
♦Source definitions. Definitions of database objects such as tables, views, synonyms, or files that provide source data.
♦Target definitions. Definitions of database objects or files that contain the target data.
♦Mappings. A set of source and target definitions along with transformations containing business logic that you build into the transformation. These are the instructions that the Integration Service uses to transform and move data.
♦Reusable transformations. Transformations that you use in multiple mappings.
♦Mapplets. A set of transformations that you use in multiple mappings.

♦Sessions and workflows. Sessions and workflows store information about how and when the Integration Service moves data. A workflow is a set of instructions that describes how and when to run tasks related to extracting, transforming, and loading data. A session is a type of task that you can put in a workflow. Each session corresponds to a single mapping.

Workflow Manager
In the Workflow Manager, you define a set of instructions to execute tasks such as sessions, emails, and shell commands. This set of instructions is called a workflow.
The Workflow Manager has the following tools to help you develop a workflow:
♦Task Developer. Create tasks you want to accomplish in the workflow.
♦Worklet Designer. Create a worklet in the Worklet Designer. A worklet is an object that groups a set of tasks. A worklet is similar to a workflow, but without scheduling information. You can nest worklets inside a workflow.
♦Workflow Designer. Create a workflow by connecting tasks with links in the Workflow Designer. You can also create tasks in the Workflow Designer as you develop the workflow.
When you create a workflow in the Workflow Designer, you add tasks to the workflow. The Workflow Manager includes tasks, such as the Session task, the Command task, and the Email task so you can design a workflow. The Session task is based on a mapping you build in the Designer.
You then connect tasks with links to specify the order of execution for the tasks you created. Use conditional links and workflow variables to create branches in the workflow.
When the workflow start time arrives, the Integration Service retrieves the metadata from the repository to execute the tasks in the workflow. You can monitor the workflow status in the Workflow Monitor.

Figure shows the Workflow Manager windows:

Workflow Monitor
You can monitor workflows and tasks in the Workflow Monitor. You can view details about a workflow or task in Gantt Chart view or Task view. You can run, stop, abort, and resume workflows from the Workflow Monitor. You can view sessions and workflow log events in the Workflow Monitor Log Viewer.
The Workflow Monitor displays workflows that have run at least once. The Workflow Monitor continuously receives information from the Integration Service and Repository Service. It also fetches information from the repository to display historic information.
The Workflow Monitor consists of the following windows:
♦Navigator window. Displays monitored repositories, servers, and repositories objects.
♦Output window. Displays messages from the Integration Service and Repository Service.
♦Time window. Displays progress of workflow runs.
♦Gantt Chart view. Displays details about workflow runs in chronological format.
♦Task view. Displays details about workflow runs in a report format

Figure shows the Workflow Monitor:

Repository Service
The Repository Service manages connections to the PowerCenter repository from repository clients. A repository client is any PowerCenter component that connects to the repository. The Repository Service is a separate, multi-threaded process that retrieves, inserts, and updates metadata in the repository database tables. The Repository Service ensures the consistency of metadata in the repository.
The Repository Service accepts connection requests from the following PowerCenter components:

♦PowerCenter Client. Use the Designer and Workflow Manager to create and store mapping metadata and connection object information in the repository. Use the Workflow Monitor to retrieve workflow run status information and session logs written by the Integration Service. Use the Repository Manager to organize and secure metadata by creating folders and assigning permissions to users and groups.
♦Command line programs. Use command line programs to perform repository metadata administration tasks and service-related functions.
♦Integration Service. When you start the Integration Service, it connects to the repository to schedule workflows. When you run a workflow, the Integration Service retrieves workflow task and mapping metadata from the repository. The Integration Service writes workflow status to the repository.
♦Web Services Hub. When you start the Web Services Hub, it connects to the repository to access web-enabled workflows. The Web Services Hub retrieves workflow task and mapping metadata from the repository and writes workflow status to the repository.
♦SAP BW Service. Listens for RFC requests from SAP NetWeaver BW and initiates workflows to extract from or load to SAP BW.
You install the Repository Service when you install PowerCenter Services. After you install the PowerCenter Services, you can use the Administration Console to manage the Repository Service.

Integration Service
The Integration Service reads workflow information from the repository. The Integration Service connects to the repository through the Repository Service to fetch metadata from the repository.
A workflow is a set of instructions that describes how and when to run tasks related to extracting, transforming, and loading data. The Integration Service runs workflow tasks. A session is a type of workflow task. A session is a set of instructions that describes how to move data from sources to targets using a mapping.
A session extracts data from the mapping sources and stores the data in memory while it applies the transformation rules that you configure in the mapping. The Integration Service loads the transformed data into the mapping targets.
Other workflow tasks include commands, decisions, timers, pre-session SQL commands, post-session SQL commands, and email notification.
The Integration Service can combine data from different platforms and source types. For example, you can join data from a flat file and an Oracle source. The Integration Service can also load data to different platforms and target types.
You install the Integration Service when you install PowerCenter Services. After you install the PowerCenter Services, you can use the Administration Console to manage the Integration Service.