There was a posting on the OWB OTN forum about how to build a report documenting mappings, their sources, and their targets. Patrick Goessens provided an OMBPlus script that fits the bill and should work (perhaps slightly customized) brilliantly. I love OMBPlus and find it to be a very useful addition to the Oracle Warehouse Builder product. I’ve noticed Patricks postings before, and he is in the minority of OWB developers who have command of a very powerful feature.
There might be circumstances when OMBPlus might not be a prefereable option. Not everyone will have OWB installed, or want to pay the additional license costs to just “report” on the metadata. The consumer of this information might not be a “person,” but rather another system or repository. In that case, coordinating the execution of an OMBPlus script, parsing and importing into an alternative application might be troublesome.
I’ve built a small SQL script that runs against the OWB design repository public views (an Oracle provided view into the design metadata). I ran this against the solution for the sample company for my OWB workshop and it reports correctly for it. Use it as a reference, but ensure it works for your actual metadata repository as I don’t claim that this is a complete solution (or even nicely written SQL).
select distinct 'TARGET', comp.map_name, comp.data_entity_name, comp.operator_type from all_iv_xform_map_components comp, all_iv_xform_map_parameters param where lower(operator_type) in ('table', 'view', 'dimension', 'cube') and param.map_component_id = comp.map_component_id and param.source_parameter_id is not null UNION select distinct 'SOURCE', t1.c1, t1.c2, t1.c3 from (select comp.map_name c1, comp.data_entity_name c2, comp.operator_type c3, max(param.source_parameter_id) c4 from all_iv_xform_map_components comp, all_iv_xform_map_parameters param where lower(operator_type) in ('table', 'view', 'dimension', 'cube') and param.map_component_id = comp.map_component_id group by comp.map_name, comp.data_entity_name, comp.operator_type) t1 where t1.c4 is null order by 2,1
TARGETS are determined if any of their columns have a source parameter (ie, there’s been a line drawn into them on a mapping). SOURCES are determined if they do not have any source parameters (they don’t have any mapping lines coming “in”). I couldn’t see any special flag or marker to find the “one” target of a mapping, so it had to be inferred. I wonder if anyone from the OWB product team would like to comment on the validity of this logic?
Let me know how people get on with this script; especially if there’s any edge cases or revisions that augment it.