Data Warehouse: Parent - children links from RTC for work items, parent - children links for iterations

It seems the data warehose does not capture:

• RTC parent and children work item attributes.
• RTC parent - children relationships for iterations

We need those relationships to report the estimates and effort for hierarchies of work items and hierarchies of iterations within a single project and across multiple projects in an automated way.

If I missed something in the data dictionary please point me in the right direction. I know the relationships are in the API. We're hoping to use the existing reporting tool, in this case RRDI which works off of the data warehouse. Using the API would require that we custom build a lot of reporting and automation functionality.

We'd like to be able to answer questions such as how much effort has been accomplished against a particular high-level work item, a work item that might be a parent to many stories and grand-parent to even more tasks. If the data warehouse doesn't maintain information about parent and children relationships for work items, is there another way to derive how much work is represented by a grand-parent work item? We'd like to be able to answer these questions in an automated way across many high-level work items, and across RTC projects.

We also need to be able to know if an iteration is a low level iteration, or a parent for reasons similar to why we need the parent-child relationships of the work items.