To create the S_ETL_DAY time series table, at the SQL prompt, enter start <InstallDrive>:\oraclebi\server\Sample\usagetracking\SQL_Server_Time\Oracle_create_nQ_Calendar.sql; and press Enter.

The table is created. Notice that an error is issued because there is a DROP TABLE statement in the script to drop any existing S_ETL_DAY table in the schema.

To load the S_ETL_DAY table with calendar data, enter start <InstallDrive>:\oraclebi\server\Sample\usagetracking\SQL_Server_Time\Oracle_nQ_Calendar.sql;.and press Enter.

It takes several minutes for the calendar information to load into the table. You can reduce the periods covered to meet your own requirements by editing the DML script before running it.

To create the S_ETL_TIME_DAY time series table, at the SQL prompt, enter start <InstallDrive>:\oraclebi\server\Sample\usagetracking\SQL_Server_Time\Oracle_create_nQ_Clock.sql; and press Enter.

.

To load the S_ETL_TIME_DAY table with time data, enter start <InstallDrive>:\oraclebi\server\Sample\usagetracking\SQL_Server_Time\Oracle_nQ_Clock.sql;.and press Enter.

Using Windows Explorer, open <InstallDrive>:\oraclebi\server\Sample\usagetracking\SQL_Server_Time\SQLServer_create_nQ_UserGroup.sql to edit it. This file creates a user group view, and includes a column named "login," which is a reserved word in the Oracle database. Change the name to USER_NAME. Also delete Option 1 from the script.

Save and close the file.

Using SQL*Plus, enter start <InstallDrive>:\oraclebi\server\Sample\usagetracking\SQL_Server_Time\SQLServer_create_nQ_UserGroup.sql; and press Enter to create the view.

Close SQL*Plus.

In the Services dialog box, select the Oracle BI Presentation Server and select Action > Stop to stop the service. Also, stop the Oracle BI Server. These services can be stopped in any order.

Select Start > All Programs > Oracle Business Intelligence > Administration.

Select File > Open > Offline and select sh_usagetracking.rpd in <InstallDrive>:\OracleBI\server\Repository to open the repository in offline mode. Enter Administrator as the username and password, and click OK.

In the Physical layer of the repository, expand OBI Usage Tracking > Catalog > dbo and verify that the usage tracking table and time series tables are present in the physical catalog.

Right-click the Usage Tracking Writer Connection Pool and select Properties. This connection pool is used to perform writes to the usage tracking table, and should be kept separate. In the Connection Pool dialog box, notice that the data source name and shared logon are configured by default to use variables. Click Cancel to close the dialog box.

You can set up the OLTP_DSN and OLTP_USER variables to point to the correct database values or you can directly change the writer and reader connection pools so that they are hard-coded.

For the Usage Tracking Writer Connection Pool, change the Data Source Name to your TNS name. Change the user name and password to SH, then click OK. To confirm the password, enter SH in the Confirm Password dialog box and click OK.

Right-click the Connection Pool and select Properties. Change it to point to your TNS name and use the SH user name and password. Click OK and confirm the password in the Confirm Password dialog box.

Click File > Save to save the repository and decline the global consistency check. Click File > Close to close the Administration Tool.

Note: NQSConfig.ini is the initialization file read by the Business Intelligence Server when it starts up. It contains a number of parameters that control server settings and behavior. In this step, you have set up the parameter that informs the server to read the sh_usage.rpd metadata repository.

Next, set the usage tracking options. Scroll to the Usage Tracking section. Set Enable = Yes; to enable usage tracking. Notice that, when you scroll past the parameters for writing data to a flat file, the configuration file is set by default to DIRECT_INSERT = YES, which determines that usage tracking information is stored in the S_NQ_ACCT database table, which is the recommended approach.

If the DIRECT_INSERT parameter is set to NO, usage tracking information is stored in a file.

In the section that lists parameters used for inserting data into a table, set the PHYSICAL_TABLE_NAME = "OBI Usage Tracking"."Catalog"."dbo"."S_NQ_ACCT" ; .

The table name is the fully qualified name as it appears in the Physical layer of the Server Administration Tool. The general structure of this parameter depends on the type of database being used. For Oracle, the structure is PHYSICAL_TABLE_NAME = "<Database>"."<Schema>"."<Table>".

Set the CONNECTION_POOL parameter to specify the connection pool to use for inserting records into the usage tracking table. The setting is CONNECTION_POOL = “OBI Usage Tracking”.“Usage Tracking Writer Connection Pool”. Leave the default settings for BUFFER_SIZE, BUFFER_TIME_LIMIT_SECONDS, NUM_INSERT_THREADS, and MAX_INSERTS_PER_TRANSACTION. Your configuration file should look like this:

The connection pool name is the fully qualified name as it appears in the Physical layer of the repository.

Save and close the NQSConfig.ini configuration file.

Using Windows Explorer, navigate to the SetupFiles directory and copy the sh folder and its contents to <InstallDrive>:\OracleBIData\web\catalog. This is a backup of the Presentation Catalog corresponding to the Sales History content, merged with the Usage Tracking content from the sample Usage Tracking catalog.

Open <InstallDrive>:\OracleBIData\web\config\instanceconfig.xml in Notepad and edit the CatalogPath section to point to the directory you just copied (as shown in the screen below). The path should be : <InstallDrive>:/OracleBIData/web/catalog/sh.

Click Start > All Programs > Administrative Tools > Services. In the Services dialog box, select the Oracle BI Server and click Action > Start. Also start the Oracle BI Presentation Server service.

Click Start > All Programs > Oracle Business Intelligence > Presentation Services, enter Administrator as the user ID and password, and click Log In.

Click Answers.

Verify that the Usage Tracking subject area is visible. If not, click Reload Server Metadata.

Select the SH subject area and create the following query:

Customers.Country Region, Measures.Sales Facts.Amount Sold.

This query represents the sum of the sales revenue at the Region level in the Customer dimensional hierarchy.

Press and hold the Ctrl key and click Times > Calendar > Calendar Year. The Create/Edit Filter dialog box opens.

Create the filter Calendar Year is equal to / is in 2001.

.

Click OK to close the Create/Edit Filter dialog box. The filter is added to the query.

Click Results.

Click the Create a New Request icon in the upper-right portion and select the Usage Tracking subject area.

Create the following query:

Users.User Name, Query Time.Date, Topic.Logical SQL, Measures.Max Row Count, and Measures.Total Time in Secs.

Click Results and verify that you get the expected usage tracking results.

Click the Answers link and, in the selection pane, expand the Shared Folders > Usage Tracking > Usage Monitoring Reports folders to view and explore the prebuilt usage tracking requests that come with the sample Usage Tracking Presentation Catalog.

Click the Usage Tracking dashboard link to view and explore the prebuilt Interactive Dashboard, which lists the packaged requests you just explored in the presentation catalog.