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 the upload repository command.

This chapter contains the following topics:

Configuring the Repository for Oracle Scorecard and Strategy Management

You can use the organization's licensed Oracle Scorecard and Strategy Management repository, if you have the appropriate privileges.

The Oracle Scorecard and Strategy Management repository is part of a default installation with no additional configuration changes are required. Some features such as comments and status overrides, require repository configuration in order to work.

Note:

See System Administrator's Guide for Oracle Business Intelligence Enterprise Edition :

Configuring the Repository for Comments and Status Overrides

Oracle Scorecard and Strategy Management provides the capability to add comments, also known as annotations, or to override the status associated with specific dimension values for key performance indicators (KPIs), objectives, and initiatives.

KPI Watchlists offer the capability to add comments or to override statuses for KPIs. You must configure the repository to include a database object for storing the comments and status override information.

The database that you installed for use with Oracle Business Intelligence contains the Business Intelligence Platform schema that includes the 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 Installing and Configuring Oracle Business Intelligence.

Note:

You should open the Oracle BI Administration Tool in Online mode to perform data access security tasks.

See Allowing or Disallowing Direct Database Requests.
  1. In the Oracle BI Administration Tool , open the repository in online mode.
  2. In the Physical layer, right-click and select New Database.
  3. In the Databases dialog, in Name, enter BSC.
  4. From the Database list, select the type of database to use with Oracle Business Intelligence such as Oracle 12c.
  5. Click the Connection Pool tab, and click Add .
  6. In the Connection Pool dialog, in Name, enter BSC.
  7. Select the Call interface appropriate for the database, for example, OCI 11g/12c 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:
    (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521))(CONNECT_
    DATA=(SERVER=DEDICATED)(SERVICE_NAME=KPIOracle)(SID=KPIOracl)))
    

    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:

    BI_DOMAIN/bidata/components/core/serviceinstances/ssi/oracledb

  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.

    Set up the user with 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.
  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 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.

Note:

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. See Checking the Consistency of a Repository or a Business Model.

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.

  • In the Oracle BI Administration Tool with a repository open, from the File menu, select Check Global Consistency.

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

Tip:

On Windows, you can see the available local ODBC data source names in Data Sources (ODBC) available in Administrative Tools. The System DSN tab displays a list of the available DSNs, for example, AnalyticsWeb_coreapplications.

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.

See nqcmd Command Line Arguments.

  1. On Windows launch nqcmd from the following location:

    BI_DOMAIN/bitools/bin

  2. At nqcmd, type the argument options to use, for example:
    nqcmd -dmy_dsn -umy_username [-pmy_password] -ssql_input_file -omy_result_file
    

nqcmd Command Line Arguments

Review the table to learn about the valid the command-line arguments for nqcmd.

If you run nqcmd in interactive mode rather than script mode, that is, 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 only use Q, T, and C against the Oracle BI Server.

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

Use 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, use % for these options.

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

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

Argument Description

-?

Lists the available command-line arguments.

-ddata_source_name

Specifies the ODBC data source name for the targetOracle BI Server.

If you omit this parameter, you are prompted at the command line to enter the data source name (DSN).

-uuser_name

Specifies a valid Oracle Business Intelligence user name.

-ppassword

Specifies 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. The password argument is supported for backward compatibility only. For scripting purposes, you can pass the password through standard input.

-ssql_input_file_name

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

-ooutput_result_file_name

The name and path of a file where the utility writes the query results. This option is only used with -s.

-Ddelimiter

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

-a

Enables asynchronous processing.

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

-z

Enables UTF8 output instead of ANSI Code Page (ACP) in the output result file.

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

-utf16

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.

-NotForwardCursor

Disables the ODBC forward only cursor.

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

-v

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.

Upload Repository Command

Use the uploadrpd command to upload the repository to Oracle BI Server.

Uploading the repository to Oracle BI Server allows BI Server to load the repository into memory on startup and makes the repository available for queries.

Note:

You can only upload the repository to a specific service instance.

Oracle provides the downloadrpd and uploadrpd commands for offline repository diagnostic and development purposes such as testing, only. In all other repository development and maintenance situations, you should use BAR to utilize BAR's repository upgrade and patching capabilities and benefits.

You can use this command to upload the Oracle BI repository in RPD format. You cannot use this command to upload a repository composed of MDS XML documents.

You execute the utility through a launcher script, datamodel.sh on UNIX and datamodel.cmd on Windows. If the domain is installed in default folder then the location of the launcher script looks like the following:

Oracle_Home/user_projects/domains/Domain_Name/bitools/bin/datamodel.sh or datamodel.cmd on Windows

If the client install doesn't have domain names, the launcher script location is as follows:

Oracle_Home\bi\bitools\bin\datamodel.sh or datamodel.cmd on Windows

See What You Need to Know Before Using the Command.

Syntax

The uploadrpd command takes the following parameters:

uploadrpd -I <RPD filename> [-W <RPD password>] [-D] [KG <groups>] [-RG <groups>] -U <cred_username> [-P <cred_password>][SI <service_instance>] [-S <host>] [-N <port>] [-SSL] [-H]

Where

I specifies the name of the repository that you want to upload.

W is the repository's password. If you do not supply the password, then you are prompted for the password when the command is run. Oracle recommends that you include a password in the command only if you are using automated scripting to run the command.

SI specifies the name of the service instance.

U specifies a valid user's name to be used for Oracle BI EE authentication.

P specifies the password corresponding to the user's name that you specified for U. If you do not supply the password, then you are prompted for the password when the command is run. Oracle recommends that you include a password in the command only if you are using automated scripting to run the command.

S specifies the Oracle BI EE host name. Only include this option when you are running the command from a client installation.

N specifies the Oracle BI EE port number. Only include this option when you are running the command from a client installation.

SSL specifies to use SSL to connect to the Oracle WebLogic Server to run the command. Only include this option when you are running the command from a client installation.

H displays the usage information and exits for the command. Use -H or run .sh without any parameters to display the help content.

-D removes all the existing customization groups on the server, for example:

datamodel.sh uploadrpd -I orders.rpd -SI ssi -U weblogic -D

You can select to keep some of the existing groups and other all of the other groups, using -D -KG, for example:

datamodel.sh uploadrpd -I orders.rpd -SI ssi -U weblogic -D -KG "group1, group2" The upload repository command keeps group1 and group2, and deletes any other existing groups.

You can delete some of the existing groups, but keep all of the other groups, using -D -RG, for example:

datamodel.sh uploadrpd -I orders.rpd -SI ssi -U weblogic -D -RG "group1, group2"

The upload repository command deletes group1 and group2, and keeps any other existing groups.

Example

datamodel.sh uploadrpd -I repository.rpd -SI bi -U weblogic -S server1.example.com -N 7777 -SSL

Making the Repository Available for Queries

Use the upload repository command to make the repository available for queries.

After you build a repository and it is consistent, you need to upload the repository using the Upload Repository Command 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.

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.

Note:

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:

The 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 Integrator's Guide for Oracle Business Intelligence Enterprise Edition to learn 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.