Query and Manage Repository Metadata

You can use repository queries to help manage repository metadata and configure the repository to handle large complex queries.

This section contains the following topics:

Query Related Objects

Query Related Objects enables querying objects related to one or more objects that you select from the Physical, Business Model and Mapping, or Presentation layer.

You can only use this feature with objects selected from the same layer. You can't, for example, query objects related to both a Physical layer object and a Business Model and Mapping layer object. See Repository Query Options.

In the Model Administration Tool, open your repository.
Select one or more objects of the same type from a single layer, for example, a set of logical columns from the Business Model and Mapping layer.
Right-click the objects and select Query Related Objects.
Select an object type to narrow your search to a particular type of object, or select All Types to query all objects related to your source objects.

After you select an object type, the Query Related Objects dialog is displayed, showing the objects related to your source objects in the Name list.

Repository Query Options

Review this topic to see options available in the Query Repository dialog.

Option	Description
Mark	Select one or more objects in the Name list and click Mark to mark the selected objects. To unmark the objects, select them and click Mark again. Marking objects makes them easier to visually identify as you develop metadata.
Set Icon	Select one or more objects in the Name list and click Set Icon to select a different icon for the objects. You can set special icons for objects to help visually identify them as having common characteristics. For example, you might want to pick a special icon to identify columns used only by a specific user group. To change the icons back to the original icons, select the objects and click Set Icon again. Then, select Remove associated icon and click OK.
Show Qualified Name	Use this option to display the fully qualified name of the objects found by the query. For example, if you query for logical columns, the default value in the Name list is the column name. However, if you select Show Qualified Names, the value in the Name list changes to `businessmodel.logicaltable.column`.
Show Parent	Select an object in the Name list and click Show Parent to view the parent hierarchy of an object. If the object doesn't have a parent, a message appears. You can't use Show Parent with users or application roles. In the Parent Hierarchy dialog, you can edit or delete objects. If you delete an object from this dialog, any child objects of the selected object are also deleted.
GoTo	Select one or more objects in the Name list and click GoTo to go to the objects in the Model Administration Tool view of the repository. The selected objects appear highlighted in the Physical, Business Model and Mapping, or Presentation layer. The Query Related Objects dialog closes when you choose this option.

Query the Repository

You can query for objects in the repository to examine and update the internal structure of the repository.

Query a repository and view reports that show such items as all tables mapped to a logical source, all references to a particular physical column, content filters for logical sources, initialization blocks, and security and user permissions. Run a report before making any physical changes in a database that might affect the repository. You can save the report to a file in comma-separated value (CSV) or tab-delimited format.

You can construct a filter to restrict the results to display specific values, save a query, run a previously saved query, or create new repository objects. See Construct a Filter for Query Results.

In the Model Administration Tool, open your repository.
Select Tools, then select Query Repository.
In the Query Repository dialog, complete the query information using the table as a guide.
Click Query.

Construct a Filter for Query Results

Use the Query Repository Filter dialog to create criteria the select the data that you want returned in the results.

If you're constructing a complex filter, you might want to click OK after adding each constraint to verify that the filter construction is valid for each constraint.

You can construct multiple filters. When you do, the Operator field becomes active. When the Operator field is active, you can set AND and OR conditions.

See Query Filter Examples.

In the Options dialog on the General tab, select Show Upgrade ID in Query Repository to enable filtering by Upgrade ID.

In the Model Administration Tool, select Tools, then select Query Repository.
In the Query Repository dialog, select an item in the Results list or select an item from the Type list, and then click Filter.
In the Query Repository Filter dialog, click the Item field. The Item list contains the items by which you can filter.
In the Item list, select the filter that you want to apply to the Results or Type object you selected in the previous step.
Type information in the Value column, as appropriate.
Click OK to return to the Query Repository dialog.

Query Filter Examples

Review the examples to learn how to use filters in your queries.

Viewing All Databases Referenced In a Business Model

The following example shows how to create a filter that lets you view all databases referenced in a particular business model.

In the Query Repository dialog, select Database from the Type list, and then click Filter.
In the Query Repository Filter dialog, click the Item field, and then select Related to.
Click the ellipsis button to the right of the Value field, and in the list, choose Select object.
In the Select dialog, select the business model by which you want to filter, and then click Select. Your selection appears in the Value field.
Click OK to return to the Query Repository dialog. The filter appears in the box to the left of the Filter button.
Click Query. The Results list shows the databases referenced by the business model you selected.

Viewing All Presentation Columns Mapped to a Logical Column

The following example shows how to create a filter that lets you view all presentation columns mapped to a particular logical column.

In the Query Repository dialog, select Presentation Column from the Type list, and then click Filter.
In the Query Repository Filter dialog, click the Item field, and then select Column.
Click the ellipsis button to the right of the Value field, and in the list, choose Select object.
In the Select dialog, select the column by which you want to filter, and then click Select. Your selection appears in the Value field.
Click OK to return to the Query Repository dialog. The filter appears in the box to the left of the Filter button.
Click Query. The Results list shows the presentation columns mapped to the logical column you selected.

Nested Queries

The following example shows nested queries, where the filter itself is another query.

In the Query Repository dialog, select Logical Column from the Type list, and then click Filter.
In the Query Repository Filter dialog, click the Item field, and then select Related to.
Click the ellipsis button to the right of the Value field, and in the list, choose Set Condition for Physical Column.
In the new Query Repository Filter dialog, click the Item field, and then select Source column.
Click the ellipsis button to the right of the Value field, and in the list, choose Select Object.
In the Browse dialog, select a source physical column (for example, Column A) and click Select.
Click OK in the Query Repository Filter dialog for the subquery condition. This subquery queries all aliases for the source column you selected.
In the Query Repository Filter dialog for the main query, click the Item field in the next row and then select Related to.
Click the ellipsis button to the right of the Value field, and in the list, choose Select Object.
In the Browse dialog, select the same source physical column (for example, Column A) and click Select.
Select OR from the Operator list.
Click OK to return to the Query Repository dialog. The filter appears in the box to the left of the Filter button.
Click Query. The Results list shows a list of logical columns related to either Column A, or aliases of Column A.

Configure the Repository for Large Complex Queries

Change the OBIS_DISABLE_QUERY_GOVERN_MEMORY parameter value in the obis.properties file to support long and large complex queries that exceed the memory limits imposed by the Oracle BI Server.

The value of OBIS_DISABLE_QUERY_GOVERN_MEMORY itself doesn’t prevent recursive queries from disrupting the availability of the Oracle BI Server. The memory limits are imposed for all queries by default to prevent service disruption. However, the limits might also prevent large, complex queries from running if the query would exceed the built-in limits. Disabling the memory limits of the query governor by setting the OBIS_DISABLE_QUERY_GOVERN_MEMORY value to 1 might allow large, complex queries to run. However, the Oracle BI Server is no longer protected from recursive queries that might disrupt its availability.

Consider changing the OBIS_DISABLE_QUERY_GOVERN_MEMORY value to bypass the memory limits if you see the following error message:

State: HY000. Code: 43119. [nQSError: 43119] Query Failed: (HY000)
State: HY000. Code: 59151. [nQSError: 59151] The user query with request
id:request_ID and logical hash:hash_number exceeded the maximum query governing
memory limit. (HY000)

Open the obis.properties file for editing from the following location:

obiee_home/user_projects/domains/bi/config/fmwconfig/bienv/OBIS/obis.properties
Append the following to the file:

OBIS_DISABLE_QUERY_GOVERN_MEMORY=1
Restart the Oracle BI Server, and retry the query.

Query Data Models Remotely

You can query data models remotely.

For information, see "About Integrating with the Oracle BI Server as a Data Source" in Integrator's Guide for Oracle Business Intelligence Enterprise Edition.