Using Administration Tool Utilities

The Oracle BI Administration Tool provides several utilities and wizards that perform functions like renaming objects, persisting aggregates, and externalizing strings.

This section contains the following topics:

Using the Replace Column or Table Wizard

The Replace Column or Table Wizard automates the process of replacing physical columns or tables in logical table sources.

For example, if you have purchased Oracle BI Applications, you can update your logical table sources to map to a different database type. You can also use this utility to change logical table source mappings from a development table to a production table.

You can use the Replace Column or Table Wizard to replace a single column in the same table, or to replace an entire table. If you replace a table, you must map all the columns in the table.

In the Administration Tool, select Tools, then select Utilities.
Select Replace Column or Table in Logical Table Sources and click Execute.
In the Select Object screen, select Replace whole table.
In the left pane, select the physical table that you want to replace.
In the right pane, select the physical table that you want to use as a replacement for the original table. Then, click Next.
The Select Sources screen shows all logical table sources that map to the physical table you selected. Select the logical table sources in which you want to change the physical table mapping. Select Show Qualified Names to see the full context for each source.

If you select an invalid logical table source, or in other words, one that cannot be used for replacement, a message appears explaining why that source cannot be used, and the check box for that source is disabled.

Note that invalid logical table sources do not appear in the list when Hide unusable logical table sources in Replace wizard has been selected in the General tab of the Options dialog. Instead, the Info button is displayed when a logical table source that maps to that column does not appear in the list. Click Info to see details on why the physical objects could not be replaced in the logical table source or sources.

The Select Sources screen only appears if there are multiple logical table sources that map to the physical table you selected.

Click Next after you have selected logical table sources.
The bottom pane of the Select Columns screen shows individual column mappings between the selected physical tables. If column names in the two selected tables match, default column mappings appear in the bottom pane.

To add a column mapping to the list of mapped columns, first select a source column in the left pane. Then, select a replacement column in the right pane and click Add.

To remove a column mapping from the list of mapped columns, select a row of mapped columns from the list and click Remove.

The image shows the Select Columns screen.

Description of the illustration GUID-861C053C-9A9D-4BD0-BE05-502FE0DAB755-default.gif
When you have finished mapping columns between the selected physical tables, click Next.
When the repository is open in online mode, the Checkout screen appears. In online mode, objects need to be checked out before you can make changes to them. Click Next to check out the necessary objects.
The Finish screen displays a summary of the objects that will be replaced. If you want to make changes, click Back, or select a particular step from the navigation panel.
Click Finish.

Using the Oracle Business Intelligence Event Tables Utility

The Event Tables utility lets you identify a table as an Oracle Business Intelligence event polling table.

An event polling table is a way to notify the Oracle BI Server that one or more physical tables have been updated.

Each row that is added to an event table describes a single update event. The cache system reads rows from, or polls, the event table, extracts the physical table information from the rows, and purges cache entries that reference those physical tables.

See Cache Event Processing with an Event Polling Table in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

In the Administration Tool, select Tools, then select Utilities.
Select Oracle BI EE Event Tables and click Execute.

Using the Externalize Strings Utility

You can use the Externalize Strings utility to localize the names of Presentation layer subject areas, tables, hierarchies, columns and their descriptions.

You can save these text strings to an external file with ANSI, Unicode, and UTF-8 encoding options. You can also choose to save strings to a set of XML files with Unicode encryption.

Before you can use the Externalize Strings utility, you must externalize strings in the Presentation layer, consider the following:

You can right-click any Presentation layer object, such as a subject area, presentation table, or presentation column, and choose Externalize Display Names > Generate Custom Names or Externalize Descriptions > Generate Custom Descriptions to externalize strings. When you select Generate Custom Names and then run the Externalize Strings utility, the translation key also appears in the Externalize Strings dialog.
Choosing one of these right-click externalization options automatically selects the Custom display name or Custom description options in the Properties dialog for the selected object and all of its child objects.

For example, if you right-click a subject area and choose one of the externalization options, the externalization flag is set on all presentation tables, columns, hierarchies, and levels within that subject area.
Running the Externalize Strings utility only externalizes those strings that have been selected for externalization in the Presentation layer.

See Localizing Metadata Names in the Repository in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

To run the externalizestrings utility, do one of the following:
- From the Oracle BI Administration Tool, select Tools, select Utilities, select Externalize Strings and then click Execute.
- Use the externalizestrings command-line utility located in BI_DOMAIN/bitools/bin, and see the required syntax displayed within the externalizestrings utility.

Using the Rename Wizard

You can use the Rename Wizard to rename tables and columns in the Presentation layer and Business Model and Mapping layer.

It provides a convenient way to transform physical names to user-friendly names.

It is a best practice to rename objects in the Business Model and Mapping layer rather than the Presentation layer, for better maintainability. Giving user-friendly names to logical objects rather than presentation objects ensures that the names can be reused in multiple subject areas. Also, it ensures that the names persist even when you need to delete and re-create subject areas to incorporate changes to your business model.

In the Administration Tool, select Tools, then select Utilities. Then, select Rename Wizard and click Execute.

You can also access the Rename Wizard by right-clicking an object or set of objects in the Business Model and Mapping layer or Presentation layer, and then selecting Rename Wizard. The wizard starts in the Select Rules screen and only applies to the logical or presentation objects you selected.
In the Select Objects screen, select the objects you want to rename. First, select the layer that contains the objects (Presentation or Business Model and Mapping), then select an object and click Add. Click Add Hierarchy to add all objects associated with the selected object. Note the following information:
- You must enable the Edit presentation names Administration Tool option before you can select objects from the Presentation layer.
- You can only select individual presentation columns with the Use Logical Column Name property not selected (set to false).
Click Next after you have selected the objects you want to rename.
In the Select Types screen, select the object types you want to rename, such as Subject Area, Logical Table, or Logical Column. Note that if you select Presentation Column, then only presentation columns with the Use Logical Column Name property not selected (set to false) will be renamed. Then, click Next.
In the Select Rules screen, select the renaming rules you want to apply and click Add. Select Change specified text to rename particular words or phrases.
The renaming rules are applied in the order in which they appear in the list. Select a rule that you have added and click Up or Down to change the order in which the rules will be applied.

For example, say you want to rename the logical columns GlobalGROUP, GlobalSales, and GlobalCustomerName to Group, Sales, and Customer Name. You can apply the following rules in the given order:
```
Insert space before each first uppercase letter, unless on the first position
or there is a space already
All text lowercase
First letter of each word capital
Change each occurrence of "Global " to "" (not case sensitive)
```
Click Next after you have selected renaming rules.
When the repository is open in online mode, the Checkout screen appears. In online mode, objects need to be checked out before you can make changes to them. Click Next to check out the necessary objects.
The Finish screen displays a summary of the objects that will be renamed. If you want to make changes to the list of renamed objects, click Back, or select a particular step from the navigation panel. Click Finish to rename the objects.

The image shows the Rename Wizard.

Description of GUID-815787AC-AFA4-4FD5-92C4-0497AB7AA856-default.gif follows

Description of the illustration GUID-815787AC-AFA4-4FD5-92C4-0497AB7AA856-default.gif

Using the Update Physical Layer Wizard

You can use the Update Physical Layer Wizard to update database objects in the Physical layer of a repository, based on their current definitions in the back-end database.

The Update Physical Layer wizard is only available for repositories open in online mode.

When the wizard processes the update, the Presentation Services connects to each back-end database. The objects in the Physical layer are compared with those in the back-end database. Explanatory text alerts you to differences between objects as defined in the database in the Physical layer and as defined the back-end database, such as data type-length mismatches and objects that are no longer found in the back-end database. For example, if an object exists in the database in the Physical layer of the repository but not in the back-end database, the following text is displayed:

Object does not exist in the database and will be deleted.

The wizard does not add columns or tables to the repository that exist in the back-end database, but not in the repository. Additionally, the wizard does not update column key assignments. It checks that there is a column in the repository that matches the column in the database, and then, if the values do not match, the wizard updates the type and length of the column in the repository.

The connection pool settings for each database need to match the connection pool settings used when the objects were last imported into the Physical layer from the back-end database. See Creating or Changing Connection Pools.

In the Administration Tool, select Tools, then select Utilities.
Select Update Physical Layer and click Execute.

The databases in the Physical layer of the repository are listed in the left pane of the wizard.
In Select Database, select the databases to update in the left pane, and then click Add.
Click Remove to remove a database from the selection list.
Click Next.
In Select Connection Pool, select the connection pool for each database to update, and then click Next.
You might need to set or confirm values for variables before continuing.
In Update, review the information about each update and select the updates to perform.
In online mode, you must check out objects before you can make changes to them.
Click Next to check out the necessary objects.
Click Finish.

The wizard updates the objects in the Physical layer, and then closes automatically. Objects that do not exist in the database are deleted.
From the File menu, select Save to save the updated objects in the Physical layer.

Generating Documentation of Repository Mappings

The Repository Documentation utility documents the mapping from the presentation columns to the corresponding logical and physical columns.

The documentation also includes conditional expressions associated with the columns. The documentation can be saved in comma separated (CSV), XML, or tab delimited (TXT) format.

You can use the Repository Documentation utility to extract Oracle Business Intelligence metadata to a flat file so that it can be loaded into Excel and RDBMS. You can query the resulting file to answer questions such as "If I delete physical column X, what logical columns will be affected?" or "How many places in the business model refer to the physical table W_SRVREQ_F?" Then, you can establish dependency relationships among elements in the repository.

Excel only allows data sets of 1,000,000 rows. You might exceed this in a large repository. Therefore, you might want to run the Repository Documentation utility on a subset of the repository by extracting relevant business models into a new project. See Setting Up and Using the Multiuser Development Environment.

The Repository Documentation utility creates a comma-separated values file or a tab-separated values file that shows the connections between the Presentation and Physical layers in the current repository. This file can be imported into a repository as a Physical layer. Note that the file does not include information about repository variables and marketing objects.

In the Administration Tool, select Tools, then select Utilities.
Select Repository Documentation and click Execute.
In the Save As dialog, choose the directory where you want to save the file.
Type a name for the file.
Choose a type of file and an Encoding value, and then click Save. Current encoding options are ANSI, Unicode, and UTF-8.

Generating a Metadata Dictionary

You can generate a metadata dictionary to help Oracle Business Intelligence users obtain more information about metrics or attributes for repository objects.

For example, users might need to resolve issues caused by confusing metadata object names, or to obtain more details when an attribute is derived in a complicated way.

A metadata dictionary is a static set of XML documents. Each XML document describes a metadata object, such as a column, including its properties and relationships with other metadata objects. These XML documents can be viewed within the Oracle BI Presentation Services user interface, or they can be viewed directly in a browser.

Use the Administration Tool to generate a metadata dictionary for your repository. Because the dictionary does not change dynamically as repository changes are made, you must generate the dictionary periodically to update the content.

The metadata dictionary files need to be hosted on a Web server, such as Oracle HTTP Server or Apache HTTP Server. When you generate the dictionary, you can set the output location to the final location on the Web server, or to a temporary location. If you generate the dictionary in a temporary location, you must then copy the files to the location on the Web server.

Note that some large repositories can contain tens of thousands of objects. Generating a dictionary for a large repository can take a significant period of time.

In the Administration Tool, open your repository in offline mode. You cannot generate a metadata dictionary in online mode.
Select Tools, then select Utilities.
Select Generate Metadata Dictionary and click Execute.
In the Choose Directory dialog, click Browse to locate and select the location where you want to store the dictionary. You can select a destination for your dictionary in the following ways:
- Select a local or network location. When the dictionary is generated, a subdirectory with the same name as the repository is created in that location. The dictionary directories and files are created in that subdirectory.
  
  For example, if you select J:\BI_DataDictionary and your repository name is demo1.rpd, the dictionary files, including the style sheets, will be located in J:\BI_DataDictionary\demo1.
- If you want to use an IIS virtual directory, you can create or select a virtual directory in IIS before you generate the dictionary. When you generate the dictionary, choose the physical directory associated with the IIS virtual directory.
Note that you cannot store the dictionary in a directory with multibyte characters. If you receive a system error about creating the necessary directories for the dictionary, then you must choose another directory.
Click OK.
If you did not save the files directly to a location on a Web server, copy the files over to your Web server and ensure they are accessible. Refer to the documentation for your Web server for detailed information.

The location where the metadata dictionary files can be viewed is dependent on the host name and port number of your Web server, along with the directory location where you store the files.
You must edit the instanceconfig.xml configuration file to enable the metadata dictionary feature in the Oracle BI Presentation Services user interface, as well as grant the appropriate privilege to your users, groups, or application roles.

See Providing Access to Metadata Dictionary Information.

After you generate a metadata dictionary, style sheets and index files are created for that dictionary. The related style sheets (XSL files) are created and stored in a directory named xsl within the repository directory.

A name index and tree index are created and stored in the [drive]:\[path]\[repository name] root directory. The index files are associated with each other so that you can quickly switch views.

To learn about viewing metadata dictionary information from the Oracle BI Presentation Services user interface, see User's Guide for Oracle Business Intelligence Enterprise Edition.

Providing Access to Metadata Dictionary Information

When creating analyses, content designers might need more information about subject areas, folders, columns, or levels such as relationships to other metadata objects to guide them.

You can provide content designers with this information by allowing them access to the metadata dictionary for the repository.

The metadata dictionary describes the metrics that are contained within the repository and the attributes of repository objects. The metadata dictionary output is a static set of XML documents.

See Managing Presentation Services Privileges Using Application Roles in Security Guide for Oracle Business Intelligence Enterprise Edition.

Ensure that the metadata dictionary has been generated and the files have been stored in an appropriate location. See Generating a Metadata Dictionary.

Set the DictionaryURLPrefix element within the ServerInstance element in the instanceconfig.xml file to one of the following values. The value that you specify depends on the web servers in use.
- The prefix for the name of the directory in which you have stored the XML files. The directory must have been specified as a shared directory for the web server, and the web server must be the same one that is used by Oracle BI EE.
  
  For example, suppose that you stored the XML files for the metadata dictionary in a directory called demo1 under the metadictionary directory. Suppose that the metadictionary directory is specified as a shared directory for the web server, which is also used by Oracle BI EE. Then you specify the following value for the DictionaryURLPrefix element:
  
  <DictionaryURLPrefix>demo1</DictionaryURLPrefix>
  
  See the documentation for the web server for information about sharing directories.
- The URL that points to the directory in which you have stored the XML files. Use a value such as this when the files for the metadata dictionary are stored in the directory structure for a web server that is not being used by Oracle BI EE. For example:
  
  <DictionaryURLPrefix>http://10.10.10.10/metadictionary/demo1</DictionaryURLPrefix>
The following shows an example setting in the instanceconfig.xml file:
```
<WebConfig>
  <ServerInstance>
    <SubjectAreaMetadata>
      <DictionaryURLPrefix>demo1</DictionaryURLPrefix>
    </SubjectAreaMetadata>
  </ServerInstance>
</WebConfig>
```
Grant the Access to Metadata Dictionary privilege to the appropriate content designers.

See Viewing Metadata Information from the Subject Areas Pane in User's Guide for Oracle Business Intelligence Enterprise Edition.

Removing Unused Physical Objects

Use the procedure to remove objects that you no longer need in your repository.

Large repositories use more memory on the server and are harder to maintain. Additionally, development activities take longer on a large repository. You can remove databases, initialization blocks, physical catalogs, and variables.

In the Administration Tool, select Tools, then select Utilities.
Select Remove Unused Physical Objects and click Execute.
In the Remove Unused Physical Objects dialog, from the Type list, select the type of object.
In the list of objects, verify that only the objects that you want to remove are selected.

Below the list of objects, the number of selected objects and the total number of objects appears.
To remove the selected objects, click Yes.

Persisting Aggregates

You can use the Aggregate Persistence Wizard to create the SQL file used to create aggregate tables and map them into the metadata.

See Using the Aggregate Persistence Wizard to Generate the Aggregate Specification.

Using the Convert Presentation Folders Utility

You can designate child presentation tables using the Child Presentation Tables tab in the Presentation Table dialog to give the appearance of nested folders in Answers and BI Composer.

You could add one level of nesting in Answers by adding a hyphen at the beginning of a presentation table name, or by adding an arrow (->) at the beginning of a presentation table description. If you used these methods, Oracle recommends that you run the Convert Presentation Folders utility to convert your metadata to the new structure.

Note:

Creating nesting by adding hyphens at the beginning of presentation table names or adding arrows at the beginning of presentation table descriptions is deprecated for this release and in a future release.

Open your repository in the Administration Tool in offline mode.

Note:

Do not run the Convert Presentation Folders utility in online mode.
Select Tools, then select Utilities.
Select Convert Presentation Folders and click Execute.

The hyphens and arrows disappear from presentation table names and descriptions, and the affected tables are listed as child tables for the appropriate parent object.

Generating a List of Logical Column Types

You can use the Generate Logical Column Type Document utility to generate a complete list of logical columns and their corresponding types.

The output is stored in XML format. You can select ANSI, Unicode, or UTF-8 encoding options.

This utility is often used with the Compare Logical Column Types utility. See Comparing Logical Column Types.

In the Administration Tool, select Tools, then select Utilities.
Select Generate Logical Column Type Document and click Execute.
In the Save As dialog, choose the directory where you want to save the file.
Type a name for the file. The file must have an XML extension.
Choose an Encoding value, and then click Save.

Using the biservergentypexml Utility to Generate a List of Logical Column Types

You can generate a list of logical columns and their corresponding types using the biservergentypexml utility.

The biservergentypexml utility is similar to the Generate Logical Column Type Document utility in the Administration Tool. This utility is available on both Windows and UNIX systems. You can only use biservergentypexml with binary repositories in RPD format.

The location of the biservergentypexml utility is:

BI_DOMAIN/bitools/bin

Syntax

The biservergentypexml utility takes the following parameters:

biservergentypexml -R repository_name [-P repository_password] 
-O output_XML_file_name {-8 | -U | -A}

Where:

repository_name is the name and path of the repository from which you want to generate a list of logical column types.

repository_password is the password for the repository from which you want to generate a list of logical column types.

Note that the repository_password argument is optional. If you do not provide the password argument, you are prompted to enter the password when you run the command. To minimize the risk of security breaches, Oracle recommends that you do not provide password arguments either on the command line or in scripts. Note that the password argument is supported for backward compatibility only, and will be removed in a future release. For scripting purposes, you can pass the password through standard input.

output_XML_file_name is the name and path of the XML file where you want to store the output generated by the utility.

- 8 specifies UTF-8 encoding for the output file.

- U specifies Unicode encoding for the output file.

- A specifies ANSI encoding for the output file.

Example

The following example creates a UTF-8 encoded output XML file called log_col_types.xml that includes logical column type information from my_repos.rpd.

biservergentypexml -R my_repos.rpd -O log_col_types.xml -8
Give password: my_rpd_password

Note:

Be sure to provide the full path names to your repository file and XML output file if they are located in a different directory.

Sample Output for a Logical Column Types Document

You can review sample output for a logical column types document.

The logical column types document was generated with the Generate Logical Column Type Document utility in the Administration Tool or with the biservergentypexml utility, appears as follows:

<?xml version="1.0" encoding="UTF-8" ?>
 <REPOSITORY>
  <BUSINESS_MODEL NAME="SampleApp Lite">
   <LOGICAL_TABLE NAME="D1 Products">
    <COLUMN NAME="Product Number">
     <TYPE>INT</TYPE>
     <NULLABLE>No</NULLABLE>
    </COLUMN>
    <COLUMN NAME="Product">
     <TYPE>VARCHAR</TYPE>
     <LENGTH>25</LENGTH>
     <NULLABLE>No</NULLABLE>
    </COLUMN>
    <COLUMN NAME="Product Type">
     <TYPE>VARCHAR</TYPE>
     <LENGTH>25</LENGTH>
     <NULLABLE>No</NULLABLE>
    </COLUMN>
    <COLUMN NAME="Product Type Key">
     <TYPE>INT</TYPE>
     <NULLABLE>No</NULLABLE>
    </COLUMN>
...
   </LOGICAL_TABLE>
   <LOGICAL_TABLE NAME="D0 Time">
    <COLUMN NAME="Calendar Date">
     <TYPE>DATE</TYPE>
     <NULLABLE>No</NULLABLE>
    </COLUMN>
    <COLUMN NAME="Per Name Week">
     <TYPE>VARCHAR</TYPE>
     <LENGTH>12</LENGTH>
     <NULLABLE>No</NULLABLE>
    </COLUMN>
...
   </LOGICAL_TABLE>
  </BUSINESS_MODEL>
 </REPOSITORY>

Comparing Logical Column Types

In Oracle BI EE logical column types can change over the course of MUD development, resulting in unexpected logical column types.

When this occurs, you can generate a list of logical columns and their types using the Generate Logical Column Type Document utility in the Administration Tool or biservergentypexml, and then use the Compare Logical Column Types utility for subsequent MUD versions to ensure that the logical column types match as expected. For example, you could generate a logical column type list for repository version 20, and then use the Compare Logical Column Types utility to compare the list against repository version 30.

To use this utility, you must have already generated a list of logical column types with which you want to compare the current repository. The Compare Logical Column Types utility only compares logical columns that exist in both the repository and the XML file; newly created logical columns and deleted columns are ignored.

In the Administration Tool, select Tools, then select Utilities.
Select Compare Logical Column Types and click Execute.
In the Select XML File dialog, select the generated list of logical column types with which you want to compare the column types in the current repository.
Click Open .

Fixing Upgrade IDs

In cases where you are comparing or merging repositories, the upgrade IDs sometimes do not function correctly.

You can use the Fix Upgrade IDs utility to correct issues with upgrade IDs.

Oracle BI uses upgrade IDs to compare or merge repositories. They identify when two object in two repositories are supposed to be the same object. However, in some cases, the upgrade IDs do not work correctly. For example, when two or more objects have the same upgrade ID, when objects are missing upgrade IDs, and when hidden internal object have upgrade IDs set.

In the Administration Tool, select Tools, then select Utilities.
Select Fix Upgrade IDs and click Execute.

If you ran the utility on a repository open in read-only mode, then the utility reports how many invalid upgrade IDs are in the repository. To fix the upgrade IDs, you must open the repository in non-ready-only mode and rerun the utility.

If you ran the utility on a repository that is not read-only, then the utility fixes the invalid upgrade IDs and displays a message stating how many upgrade IDs it fixed.

Setting Permissions In Bulk

You can use the Set Permissions in Bulk utility when you want to assign the same object, data filters, and query limits permissions to several users or roles at the same time.

In the Oracle BI Administration Tool, select Tools, then select Utilities.
Select Set Permissions in Bulk and click Execute.
In the Set Permissions in Bulk dialog, specify the items to set permissions for Object Permissions, Data Filters, and Query Limits.
Select the users and roles for which you want to bulk assign permissions and click Add to move them to the selected table.
Click OK.
In the dialog, select the tab corresponding to the item to bulk assign permissions, for example, Data Filters, and specify the permissions to assign to the users and roles you chose.
After you have specified the permissions, click OK.