15 Completing Oracle BI Repository Setup

This chapter explains how to perform final Oracle BI repository setup tasks like configuring for Oracle Scorecard and Strategy management, saving the repository and checking consistency, testing the repository, and uploading the repository using Fusion Middleware Control.

This chapter contains the following topics:

Configuring the Repository for Oracle Scorecard and Strategy Management

If your organization licensed Oracle Scorecard and Strategy Management and if you have the appropriate privileges, then you can use this functionality as part of a default installation with no additional configuration. Some features, however, such as comments and status overrides, require repository configuration in order to work.

Configuring the Repository for Comments and Status Overrides

Oracle Scorecard and Strategy Management provides the capability to add comments (that is, annotations) or to override the status that is associated with specific dimension values for KPIs, Objectives, and Initiatives. KPI Watchlists offer the capability to add comments or to override statuses for KPIs. To enable these features, you must configure the repository to include a database object for storing the comment and status override information.

The database that you installed for use with Oracle Business Intelligence contains the Business Intelligence Platform schema, which includes required Oracle Scorecard and Strategy Management schema tables. For more information about installing a database for Oracle Business Intelligence and running the Repository Creation Assistant (RCU) to create the required schemas, see Oracle Fusion Middleware Installation Guide for Oracle Business Intelligence.

To configure the Oracle BI repository for comments and status overrides:

  1. In the Administration Tool, open the repository in online mode.

    Online mode is strongly recommended for performing data access security tasks, such as the task described in Step 12 of this procedure.

  2. In the Physical layer, right-click and select New Database. The Database dialog is displayed.

  3. For Name, enter BSC.

  4. For Database, select the type of database that you have installed for use with Oracle Business Intelligence (typically Oracle 11g).

  5. Select the Connection Pool tab and click the Add button. The Connection Pool dialog is displayed.

  6. For Name, enter BSC.

  7. Select the Call interface appropriate for the database (for example, OCI 10g/11g for Oracle Database).

  8. For Data source name, provide the information that is appropriate for the database that you have installed and configured for use with Oracle Business Intelligence. For example, for Oracle Database, enter a connection string similar to the following:


    When connecting to an Oracle Database data source, you can include the entire connect string, or you can use the net service name defined in the tnsnames.ora file. If you choose to enter only the net service name, then you must set up a tnsnames.ora file in the following location within the Oracle Business Intelligence environment, so that the Oracle BI Server can locate the entry:

  9. Select Shared logon and enter values for User name and Password. In this step, you provide the user/schema name and password that you created when you used the Repository Creation Utility (RCU) to populate the Business Intelligence Platform schema in the Oracle Business Intelligence database.

    Ensure that the user that you provide has read/write privileges for the ANNOTATIONS and ASSESSMENT_OVERRIDES tables in the Business Intelligence Platform schema.

  10. Click OK in the Connection Pool dialog.

  11. Click OK in the Database dialog.

  12. Use the Identity Manager in the Administration Tool to allow the BISystem application role to execute direct database requests by default for the BSC database object. See "Allowing or Disallowing Direct Database Requests" for more information.

  13. Save and close the repository.

  14. Restart the Oracle BI Server.

Saving the Repository and Checking Consistency

In offline editing, remember to save your repository from time to time. You can save a repository in offline mode even though the business models may be inconsistent.

To determine if business models are consistent, use the Check Consistency command to check for compilation errors. You can check for errors in the whole repository by choosing File > Check Global Consistency, or in a particular logical business model by selecting a business model and then selecting Check Consistency from the right-click menu.

The consistency check analyzes the repository for certain kinds of errors and inconsistencies. For example, the consistency check finds any logical tables that do not have logical sources configured or any logical columns that are not mapped to physical sources, checks for undefined logical join conditions, determines whether any physical tables referenced in a business model are not joined to the other tables referenced in the business model, and checks for existence of a subject area for each business model.


Passing a consistency check does not guarantee that a business model is constructed correctly, but it does rule out many common problems.

When you check for consistency, any errors or warnings that occur are displayed in a dialog. Correct any errors and check for consistency again, repeating this process until there are no more errors. An error message indicates a problem that must be corrected. A warning message identifies a possible problem. Refer to "Checking the Consistency of a Repository or a Business Model" for more information.

After upgrading from a previous software version and checking the consistency of your repository, you might observe messages that you had not received in previous consistency checks. This typically indicates inconsistencies that had been undetected before the upgrade, not new errors.

Using nqcmd to Test and Refine the Repository

When your repository is complete, you can run sample queries against it to test that it is created properly. Correct any problems you find and test again, repeating this process until you are satisfied with the results.

You can use the Oracle BI Server utility nqcmd to run test queries against the repository. The utility connects using an Oracle BI Server ODBC DSN. The Oracle BI Server must be running to use nqcmd.

The nqcmd utility is available on both Windows and UNIX systems.

This utility is intended for sanity testing. For heavier load testing, use Answers or another client. Queries with many thousands of rows will not work with nqcmd.

Although you can use nqcmd to run queries against other ODBC data sources, this section only describes how to use this utility to query the Oracle BI Server.

Before running nqcmd, you must first run bi-init to launch a command prompt or shell window that is initialized to your Oracle instance. This utility is called bi-init.sh on Linux and UNIX systems, bi-init.bat on client installations of the Administration Tool, and bi-init.cmd for all other Windows installation types.

For example, on Windows:

  1. In Windows Explorer, go to the location appropriate for your install type:

    • Client installations:

    • All other installations:

  2. Double-click bi-init.cmd (or bi-init.bat for client installations) to display a command prompt that is initialized to your Oracle instance.

  3. At the command prompt, type nqcmd with the desired options. For example:

    nqcmd -dmy_dsn -umy_username [-pmy_password] -ssql_input_file -omy_result_file

You can pass a text file with SQL statements to the utility (script mode), or you can enter SQL at the command line (interactive mode). Queries are run against the default subject area, unless the object names used in the query are fully qualified.

Table 15-1 lists the command-line arguments for nqcmd.

Table 15-1 Command-Line Arguments for nqcmd

Argument Description


Lists the available command-line arguments.


The ODBC data source name for the Oracle BI Server to which you want to connect.

If you omit this parameter, you are prompted at the command line to enter the DSN.

Tip: On Windows, you can see the available local ODBC data source names by going to Control Panel > Administrative Tools > Data Sources (ODBC). Click the System DSN tab to see a list of the available DSNs (for example, AnalyticsWeb_coreapplication).


A valid Oracle Business Intelligence user name.


The corresponding Oracle Business Intelligence user password.

The password argument is optional. If you do not provide a password argument, you are prompted to enter a password when you run the command. To minimize the risk of security breaches, Oracle recommends that you do not provide a password argument 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.


The name and path of a text file that includes your test SQL queries.


The name and path of a file to which the utility will write the query results. This option is only used with -s.


The delimiter used in the SQL input file (for example, semicolon (;) or colon (:)). This option is only used with -s.


Enables asynchronous processing.

This option is typically used with -s, when you are passing a SQL input file with multiple SQL statements.


Enables UTF8 output instead of ACP in the output result file.

You might need to include this option to display international characters in query results.


Enables UTF16 instead of ACP for communication between nqcmd and the Oracle BI ODBC driver.

You might need to include this option to display international characters in query results.


Disables the ODBC forward only cursor.

Including this argument overrides the setting specified in the ODBC DSN.


Displays the version of the nqcmd utility.

-SessionVar session_variable_name=session_variable_value

Includes the specified session variable and sets it to the specified value.

Although -C, -R, -f, -H, -q, and -NoFetch are listed by the utility as available arguments, these options are not typically used.

If you run nqcmd in interactive mode rather than script mode (or in other words, if you do not pass a SQL input file), nqcmd shows a menu of options after you provide the data source name and user credentials. Although many options are shown, you typically only use Q, T, and C against the Oracle BI Server.

Enter Q to type a query at the command line. You must enter the query on a single line, and you cannot use a semicolon as a delimiter. Pressing Enter sends the SQL to the Oracle BI Server.

Enter T to browse presentation tables, or C to browse presentation columns. The utility prompts you for catalog pattern, user pattern, table pattern, and table type pattern before returning results.

For catalog pattern, enter the subject area that contains the tables you want to see. For table pattern, enter the specific table. You can enter percent (%) to see all subject areas or all tables, use % with other characters to replace a set of characters, or use underscore (_) with other characters to replace a single character.

User pattern and table type pattern are not used in queries against the Oracle BI Server, so enter % for these options.

You can also enter D to view a static list of data types supported by the Oracle BI Server.

Making the Repository Available for Queries

After you build a repository and it is consistent, you need to upload the repository using Fusion Middleware Control so that all Oracle BI Server instances can access it. Uploading the repository allows the Oracle BI Server to load the repository into memory upon startup and makes the repository available for queries.

See "Configuring Repositories" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for information about how to perform this task.

When the repository is uploaded and you can connect to it, run sample queries against it to test that it is created properly. Correct any problems you find and test again, repeating this process until you are satisfied with the results.


You must upload an Oracle BI repository in RPD format. You cannot upload a repository composed of MDS XML documents.

Creating Data Source Connections to the Oracle BI Server for Client Applications

If you want to enable end user client applications to connect to the new repository, you must define an ODBC data source connection to the Oracle BI Server for each application. Note that Oracle BI Presentation Services has the same relationship to the Oracle BI Server as any other client application.

See "Integrating Other Clients with Oracle Business Intelligence" in Oracle Fusion Middleware Integrator's Guide for Oracle Business Intelligence Enterprise Edition for information about creating ODBC data source connections for the Oracle BI Server.

Publishing to the User Community

After testing is complete, notify the user community that the data sources are available for querying. Presentation Services users only need to know the URL to type in their browser. Client/server users (for example, users accessing the Oracle BI Server with a query tool or report writer client application) need to know the subject area names, the computer on which the server is running, and their user IDs and passwords. They also need to have the ODBC DSN for the Oracle BI Server installed on their computers, and they may need to know the logical names of repositories if multiple repositories are used and the data source name (DSN) being created does not point to the default repository.