Metadata reporting on the OWB 11gR1 repository- options?

There are quite a few clients, coworkers and managers I've met with that feel the documentation capabilities of OWB are, shall we say....lacking a bit. Sure, there is the OWB online repository browser which is quite handy and gives you a lot of information. There is the metadata lineage and impact analysis diagramming which again is quite handy. There are various tricks one can do with OMB*PLUS or experts to whip up some simple documentation. Of course when you want to get specific, then it becomes a challenge.

Say you want a report, with minimal work, that is in a CSV or excel spreadsheet showing all sorts of values that you figure you can grab from the repository somehow. Again, you can accomplish some great things with OMB and experts, and I have done this, but many times it is too time intensive and overkill. Sometimes you might consider UDOs (User Defined Objects) which can be created with OMB scripting, which give you additional fields and properties to use on your objects. This can make things easy when you are in the beginning of a project and you can tackle documenting things as you go along.

What if you're 50% or even 90% done with your project, and someone demands something - fast. I say, look at the repository quickly, write some SQL, and get it out there fast.

Well in OWB 11gR1, the repository is broken down into things called workspaces. These workspaces have separate owners, and they have users themselves. So if you want to access the OWBSYS schema's many views that hold important metadata, you'll need to set some context for the user accessing the data- namely the various workspace owners. Below are a few steps that have been published in forums and the owb blog as well that outline what you should do if you cannot see any data in OWBSYS's views.....

First read this.

Then....


THEN, here are 2 sample scripts that are in the oracle forums as well. These should get you started, and get your mind thinking i terms of what other views you'd like to look at, and what other columns are you interested in...as well as the various ways to join the views!


--This script focuses more on mapping contents...
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

--This script focuses more on source to target tables and column mappings

SELECT
S_TBL_LOC ,
S_TBL_name,
S_FLD_name,
T_TBL_LOC ,
T_TBL_name,
T_FLD_name
FROM
(
SELECT
parms.map_component_id S_COMP_id ,
parms.parameter_id S_PARM_id ,
colms.entity_name S_TBL_name ,
colms.column_name S_FLD_name ,
tbls.schema_name S_TBL_LOC
FROM
all_iv_xform_map_parameters PARMS,
all_iv_xform_map_components COMPS,
all_iv_columns COLMS ,
all_iv_tables TBLS
WHERE
source_parameter_id IS NULL
AND parms.map_component_id = comps.map_component_id
AND UPPER(comps.operator_type) IN ('TABLE','DIMENSION')
AND parms.data_item_id =colms.column_id
AND colms.entity_id =tbls.table_id
)
,
(
SELECT
parms.map_component_id T_COMP_id ,
parms.parameter_id T_PARM_id ,
parms.source_parameter_id T_Sid ,
colms.entity_name T_TBL_name ,
colms.column_name T_FLD_name ,
tbls.schema_name T_TBL_LOC
FROM
all_iv_xform_map_parameters PARMS,
all_iv_xform_map_components COMPS,
all_iv_columns COLMS ,
all_iv_tables TBLS
WHERE
source_parameter_id IS NOT NULL
AND parms.map_component_id = comps.map_component_id
AND UPPER(comps.operator_type) IN ('TABLE','DIMENSION')
AND parms.data_item_id =colms.column_id
AND colms.entity_id =tbls.table_id
)
WHERE
S_PARM_id=T_Sid