ssg

PowerCenter is a collection of software tools to support extraction, transformation and loading (ETL) of data between multiple applications.  Most companies prefer to use Informatica Power Center to populate their data warehouses with operational data.  However, Power Center also simplifies the process of integrating data between different applications in either a batch or near-real-time manner.

A common problem in most companies is that information exists in silos and it can be difficult to custom design and implement ways to keep data synchronized between applications.  The options are to write custom integrations or use tools to automate and simplify the process.  Since such integrations invariably change as applications are updated, there are regular needs to revisit the integration and update it.  The point-and-click capabilities of PowerCenter Designer along with it’s WorkFlow Manager dramatically simplify and speed up the creation and maintenance of these data integrations.

PowerCenter has the ability to work with data in just about any situation.  It can access data in Excel spreadsheets, any relational database (Oracle, SQL Server, MySQL, DB2, Sybase), web applications (SalesForce) and even flat files (CSV).

PowerCenter consist of several related applications as shown here.

PowerCenter Components

PowerCenter relies on Mappings and Workflow.  Mappings describe the inputs, transformations and outputs of the data.  Transformations can eliminate data, enrich the data, organize organize the data in different ways.  If you have ever worked with SQL or Microsoft Excel you are probably familiar with the many functions that PowerCenter uses to transform data.  In addition the software supports sophisticated Joins, Unions, Sorts, Aggregations, Routing and Look-ups which are simple to define and implement.

A Workflow process is then created and used to organize one or more mappings.  Within a workflow, the developer can check for error conditions, issue email updates, synchronize processes so they execute in the right order and schedule how and when a mapping process is executed.

All of the PowerCenter information is stored in a database repository (such as Oracle) where multiple developers can access the information and collaborate on new integrations.  The repository can also be used to secure the integration information and support the reuse of mappings, transformations and even workflows.  The focus on “reuse” allows very complex processes to be built, over time, by grouping simpler mappings that have been previously tested and approved.

The client tools that come with Power Center are top-notch.  They provide a very intuitive interface for designing and debugging integration processes quickly.  Several sample screens are shown below.  They allow you to drag-n-drop the data components and data flow on the screen and then quickly test the results using test data.  The PowerCenter debugger supports step-by-step review of a complex mapping by showing the states of variables and data elements.

Here is a PowerCenter Designer screen example.

PowerCenter Designer

Here is a portion of a Mapping that has been defined for data integration.  It was created using drag-n-drop components.

Sample Mapping

When you are finished with the mapping, you can then create complex workflows with many different mappings and steps.

Simple Workflow

Here at SSG, we use PowerCenter to help our clients quickly integrate applications so that they can share data or create the ETL to load their data warehouses quickly and efficiently.  By leveraging PowerCenter’s capabilities we are able to provide faster and more robust solutions compared to a traditional custom programming solution.

For more information about Informatica, you can visit their website here.