Note - before anyone asks, we have indeed asked the following question directly to each of the vendors involved to get each perspective, except Microsoft. This is how we are choosing to tackle this issue from the Microsoft perspective, at least to start with, since this community and its technical advisors have been so helpful in the past.

Considering the following architecture, Informatica PowerCenter as the ETL solution, SQL Server 2000 as the target platform, Analysis Services as the OLAP solution and any third party OLAP client that will successfully integrate with Informatica's metadata repository we wish to do the following

1) Construct target data model using ERwin, including UDPs (user defined properties) such as data definitions, business uses, etc

2) Forward engineer DDL portion of target model to SQL Server

3) Import target structure into Informatica repository via PowerCenter warehouse designer

4) Import OLTP structure(s) for source(s) necessary to populate target into Informatica repository via PowerCenter source designer

5) UDPs from the ERwin target model are imported into the Informatica repository

6) At this point, limited source metadata and robust target metadata exists in the Informatica repository. Only target metadata exists in ERwin and no metadata exists as extended properties in SQL Server

7) ETL mappings, sessions and workflows are developed

8) At this point, ETL source to target “anticipated” lineage metadata is now introduced into Informatica repository, but obviously not into ERwin or SQL Server

9) ETL executes and target is loaded

10) At this point, ETL source to target “actual” (historical tuple by tuple) lineage metadata as well as load metrics metadata are now introduced into Informatica repository, but again - obviously not into ERwin or SQL Server

The question - how do we get the business metadata from either the Informatica repository to Analysis Services, directly or indirectly, allowing users to access the originally created UDPs, OLTP source to target lineage, etc. during OLAP querying