Informatica PowerCenter Metadata Manager and its use in a Data Integration project

By Kevin Madrick, Senior Director, Data Integration Practice

So you have PowerCenter Advanced Edition, and it comes with this Metadata Manager tool. What is it? What do you use it for? How is another tool going to make your job easier?

Metadata is normally defined as “data about data”, which is a pretty generic concept. In the context most of us use Metadata, it’s the sources, targets, mappings, etc in PowerCenter, as well as the databases PowerCenter extracts from and loads to, and the BI tools that report against the databases. That sounds like a lot. There’s a lot of documentation on how to load the tool, but what do you do with it then?

Metadata Manager is normally used for lineage, impact analysis and Metadata catalog search.

Lineage

Lineage is the path that data takes from the source to the target. In general terms, it’s composed of the data source, the integration rules, the target, and the reports that use a particular data field. Generally it’s used to figure out how you got the results in that data field, and where the data came from.

Assume that a Sarbanes-Oxley auditor looks at your financial statement report and has issues with your depreciation figure. Normally you’d have to reverse engineer the report back to the BI tools semantic layer, through the datamart data model , through the source/target mappings spec, and ultimately to the source database. It can take days, and in some cases weeks if the documentation hasn’t been kept current. And everyone always keeps their documentation current, right?

Using Metadata Manager’s lineage feature, you can get the data for that report element in a few seconds. All you have to do is navigate through the interface to the element, and click on “lineage”. Sha-zam! Days of work saved...

Impact Analysis

Impact Analysis is the reverse of lineage. It allows you to predict which downstream objects will be affected by a change.

Assume a DBA wants to change the data type for a specific field in your source database. If you run the impact analysis feature against that database and find out that it’s linked to your financial reports, you might want to flag that for the change management approval process. This is especially true if you have different change management processes for different types of data. For example, people might not be happy if you make a change that impacts the financial reports right before the Sarbanes-Oxley auditors arrive.

Normally you’d have to examine all of the project documentation, looking for any objects downstream of the object you’d like to change, in order to get a list of what that change will affect. It can take days or weeks to do this. With the impact analysis feature, you can get it in...Sha-Zam...seconds.

One major thing to keep in mind is that sometimes the impact analysis feature doesn’t reveal quite as much data as you’d like. If this happens, just use lineage for the data element, which you’re interested in.

Metadata Search

The metadata search feature allows keyword search of the catalog. This is helpful in situations where you know what you want, but you don’t know where it is.

An example is if the business asks for a new report on a few key metrics of the business, and they need it as soon as possible. Using the normal search method, you’d have to go through a lot of documentation and data models, which could take days or weeks. With the catalog search feature, you can get a Google-like search in seconds. You can browse through the results to quickly find the elements you need for the report.

PowerCenter 8.6 Advanced Edition contains a number of labour-saving tools, including metadata manager. If you know how to use those tools, you can save significant amounts of time in planning and executing data management projects.