Oracle BI and DWH

One will definitely come across requirement where he/she needs to merge two repository.

There will happens when we need to merger two entirely different repository or when we want to move incremental changes to production.

OBIEE does support 2 online repository ( However you can have 2 repository online but by doing so one will lose the oracle’s product support). In this case merging two repositories into one and making it online is the solution!

Let me discuss that aspect first.

We will two standard repositories  paint.rpd with UsageTracking.rpd so that we can get the usage information in paint repository.

Steps  1  Background

You can mergr two repository in one ,when one is online. However I would not suggest you to do that.

Make sure while merging you are working offline in Administrator tool.

Merging originally designed to work with three repositories. As we have just two repositories we will create a dummy repository.

Go to Administration — > File — > New and create a new repository call dummy.rpd

Creating Dummy.rpd

( Save this to this default location <Home>\ OracleBI\server\Repository however this is not necessary)

Now close this repository with doing anything. Just save when prompted.

Step 2 Open the paint.rpd in offline

Open pain.rpd

paint.rpd

Step 3 Merger paint.rpd with dummy.rpd

Go to File — > Merge and select dummy.rpd

open dummy.rpd for Merge

You will see the screen as below:

Merge Screen

step 4 Click select for the Modified Repository and select UsageTracking.rpd

Selecct usage tracking for merging

In decision you can either select Modified or Current. As we are merging 2 different repository it ok. select merge. You will see an error message called : Delete Failed. Just ignore that message.

And you will see both the repository merged as below.

Merged Repository Paint and UsageTracking

How to enable usage tracking in OBIEE ?
How to perfrom usage audit in OBIEE ?
How to set you Direct Insertion to Collect Information for Usage Tracking ?

Introduction

Oracle BI server supports collection of usage statistics which can use in a variety of ways such as who is running what and when ? database optimization, aggregation strategies, figuring out most used reports, billing departments as per their usage etc etc.

Goal

We want to achieve a reporting for usage in OBIEE. One such sample report is attached. Lots of analysis similar to this can be achieved using the Usage Tacking.

Usage Tracking Report

How it works?

When we enable usage tracking, statistics for every query are inserted into a database table or are written to a usage tracking log file. If you use direct insertion, the Oracle BI Server directly inserts the usage tracking data into a relational database table.

There are two ways to achieve Administration of usage tracking.

• Setting Up Direct Insertion to Collect Information for Usage Tracking
• Setting Up a Log File to Collect Information for Usage Tracking

Oracle Recommendation: Use direct insertion to write statistics to a database table.And I am going to discuss that method here.

Steps to Set Up Direct Insertion to Collect Information for Usage Tracking:

Set 1 : Create a necessary tables to collect usage tracking information.

A. Click here to download SAACCT.Oracle.sql or

Go to \\OracleBI\server\Schema and run the script  SAACCT.Oracle.sql to create table S_NQ_ACCT. This table will hold all the usage tracking information.

B. Go to \\OracleBI\server\Sample\usagetracking

This folder has necessary time series table creation script  and build in repository for usage tracking.

Create a tables using scripts:

Oracle_create_nQ_Clock.sql and Oracle_create_nQ_Calendar.sql

Load data in above tale using  Oracle_nQ_Clock.sql and Oracle_nQ_Calendar.sql script.

Step 2 Import  S_NQ_ACCT into the repository.

Please note: in order for Usage tacking to work properly , one must have to include this table in physical layer.

Step 3  Creating a business and presentation layer.

We will use a repository provided at C:\OracleBI\server\Sample\usagetracking\UsageTracking.rpd So that we want have to create a repositoy from ground zero.

Click  UsageTracking.rpd to down load the usage tracking repository.

Step 4 Change configuration file.

This is most important step in setting up usage tracking.

Go to \\OracleBI\server\Config and open NQSConfig.INI.

We have to make the change in [ USAGE_TRACKING ] portion of the config file.

Change DIRECT_INSERT                  = YES;
Along with this we have change the connection properties as well.
–PHYSICAL_TABLE_NAME
–CONNECTION_POOL

Change below parameter for better insertion performance and insertion control

–BUFFER_SIZE
–BUFFER_TIME_LIMIT_SECONDS
–NUM_INSERT_THREADS
–MAX_INSERTS_PER_TRANSACTION

Please refer below screen shot for more detail.
Note : Any typo will hold server from starting.

NQSConfig DIRECT_INSERT

Step 5 Check results to see every things is working.

Copy repository UsageTracking.rpd to OracleBI\server\Repository\
make Star = UsageTracking.rpd, DEFAULT; in NQSconfig file.

Restart BI server. If server fails to start check possible typo in NQSConfig

Check the Answers to see various analysis available for Usage Tracking.

Once should be able to see the reporting as shown below.

Usage Tracking Report