Informatica Metadata Manager can now read SSAS cubes and SSRS reports metadata

In Informatica version 9, PowerCenter Metadata Manager can now read SSAS and SSRS metadata. Metadata Manager (MM) is a tool to document the data lineage. It interogates the ETL tool, the RDMBS, the reporting tools and the analytics tools to get their metadata. Then it finds out (automatically) where each piece of data is coming from. For example, if your DW/BI system consists of:

• Source System in Oracle
• ETL in Informatica
• Data Warehouse (DW) in SQL Server
• Data Models in ERWin
• Reports in SSRS
• Cubes in SSAS

then using Informatica 9 Metadata Manager you can create a diagram that explains:

1. From which column in the Source System each column in the DW is derived from (and vice versa)
2. Which Transformation in which Informatica mapping does it #1 above (and vice versa)
3. Which ERWin object is associated with each DW table (and vice versa)
4. From which column in DW each attribute in the cube is derived from (and vice versa)
5. From which column in DW each column in the SSRS report is derived from (and vice versa)

At the highest level the diagram probably consists of 5 icons: Oracle – PowerCenter – DW – ERWin – SSRS – SSAS. If click the + sign on any icon, it opens up and show you the details. For example if you click on the DW it shows the tables, and if you click on a table, it shows the column. If you click on the SSRS icon, it shows the relevant reports, click on the reports and it shows the columns. Go here and scroll down to page 2 to see the screenshot of that diagram.

Data lineage diagram like that is indispensible and a must have in any data warehousing project. Sorry, any large data warehousing project. It’s not for the sake of documentation, but more importantly it’s for impact analysis. You get to know in detail which objects will be affected if you modify the data type of column1 in table1 in the warehouse. The problem with data lineage, as with any documentation, is incorporating changes. Sure it’s up to scratch when your BI system went live, but after just 3 releases my experience shows that the documentation would usually lack behind. Hence we had to trace those columns manually. With data lineage tool like Metadata Manager we can point it to our system and it creates the diagram. It shows where the data flows to, in detail. Up to the reports and cubes.

Informatica 9 PowerCenter Metadata Manager can read BusinessObjects, Cognos, Netezza, MicroStrategy, Hyperion Essbase, DB2 Cube View, ERWin, PowerCenter, SQL Server, Oracle, Sybase, Teradata, Informix, Sybase, DB2 UDB, Oracle BIEE and SAP, to get their metadata. Up to version 8.6, it could not read SSRS and SSAS. Now in version 9 it can.

No SSIS is not in there yet. Hopefully soon.