ETL Metadata Warehouse Design

Question

Design Question:

I am looking to design a warehouse that essentially captures the ETL metadata associated with a large scale conversion process. The purpose of this warehouse will be to provide an ongoing audit and reporting capability for ETL activity on the whole. By ETL activity I mean extract to cleansed processes including but not limited to crud activities as well as the application of complex business logic to various datapoints. On the whole I believe the most important points of interest are not so much the data within the tables but end to end record counts, and some data level aggregates like sum amounts for customer etc..

I am struggling with conceptualizing the dimensions by which I would base this on. As a starting point I believe breaking down all processes that manipulate the data, mock time elements,and the discrete data points are a good start. Then looking at deltas in record counts as per these.

Has anyone implemented something similar? I really haven't found much information out there regarding warehousing etl metadata and audit data. Any advice would be greatly appreciated

---

“Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.” -Terry Pratchett

Answer 1

This is a general discussion question on a data warehouse design. Before you start your dimension design, I recommend that you first answer the following questions:
1. What is the subject of your data warehouse?
2. What are the measures you want to get?
3. What are the dimensions you want to use to observe your measures?
4. What are your current difficulties?

Then you can read some well-known books for getting a systematic view of this knowledge with your questions:
Data Warehouse Design Solutions (Paperback)
http://www.amazon.com/Warehouse-Design-Solutions-Christopher-Adamson/dp/047125195X
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition) (Paperback)
http://www.amazon.com/Data-Warehouse-Toolkit-Complete-Dimensional/dp/0471200247/ref=pd_bxgy_b_img_c
Mastering Data Warehouse Design: Relational and Dimensional Techniques (Paperback)
http://www.amazon.com/Mastering-Data-Warehouse-Design-Dimensional/dp/0471324213/ref=pd_sim_b_5
The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleanin (Paperback)
http://www.amazon.com/Data-Warehouse-ETL-Toolkit-Techniques/dp/0764567578/ref=pd_bxgy_b_img_c

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help