Do you need to document EUL Schema, Oracle Discoverer Work-books, worksheets?
Discoverer is a great ad-hoc and BI tool. People working and using this tool often need to document the EUL (End User Layer) but there is no straight forward ways to do it. I have seen clients run some scripts against the EUL meta data and then create a word doc file for later referencing.
Lets focus on some of the key EUL tables which could be used to document the different Discoverer components.
In the below examples, replace "disco" with the appropriate schema owner of EUL tables. In my case it is the disco owner and hence the examples
List of Business Areas
select ba_name "Business Area", ba_created_by "Creator", ba_created_date "Creation Date", ba_updated_by "Updated By ", ba_updated_date "Last Update Date" , ba_id
from disco.eul4_bas
where ba_created_by like 'DISCO'
List of Folders
select b.ba_name, f.obj_name folder_name, f.obj_id, f.obj_ext_owner Owner
from disco.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_bas b
where 1=1
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('%')
order by b.ba_name,f.obj_name
List of Folder Items
select i.exp_name item_name, i.exp_id, i.it_ext_column, f.obj_name folder_name, b.ba_name
from disco.eul4_expressions i,
disco.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_bas b
where f.obj_id= i.it_obj_id
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(i.exp_name) like upper('%')
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('Products')
order by b.ba_name,
f.obj_name,
i.exp_name
List of Folder Joins
select key_description
from disco.eul4_key_cons
where fk_obj_id_remote=100027 or key_obj_id=100027
(substitute the numeric id from the Folders obj_id (see the List of Folder query)
List of Workbooks
select doc_name "Document",doc_developer_key, doc_description "Description" from disco.eul4_documents
Documenting Workbooks and worksheets ..........continued ....
Discoverer is a great ad-hoc and BI tool. People working and using this tool often need to document the EUL (End User Layer) but there is no straight forward ways to do it. I have seen clients run some scripts against the EUL meta data and then create a word doc file for later referencing.
Lets focus on some of the key EUL tables which could be used to document the different Discoverer components.
In the below examples, replace "disco" with the appropriate schema owner of EUL tables. In my case it is the disco owner and hence the examples
List of Business Areas
select ba_name "Business Area", ba_created_by "Creator", ba_created_date "Creation Date", ba_updated_by "Updated By ", ba_updated_date "Last Update Date" , ba_id
from disco.eul4_bas
where ba_created_by like 'DISCO'
List of Folders
select b.ba_name, f.obj_name folder_name, f.obj_id, f.obj_ext_owner Owner
from disco.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_bas b
where 1=1
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('%')
order by b.ba_name,f.obj_name
List of Folder Items
select i.exp_name item_name, i.exp_id, i.it_ext_column, f.obj_name folder_name, b.ba_name
from disco.eul4_expressions i,
disco.eul4_objs f,
disco.eul4_ba_obj_links l,
disco.eul4_bas b
where f.obj_id= i.it_obj_id
and f.obj_id= l.bol_obj_id
and b.ba_id= l.bol_ba_id
and upper(i.exp_name) like upper('%')
and upper(b.ba_name) like upper('Video Store Tutorial')
and upper(f.obj_name) like upper('Products')
order by b.ba_name,
f.obj_name,
i.exp_name
List of Folder Joins
select key_description
from disco.eul4_key_cons
where fk_obj_id_remote=100027 or key_obj_id=100027
(substitute the numeric id from the Folders obj_id (see the List of Folder query)
List of Workbooks
select doc_name "Document",doc_developer_key, doc_description "Description" from disco.eul4_documents
Documenting Workbooks and worksheets ..........continued ....
No comments:
Post a Comment