{"id":60,"date":"2005-04-01T10:02:09","date_gmt":"2005-04-01T17:02:09","guid":{"rendered":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/?p=60"},"modified":"2005-04-01T10:02:09","modified_gmt":"2005-04-01T17:02:09","slug":"owb-sources-and-targets-sql","status":"publish","type":"post","link":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/2005\/04\/01\/owb-sources-and-targets-sql\/","title":{"rendered":"OWB SOURCES AND TARGETS SQL"},"content":{"rendered":"

There was a posting on the OWB OTN forum<\/a> 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<\/a> before, and he is in the minority of OWB developers who have command of a very powerful feature.<\/p>\n

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.<\/p>\n

I’ve built a small SQL script that runs against the OWB design repository public views<\/a> (an Oracle provided view into the design metadata). I ran this against the solution for the sample company for my OWB workshop<\/a> 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).
\nsqplus design_rep<\/i>\/design_rep_password<\/i>@DB<\/p>\n

\nselect\n  distinct 'TARGET',\n  comp.map_name,\n  comp.data_entity_name,\n  comp.operator_type\nfrom\n  all_iv_xform_map_components comp,\n  all_iv_xform_map_parameters param\nwhere\n  lower(operator_type)\n     in ('table', 'view', 'dimension', 'cube')\n     and param.map_component_id = comp.map_component_id\n     and param.source_parameter_id is not null\nUNION\nselect\n  distinct 'SOURCE',\n  t1.c1,\n  t1.c2,\n  t1.c3\nfrom\n  (select\n  comp.map_name c1,\n  comp.data_entity_name c2,\n  comp.operator_type c3,\n  max(param.source_parameter_id) c4\nfrom\n  all_iv_xform_map_components comp,\n  all_iv_xform_map_parameters param\nwhere\n  lower(operator_type) in\n                           ('table', 'view', 'dimension', 'cube')\n     and param.map_component_id = comp.map_component_id\n group by\ncomp.map_name, comp.data_entity_name, comp.operator_type) t1\nwhere t1.c4 is null\norder by 2,1\n<\/pre>\n
<\/p>\n
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?<\/p>\n
Let me know<\/a> how people get on with this script; especially if there’s any edge cases or revisions that augment it.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[10],"tags":[],"_links":{"self":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/60"}],"collection":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/comments?post=60"}],"version-history":[{"count":0,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/60\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/media?parent=60"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/categories?post=60"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/tags?post=60"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}