14 Completing Oracle BI Repository Setup

After you have created the repository file, the Physical layer, Business Model and Mapping layer, and Presentation layer, you need to perform several tasks to complete the initial repository setup. These tasks include saving the repository and checking consistency, adding an entry in NQSConfig.INI, and creating data source connections to the Oracle BI Server for client applications. This chapter provides information about these final setup tasks.

This chapter contains the following topics:

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.

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.

Testing and Refining 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 utilities nqcmd and NQClient to run test queries against the repository. Both utilities connect using an Oracle BI Server ODBC DSN. The Oracle BI Server must be running to use these utilities.

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

These utilities are intended for sanity testing. For heavier load testing, use Answers or another client. Queries with many thousands of rows will not work with these clients.

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

This section contains the following topics:

Using nqcmd to Run Sample Queries

Before running nqcmd, you must first run bi-init.cmd (or bi-init.sh on UNIX) to launch a command prompt or shell window that is initialized to your Oracle instance. You can find this utility in:

ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup

Then, run nqcmd from the resulting shell window 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 14-1 lists the command-line arguments for nqcmd.

Table 14-1 Command-line Arguments for nqcmd

Argument Description

-?

Lists the available command-line arguments.

-ddata_source_name

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

-uuser_name

A valid Oracle Business Intelligence user name.

-ppassword

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.

-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 to which the utility will write 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 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.


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.

Using NQClient to Run Sample Queries

To run NQClient, choose Start > Programs > Oracle Business Intelligence > BI ODBC Client.

After you run the utility, select File, then select Open DataBase to connect to the Oracle BI Server. Enter values as follows:

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

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

  • UID: A valid Oracle Business Intelligence user name.

  • Password: The corresponding Oracle Business Intelligence user password.

  • Database: The subject area to which you want to direct your queries (optional). If you do not specify this parameter, queries are run against the default subject area defined in either the DSN or in the repository.

Table 14-2 lists the additional options in NQClient.

Table 14-2 NQClient Options

Option Description

Utility menu: View Schema

Opens the DataBase Schema window to enable you to browse Presentation layer objects and attributes.

Select a presentation table from DataBase Tables, then select a presentation column from Columns in the Current Table. Available attributes and values for the selected column are displayed in the Attributes of the Current field.

Utility menu: SQL Query List

This option is not used.

Utility menu: Enter SQL Query

Opens the SQL Statement Editor. Enter a SQL query and click Execute. Results appear in the SQL Query Result grid.

File menu: Set Time-out

Lets you enter a query timeout value to specify a timeout period for long-running queries. Enter the number of seconds and click OK.

You must set the timeout value before connecting to 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 "Publishing the Default Metadata Repository" 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.

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.