OWB Public Views in 11g Release 2
By Dong Ruirong on Jan 21, 2010
Oracle Warehouse Builder (OWB) Public Views is a relational public interface to OWB repository. It helps users to have a direct access to OWB metadata by using SQL. The Warehouse Builder provides a set of pre-built views for both the design and runtime environments. Compared to other OWB public access methods, such as Scripting Command and OWB Repository Browser, Public Views provides fast, lightweight, secure, multiple-user enabled and remote access to the OWB metadata.
OWB Browser is entirely relying on OWB Public Views to provide metadata support. Many OWB customers, such as data warehouse developers, consultants and administrators, are using OWB Public Views to perform metadata management work. But remember that you must be familiar with the public views before accessing them.
You can access Public Views by logging in to SQL*Plus with default workspace. When you login to SQL*Plus, you can access Public Views (Design-Time Public Views or Runtime Public Views) from your default workspace. If you try to access Public Views from any workspace other than the default, then you must call: wb_workspace_management.set_workspace(<wksp_name>, <wksp_owner>). The package wb_workspace_management contains the procedures used to create/drop workspace, register/unregister workspace users, grant/revoke workspace privileges, set workspace for user etc. If you want to switch to a workspace other than the default one, then you can call the wb_workspace_management.set_workspace procedure. You must have the ACCESS_PUBLICVIEW_BROWSER system privilege to retrieve useful information from the Pubic Views. Otherwise, you will get "0 rows returned." You may need to ask the workspace owner or workspace admin to grant the system privilege ACCESS_PUBLICVIEW_BROWSER. However, this method can not be applied to OWB 11g Release 1. If you want to access OWB Public Views in OWB 11g Release 1, click here for details.
An example that shows how to play around with Public Views:
If you want to get the details of some Mappings, what should you do? To achieve the details, you should focus on the following OWB Public Views:
Before starting the exercise, you need to create a project: OWBB_PROJ, an oracle module: ORA_MOD, an oracle location: ORA_MOD_LOC, two tables: T_SRC and T_TGT and a mapping T2T_MAPING, add several columns to tables T_SRC and T_TGT and keep the column names of both tables the same, add table T_SRC and table T_TGT to the mapping T2T_MAPING and add mapping edges to connect columns of the table operators by column names, then deploy the tables and the mapping, and start the mapping. Simply, you can import the MDL file to set up the environment.
Get all Mappings:
Of cause, you can select all fields of the view. But the most important fields are MAP_ID and MAP_NAME.
Get the information of the project and module that the mapping belongs to:
Get the operators of a Mapping using Mapping's id:
Get the operator information using operator's id:
Get the mapping group information using operator's id:
Get the parameters of a mapping group using operator's id: