We have an LDM in ERwin representing a comprehensive data repository expected to collect data from many sources. We are looking for a tool to store information linking the source data, transformation logic and the target attributes in the LDM. What are advantages of using an ETL tool (e.g., Informatica) vs. a meta data repository (e.g., CA Advantage Repository).
Les Barbusinski’s Answer: The basic difference between an ETL tool like Informatica and a data repository tool like CA-Advantage is that the former only stores meta data needed to transform source data into target data structures, while the latter can store many different kinds of meta data.
ETL tools typically only store a limited amount of discrete data about a source or target column (e.g., table/column name, data type, length, FK relationships, etc.) as well as source/target mappings and detailed transformation rules about each source/target pair (e.g., defaults, translations, calculations, etc.). Additional information (e.g., aliases, examples, domains of value, business rules, security rules, usage, etc.) can at best be stored as free-form text in an ETL tool. Furthermore, ETL tools typically do not offer robust interfaces for other tools to extract their meta data.
Meta data repositories, on the other hand, can store information about any object in a data warehouse (including users, processes, etc.) and can store information for a wide variety of user-defined properties for each object. In addition, repositories usually do provide interfaces for other applications to extract meta data from their repository. Hope this helps.
Chuck Kelley’s Answer: I would lean more toward a meta data repository because it will be able (hopefully) to extend the functionality to add business meta data. In the long run it would be better to have a single place to capture all the meta data, not just the ETL stuff.
Register or login for access to this item and much more
All Information Management content is archived after seven days.
Community members receive:
- All recent and archived articles
- Conference offers and updates
- A full menu of enewsletter options
- Web seminars, white papers, ebooks
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access