18 Using Expression Builder and Other Utilities

This chapter describes Expression Builder and provides instructions for creating constraints, aggregations, and other definitions within the Oracle BI repository. It also describes the various utilities and wizards contained in the Oracle BI Administration Tool.

This chapter contains the following topics:

Using Expression Builder

You can use the Expression Builder dialogs in the Oracle BI Administration Tool to create constraints, aggregations, and other definitions within a repository.

Expression Builder provides automatic color highlighting and other formatting enhancements to make expressions easier to build and to read.

The expressions you create with Expression Builder are similar to expressions created with SQL. Except where noted, you can use all expressions constructed with Expression Builder in SQL queries against the Oracle BI Server.

This section contains the following topics:

About the Expression Builder Dialogs

The Expression Builder contains a number of dialogs.

When creating expressions in Expression Builder, you select a category from the Category pane and values are displayed in the lower panes depending on the value selected in the Category pane. When you type a value into a Find field, it filters out the non-matching strings and displays matching strings only. After typing search criteria in a Find field, you can move up and down the list using the scroll bar, and use the tab key to move between the Find fields. To return to the full list of results, delete the string from the Find field.

Note:

You can only enter text in the Find field that matches the text of one of the available strings. For example, if the available string options begin with A11, A12, and A13, the text you enter in the Find field must begin with A.

When you first open Expression Builder, the items are not sorted. When selected, the Sort Panes option sorts all items in the panes. As soon as you select this option, the panes are automatically redrawn without changing the contents of the panes or your filtering criteria.

About the Expression Builder Toolbar

Describes operators on the Expression Builder toolbar.

The toolbar is located at the bottom of Expression Builder.

The table describes each button and its function in an expression.

Operator	Description
+	Plus sign for addition.
-	Minus sign for subtraction.
*	Multiply sign for multiplication.
/	Divide by sign for division.
\|\|	Character string concatenation.
(	Open parenthesis.
)	Close parenthesis.
>	Greater than sign, indicating values higher than the comparison.
<	Less than sign, indicating values lower than the comparison.
=	Equal sign, indicating the same value.
<=	Less than or equal to sign, indicating values the same or lower than the comparison.
>=	Greater than or equal to sign, indicating values the same or higher than the comparison.
<>	Not equal to, indicating values higher or lower, but different.
AND	`AND` connective, indicating intersection with one or more conditions to form a compound condition.
OR	`OR` connective, indicating the union with one or more conditions to form a compound condition.
NOT	`NOT` connective, indicating a condition is not met.
,	Comma, used to separate elements in a list.

About the Categories in the Category Pane

The categories that appear in the Category pane vary, depending on the dialog from which you accessed Expression Builder.

Category Name	Description
Aggregate Content	Contains the available aggregate functions. Aggregate sources must use one of the functions listed here to specify the level of their content.
Time Dimensions	Contains the time dimensions configured in the business model. If no time dimensions exist in a business model, or if time dimensions are not pertinent to a particular Expression Builder, the Time Dimensions category is not displayed. When you select the Time Dimensions category, each configured time dimension appears in the middle pane, and each level for the selected dimension appears in the lower pane.
Logical Tables	Contains the logical tables configured in the business model. If logical tables are not pertinent to a particular Expression Builder, the Logical Tables category is not displayed. When you select the Logical Tables category, each logical table in the business model appears in the middle pane, and each column for the selected logical table appears in the lower pane.
Value Based Dimensions	Contains the dimensions with parent-child hierarchies configured in the business model. If no dimensions with parent-child hierarchies exist in a business model, or if dimensions with parent-child hierarchies are not pertinent to a particular Expression Builder, the Value Based Dimensions category is not displayed. When you select the Value Based Dimensions category, the configured dimensions with parent-child hierarchies appear in the middle pane. No lower pane exists for this category.
Logical Levels	Contains the related logical levels. If level-based dimensions are not pertinent to a particular Expression Builder, the Logical Levels category is not displayed. When you select the Logical Levels category, you can then select the appropriate logical dimension (level-based) in the middle pane, and the level itself in the lower pane.
Physical Tables	Contains the related physical tables. If physical tables are not pertinent to a particular Expression Builder, the Physical Tables category is not displayed.
Operators	Contains the available SQL logical operators.
Expressions	Contains the available expressions.
Functions	Contains the available functions. The functions that appear depend on the object you selected.
Constants	Contains the available constants.
Types	Contains the available data types.
Repository Variables	Contains the available repository variables. If no repository variables are defined, this category does not appear.
Session Variables	Contains the available system session and non-system session variables. If no session variables are defined, this category does not appear.

Setting Up an Expression

You can view the Expression Builder dialog for a derived logical column.

To set up an expression, select Functions from the Category pane, select a function type from Functions pane, then select a function from the lower pane.

Description of GUID-85DC28EE-101C-4445-8B4C-26B2C1037FCD-default.gif follows

Description of the illustration GUID-85DC28EE-101C-4445-8B4C-26B2C1037FCD-default.gif

Double-click the function you want to use to paste it in the edit pane. Then, in the edit pane, click once between the parentheses of the function to select that area as the insertion point for adding the argument of the function.

To paste a logical column at the insertion point, select Logical Tables from the Category pane, select the table you want to use in the Logical Tables pane, and then double-click the logical column in the lower pane to paste the logical column at the insertion point as the argument of the function in the edit pane. The image shows where the expression appears in the edit pane.

Description of GUID-0EC46CA6-6009-4E75-AE50-145A8FA26124-default.gif follows

Description of the illustration GUID-0EC46CA6-6009-4E75-AE50-145A8FA26124-default.gif

Navigating Within Expression Builder

Use these steps to navigate within Expression Builder.

In the Category pane, select the appropriate category for the expression you want to build.

The available expression types for the selected category appear in the middle pane.
Select the appropriate item for the expression you want to build.

The available building blocks for the selected item appear in the lower pane.
Double-click a building block to display it in the edit pane.
To insert an operator into the expression, click an operator on the Expression Builder toolbar.

Building an Expression

Use these steps to build an expression in Expression Builder.

Navigate to the individual building blocks you want in the expression.

The Syntax bar at the bottom of the Expression Builder dialog shows the syntax for the expression.

For example: BETWEEN <<Upper Bound>> AND <<Lower Bound>>
Add the building blocks to the edit pane.
Edit the building blocks to reflect the expression you want.
Use the Expression Builder toolbar to insert operators into the expression.
Repeat the preceding steps until the expression is complete, and then click OK.

The Administration Tool displays a message for any syntax errors in the expression. When the expression is syntactically correct, the Administration Tool adds the expression to the dialog from which you accessed Expression Builder.

If the parameter PREVENT_DIVIDE_BY_ZERO is set to YES in NQSConfig.INI, the Oracle BI Server prevents errors in divide-by-zero situations, even for Answers column calculations. The Oracle BI Server creates a divide-by-zero prevention expression using nullif() or a similar function when it writes the physical SQL. Because of this, you do not have to use CASE statements to avoid divide-by-zero errors, as long as PREVENT_DIVIDE_BY_ZERO is set to YES (the default value).

About the INDEXCOL Conversion Function

The INDEXCOL function enables you to build a derived logical column.

Selecting INDEXCOL automatically generates the following function template:

IndexCol( <<integer literal>>, <<expr1>> [, <<expr2>>, ?-] )

Note:

You can also use a session variable, an arithmetic expression, or a CASE WHEN statement, when an evaluation is possible without reference to back-end data, as the argument integer literal.

See INDEXCOL in the Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition .

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.

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.

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. The Info button is displayed when a logical table source maps to a column that does not appear in the list. Click Info to see details for the reason the physical objects were not 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.

In the Oracle BI 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.
Select the physical table to replace.
Select the physical table to use as a replacement for the original table, and then, click Next.
Select the logical table sources to change the physical table mapping.
(Optional) Select Show Qualified Names to see the full context for each source.
Click Next after you have selected logical table sources.
In Replace Wizard - Select Columns, specify the 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.
When you have finished mapping columns between the selected physical tables, click Next.
(Optional) In online mode, click Next to check out the necessary objects.
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 , select Generate Custom Names or Externalize Descriptions and select 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.

The Rename Wizard provides a way to transform physical names to user-friendly names.

Renaming objects in the Business Model and Mapping layer rather than the Presentation layer is a best practice for maintainability. Using friendly names for logical objects rather than presentation objects ensures reuse in multiple subject areas and ensures that the names persist even when you need to delete and re-create subject areas to incorporate changes to your business model.

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 is set to false.
If you select Presentation Column, then only presentation columns without the Use Logical Column Name property are renamed.

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 to apply the rules.

For example, 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)

In the Administration Tool, select Tools, then select Utilities. Then, select Rename Wizard and click Execute.
In Select Objects, from the Presentation or Business Model and Mapping layer that contains the objects, select an object and click Add.
Click Add Hierarchy to add all objects associated with the selected object, and then click Next
In Select Types, choose the object types you want to rename such as Subject Area, Logical Table, or Logical Column, and then click Next.
In Select Rules screen, choose the renaming rules and click Add.
Select Change specified text to rename particular words or phrases, and click Next.
In online mode, click Next to check out the necessary objects.
Click Finish to rename the objects.

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 includes conditional expressions associated with the columns. You can save the documentation 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 and load the file into Excel and RDBMS. You can query the resulting file to answer questions such as "If I delete physical column X, what logical columns are affected?" or "How many places in the business model refer to the physical table W_SRVREQ_F?" You can establish dependency relationships among elements in the repository.

Excel only allows data sets of 1,000,000 rows. You might exceed the row limitation in a large repository. 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. You can import the file into a repository as a Physical layer. 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.

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. By generating a metadata dictionary, users can gain an understanding of the repository and its objects.

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. Your users can view the XML documents in the Oracle BI Presentation Services user interface, or in a browser.

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

You must host the metadata dictionary files on a Web server such as Oracle HTTP Server or Apache HTTP Server. When you generate the dictionary, you can set the output 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.

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

You cannot store the dictionary in a directory with multi-byte characters. If you receive a system error about creating the necessary directories for the dictionary, then you must choose another directory.

When choosing a destination for your dictionary:

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

The location where users can view the metadata dictionary files is dependent on the host name and port number of your Web server, along with the directory location where you store the files.

See Providing Access to Metadata Dictionary Information.

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.
Click OK.
Copy the files over to your Web server and ensure they are accessible.
Edit the instanceconfig.xml configuration file to enable the metadata dictionary in the Oracle BI Presentation Services user interface, as well as grant the appropriate privilege to your users, groups, or application roles.

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.

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. 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:

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

Using the Calculation Wizard

You can use the Calculation Wizard to create new calculation columns that compare two existing columns, and also to create metrics in bulk.

It has a built-in mechanism to handle divide-by-zero and null cases, as well as other difficult situations. The Calculation Wizard provides an automated way to calculate the sales by quarter, the percentage of revenue, minimum and maximum values, and so on.

In the Calculation Wizard, the New Calculation page select from the following options:

Change (CurrentX - ComparisonX)

Subtract the value of the comparison column from the source column.

For example, for the Change calculation, you can choose whether to return NULL or some other value when the comparison column is NULL.
Percent Change (100.0 * (CurrentX - ComparisonX) / ComparisonX)

Subtract the value of the comparison column from the source column and express as a percentage.
Index (1.0 * CurrentX / ComparisonX)

Divide the source column by the comparison column.
Percent (100.0 * (CurrentX / ComparisonX))

Divide the source column by the comparison column and express as a percentage.

In the Business Model and Mapping layer, right-click any logical fact or dimension column of data type numeric measure column, and then select Calculation Wizard.
Click Next
In the Select Columns, choose the columns that you want to compare with the source column.
ClickRemove to take items out of the selected list.
Click Next.
In the New Calculations screen, choose the calculations to perform for specific columns.
Click Next when you have finished creating calculations.
In online mode, check out the objects to change.
Click Next to check out the necessary objects.

The Finish screen displays a summary of the calculations that are created.
Click Back or select a particular step from the navigation panel to make changes.
Click Finish. The new calculation columns are created.

Associating S_NQ_ACCT Record with the BI Query Log

Your Oracle BI Administrator can associate the records in the Oracle BI Usage Tracking table with the Oracle BI Server query log to help you troubleshoot Logical SQL query issues or to find queries related to a specific subject matter area.

The Oracle BI Server calculates a hash code from the text of the Logical SQL query and the text of the physical SQL queries. The physical SQL hash code, of any SQL queries executed from the Oracle BI Server, is recorded in the ACTION column in V$SQL. Your administrator can join the ACTION column with the PHYSICAL_HASH_ID column in the S_NQ_DB_ACCT table.

When usage tracking is enabled every Logical SQL request submitted to the Oracle BI Server is recorded in the S_NQ_ACCT table. See Setting Up Direct Insertion to Collect Information for Usage Tracking in the System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

Set the ENABLE_HASH_CODE_IN_SQL_COMMENTS parameter to YES in the NQSConfig.ini file to create an unique Hash_ID with each Logical SQL comment associated with a Logical SQL query.

You should use DISABLE_HASH_CODE after resolving the query issue.

You can associate the physical SQL hash code that is recorded in the Oracle BI Server query.log with the same hash code value that is stored in the ACTION column of the V$SQL performance view in the Oracle Database.

Your Oracle BI Server administrator can associate the physical SQL queries in the V$SQL view by doing the following:

Getting the Oracle BI EE physical query hash code from the ACTION column of the V$SQL view.
Querying the Oracle BI EE physical query usage tracking table, S_NQ_DB_ACCT, filtering on the PHYSICAL_HASH_ID column using the hash code value obtained from the ACTION column of the V$SQL view.
Querying the Oracle BI EE logical query usage tracking table, S_NQ_ACCT, joining the S_NQ_ACCT.ID column with the LOGICAL_QUERY_ID column from the S_NQ_DB_ACCT table.

You can obtain various properties of the corresponding BI logical request from the columns in the S_NQ_ACCT table including the SUBJECT_AREA_NAME column.

The relevant columns for associating the logical request record from S_NQ_ACCT table with the BI query log and the BI catalog are:

QUERY_TEXT represents the text of the logical SQL query, truncated to 4000 bytes. For the complete text of the SQL query, use the QUERY_BLOB columns or in the BI query log file.

For example:
```
select product.productid, product.qtysold, supplier.companyname, supplier.qtysold, (1.0 * product.qtysold) / supplier.qtysold from SnowflakeSales
```
HASH_ID represents the hash code of the Logical SQL query. You can use this identifier to search the query log for all occurrences of the same query.

For example:

a3a04491 as the HASH_ID value
ID represents a unique identifier for the logical request. You can join the ID column with the LOGICAL_QUERY_ID column in the S_NQ_DB_ACCT table to get the physical SQL query details.