Querying and Managing Repository Metadata

You can use repository queries to help manage repository metadata.

Use the following:

Examine and update the internal structure of the repository. For example, you can query for objects in the repository based on name, type such as Logical Column or Presentation Hierarchy, or on a combination of name and type. You can then edit or delete objects that appear in the Results list. You can also create new objects and view parent hierarchies.
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.

For example, you might want to 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 save a query to run again later, or save the query results to an external file. When you save to an external file, the encoding options are ANSI, Unicode, and UTF-8.

This section contains the following topics:

Querying the Repository

You can query for objects in the repository using the Query Repository tool.

You can also construct a filter to filter the results, save a query, run a previously saved query, or create new repository objects.

To query a repository:

In the 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.

The table lists the options available in the Query Repository dialog.

Option	Description
Name	Use this option to search by object name. You can use an asterisk ( * ) wildcard character to specify any characters. The wildcard character can represent the first or last characters in the search string. Searches are not case sensitive.
Type	Select a type from the list to narrow your search to a particular type of object, or select All Types to query all objects. The list does not contain objects such as aggregate rules, logical source folders, privilege packages, and other objects that are considered internal objects.
Filter	Click Filter to create or edit a filter for your query. After you create a filter, the filter criteria appear in the box to the left of the button. See Constructing a Filter for Query Results for more information.
Query	Click Query when you are ready to submit your query.
Save Query As	Click Save Query As to save your query to run again later. Enter the name of the saved query in the Save query as field, then click Save.
Saved Queries	Click Saved Queries to view or run previously saved queries. You can also delete saved queries. To run a previously saved query, select the row that contains the query you want to run and click Select, or double-click the row. The Saved Queries option is only available if you have previously saved queries.
Edit	After executing a query, select an object from the Results list and click Edit to edit an object in the list of query results. Not all repository objects can be edited from the results list, for example, privilege objects and user database sign-on objects. If an object cannot be edited from the results list, Edit is not available.
Delete	After executing a query, select one or more objects in the Results list and click Delete to delete the objects. After you confirm the deletion, the objects are deleted from your metadata repository.
New	Use this option to create new repository objects. First, select the type of object you want to create from the Type list, then click New. This option is not available when All Types is selected. The dialogs that appear depend on the object type that you select. For more information, refer to the sections that describe how to create that object. Note that if you choose to create a new logical dimension, you must choose whether to create a dimension with a level-based hierarchy, or a parent-child-hierarchy. Similarly, if you choose to create a new Oracle OLAP hierarchy, you must select whether you want to create a level-based or value-based hierarchy.
Show Parent	After executing a query, select an object in the Results list and click Show Parent to view the parent hierarchy of an object. If the object does not have a parent, a message appears. You cannot use Show Parent with users or application roles. In the Parent Hierarchy dialog, you can edit or delete objects. Note that if you delete an object from this dialog, any child objects of the selected object are also deleted.
Mark	After executing a query, select one or more objects in the Results 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	After executing a query, select one or more objects in the Results 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 that will be used only by a certain 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.
GoTo	After executing a query, select one or more objects in the Results list and click GoTo to go to the objects in the Administration Tool view of the repository. The selected objects appear highlighted in the Physical, Business Model and Mapping, or Presentation layer. Note that the Query Repository dialog closes when you choose this option.
Save	After executing a query, click Save to save query results to an external file. Then, in the Save As dialog, provide a name, file type, and encoding value for the file, then click Save.
Columns	Click Columns to add additional columns of information to the results. Then, select the columns you want from the list and click OK. Note that in the Select Columns dialog, you can re-order the columns by selecting a checked column and clicking Up or Down. Select Show Upgrade ID in Query Repository in the General tab of the Options dialog to enable Upgrade ID as a results column. See Setting Administration Tool Options for more information.
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 column of the Results list is the column name. However, if you select Show Qualified Names, the value in the Name list changes to `businessmodel.logicaltable.column`.

Constructing a Filter for Query Results

Use the Query Repository Filter dialog to filter the results in the Results list of the Query Repository dialog.

The Query Repository Filter dialog contains five columns: an Item column and its operator or selection column, a Value column and its operator or selection column, and a Delete column that lets you delete the selected filter.

To construct a filter:

In the 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.
Select Show Upgrade ID in Query Repository in the General tab of the Options dialog to enable filtering by Upgrade ID. See Setting Administration Tool Options for more information.
In the Item list, select the filter that you want to apply to the Results or Type object you selected in Step . Then, adjust or enter information in the Value column, as appropriate.
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.
Click OK to return to the Query Repository dialog. The filter appears in the box to the left of the Filter button.

Note:

If you are 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.

Example 17-1 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.

Example 17-2 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.

Example 17-3 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.

Example 17-1 and Example 17-2 show how to create different kinds of filters.

Querying Related Objects

The Query Related Objects feature enables you to query 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. For example, you cannot query objects related to both a Physical layer object and a Business Model and Mapping layer object.

To query objects related to a selected object:

In the Administration Tool, open your repository.
Select one or more objects from a single layer (for example, a set of logical columns from the Business Model and Mapping layer). The objects you select must all be of the same type.
Right-click the objects and select Query Related Objects.
From the right-click submenu, 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. If you have previously made queries for this source object type, the three most recent queries are available at the top of the submenu.

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.

The table lists the 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 that will be used only by a certain 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 does not have a parent, a message appears. You cannot use Show Parent with users or application roles. In the Parent Hierarchy dialog, you can edit or delete objects. Note that 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 Administration Tool view of the repository. The selected objects appear highlighted in the Physical, Business Model and Mapping, or Presentation layer. Note that the Query Related Objects dialog closes when you choose this option.