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:
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.
Note:See the following sections in Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition for more information about these features:
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:
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.
In the Physical layer, right-click and select New Database. The Database dialog is displayed.
For Name, enter BSC.
For Database, select the type of database that you have installed for use with Oracle Business Intelligence (typically Oracle 11g).
Select the Connection Pool tab and click the Add button. The Connection Pool dialog is displayed.
For Name, enter BSC.
Select the Call interface appropriate for the database (for example, OCI 10g/11g for Oracle Database).
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:
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.
Click OK in the Connection Pool dialog.
Click OK in the Database dialog.
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.
Save and close the repository.
Restart the Oracle BI Server.
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.
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. 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.
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 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
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.
For example, on Windows:
nqcmd from the following location:
nqcmd, type 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
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
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
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.
Use the upload repository command
uploadrpd to upload the repository to Oracle BI Server. Uploading the repository to Oracle BI Server allows BI Server to load the repository into memory upon startup and makes the repository available for queries.
Note:You can only upload the repository to a specific service instance.
Oracle provides the
uploadrpd commands for offline repository diagnostic and development purposes such as testing, only. Note that 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, data-model-cmd.sh on UNIX and data-model-cmd.cmd on Windows. You can find the launcher script at the following location:
See "What You Need to Know Before Using the Command" for more information.
uploadrpd command takes the following parameters:
uploadrpd -I <RPDname> [-W <RPDpwd>] -SI <service_instance> -U <cred_username> [-P <cred_password>] [-S <hostname>] [-N <port_number>] [-SSL] [-H]
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 will be prompted for the password when the command is run. For security purposes, 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 will be prompted for the password when the command is run. For security purposes, 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 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 the command.
data-model-cmd.sh uploadrpd -I repository.rpd -SI bi -U weblogic -S server1.example.com -N 7777 -SSL
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.
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.
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.