Item: Informatica PowerCenter
Rating: 7
Author: Jody Gitchel

Overall Satisfaction with PowerCenter

Use Cases and Deployment Scope

Informatica PowerCenter is the enterprise ETL tool used for all data integration across the organization. PowerCenter is utilized for all on premises data integration including the Electronic Medical Record (EMR) system as well as disparate systems including scheduling and time management systems.

Pros and Cons

Pros

Enforces enterprise wide ETL development standards.
Provides code re-usability with shared connections and objects.
Particularly adept at integrating a wide range of disparate data sources (handles flat files particularly well).
Well suited for moving large amounts of data.

Cons

There are too many ways to perform the same or similar functions which in turn makes it challenging to trace what a workflow is doing and at which point (ex. sessions can be designed as static or re-usable and the override can occur at the session or workflow, or both which can be counter productive and confusing when troubleshooting).
The power in structured design is a double edged sword. Simple tasks for a POC can become cumbersome. Ex. if you want to move some data to test a process, you first have to create your sources by importing them which means an ODBC connection or similar will need to be configured, you in turn have to develop your targets and all of the essential building blocks before being able to begin actual development. While I am on sources and targets, I think of a table definition as just that and find it counter intuitive to have to design a table as both a source and target and manage them as different objects. It would be more intuitive to have a table definition and its source/target properties defined by where you drag and drop it in the mapping.
There are no checkpoints or data viewer type functions without designing an entire mapping and workflow. If you would like to simply run a job up to a point and check the throughput, an entire mapping needs to be completed and you would workaround this by creating a flat file target.

Return on Investment

PowerCenter has been instrumental in being the center of all data movement within the organization.
It has also provided a foundation for which re-usability and scalability are the focus.
Finding talent with experience and expertise in PowerCenter is far more likely due to its presence and market share.

Alternatives Considered

SQL Server Integration Services, Pentaho and IBM InfoSphere DataStage

SSIS is a good entry into ETL, for smaller organizations or Microsoft-centric companies. It's strengths lie in its ease-of-use, quick turnaround, and simplicity. Its weaknesses lie in scalability and re-usability (you can achieve re-usability, however segmentation is at the project level with no global scale). Pentaho is a great, low cost solution with deceptive power, however scheduling along with flexibility in other functions such as OS level script execution is a bit weaker. PowerCenter and DataStage are more comparable at an enterprise ETL level, having different management philosophies and approaches. For both, there are features in each that I wish the other either adopted or replaced, however due to its place in the market, ease of finding additional developers, and scalability, PowerCenter was chosen.

Likelihood to Recommend

PowerCenter is well equipped to handle large amounts of data movement in an organization with many disparate sources and a structured development team. It excels in enforcing enterprise development standards through things like metadata manager and the monitoring capabilities (as well as being able to design monitoring rules for everything from naming standards to design practices). It is especially well suited at handling flat-file data in addition to its many connectors and native support for just about any ANSI standard database. For large development teams or the desire to remain flexible at an enterprise scale, Powercenter is a top-tier solution.

For small projects or even smaller development teams with mostly a single data source, expect frustration with being able to quickly test a solution as the design flow is very structured. It is also designed in a way that segregation of duties at a very high level can also cause small development teams to be counter-productive. Each step in the design process is a separate application, and although stitched together, is not without its problems. In order to design a simple mapping for example, you would first need a connection established to the source (example, ODBC) and keep in mind that it will automatically name the container according to how you named your connection. You would then open the designer tool, import a connection as a source, optionally check it in, create a target, optionally check it in as well, and design a transformation mapping. In order to test or run it, you will need to open a separate application (Workflow Manager) and create a workflow from your mapping, then create a session for that workflow and a workflow for those one or more sessions at which point you can test it. After running it, in order to observe, you then need to open a separate application (Monitor) to see what it is doing and how well. For a developer coming from something like SSIS, this can be daunting and cumbersome for building a simple POC and trying to test it (although from the inverse, building an enterprise scalable ETL solution from SSIS is its own challenge).

Informatica PowerCenter Feature Ratings

Comments

Please log in to join the conversation

A powerful ETL solution which focuses on enterprise scalability, flexibility, and code re-usability