Monday, January 7, 2013

OBIEE 11g Usage Tracking and 10g to 11g Upgrade

1. Create a User or Schema on Database in my case i have created a user called BI
Easy way in SQL Developer right click on Other Users and Create User (Left Hand Toolbar Connections..)

2. Create Tables and a View from Oracle Scripts Directory ()

I Use sql Developer free from Oracle

Scripts are available from Directory:
- Compile - SAACCT.Oracle

- SQLServer_create_nQ_UserGroup - Use This Script for Oracle Database
create view NQ_LOGIN_GROUP as
select distinct USER_NAME as LOGIN, USER_NAME as RESP
from S_NQ_ACCT;

Scripts are available from Directory:

Compile - Oracle_create_nQ_Calendar
Compile - Oracle_create_nQ_Clock

Load - Oracle_nQ_Calendar - Add More Values if needed as only upto Year 2016
Load - Oracle_nQ_Clock

All the Database Objects are now created and two of them loaded with Data ...
Move onto some config
4. Ammend NQSCONFIG.ini ( File Location has changed from 10g to Below)

Edit With Notepad--
Ammend USAGE Tracking Section in NQSCONFIG.ini Like So I have shown only sections relevent to
# Parameters used for inserting data into a table (i.e. DIRECT_INSERT = YES).
PHYSICAL_TABLE_NAME = "OBI Usage Tracking"."Catalog"."dbo"."S_NQ_ACCT";
CONNECTION_POOL = "OBI Usage Tracking"."Usage Tracking Writer Connection Pool" ;
5. Lets move on to Merging the Usage Tracking RPD into our RPD.

Sample RPD \ Catalogue can be found in location :

Open your RPD mine is called Sha with Administrator just like in OBIEE 10g . Tools located within
Windows Install
My RPD from 10g has been Upgraded to 11g using the UA.bat (Upgrade Assistant Tool)

If you cant connect to your Datasource After Converting your RPD - Oracle Database 11g
You have to make sure your TNS Names is in the directory below and use - Connection Type OCI 10g/11g{Oracle_BI1}\network\admin directory

Alternatively use this in Datasource - (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL =TCP)(HOST = = 1521))) (CONNECT_DATA =(SID = orcl)))

The UsageTracking Rpd Will need converting to 11g as Oracle have packed the 10g Rpd...

NOTE !! . Before Conversion of Usage Tracking rpd make sure it has a password as by default its blank .
Open with OBIEE 10g Admin
and insert a password......!!. Otherwise RPD upgrade will fail. Also Make Sure features revert to default.

We have one already converted to OBIEE 11g and Ready to go on our website , Use
this to merge to your OBIEE 11g Repository.

To Do This goto D:\MIDDLEWAREHOME\Oracle_BI1\bin
Start UA.bat

And this Starts the Upgrade Assistant - Upgrade Assistant can be used to Upgrade RPD's and Web

Click on Upgrade BI RPD and Presentation Catalogue >> Next

- Click Upgrade (RPD)
- Fill in Administrator Username & Password - Password for Usage stats is blank so leave it blank
- Fill in a New Password that will be given to upgraded RPD - Confirm password
Click Next >>

- Fill in Port Number and Host for Weblogic Server
- Username for Weblogic server & Password.
click Next >>

You should get Succeeded if not read log file as highlighted in blue...
Click Next >>

Hit Upgrade ... Woohoo Away go...
This will copy the RPD to the default repository location and load the repository into OEM...( Horrible....)

Oh no its failed .. Thats ok it has still converted and loaded it. We can check why it failed when we open the
RPD as we can now open it . More than likely failed due to Connection Pool Information. If its succesfull then cool.

If you dont want to Upgrade the Usage Stats RPD then download one already upgraded with password
weblogic1 Your Upgraded RPD Can be found here ...


As You can see it has numbers on the end after upgrade that because
on upgrade the RPD Gets loaded into THE oem aswell.. Neat if you just want to upgrade
and load. We will tidy up later...

Take a copy and place into a merge folder .. Just create a folder in a location and copy the usagestats rpd
and your other rpd mine is called sha.rpd.

Ok Lets Merge Sha_B10003 and UsageTracking_BI0004 . Your rpd names and numbers might be different but same concept.

Create a Dummy RPD - Open Administrator - Creat Dummy, Import Metadata = No

Once done Click Finish

Again copy it back from here .
C:\Middleware\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repositor y

Now My Directory Looks Like This I got three rpd's

want to Merge my two rpd's into Dummy , Just like a two merge in OBIEE 10g.
- Open Administrator with your primary rpd , Mine is Sha_B10003
- Merge Type Full Repository Merge

Select Dummy as Original and UsageStats as Modified
Click Next >>

Select Decision = Current..
Thats it Both Sha_BI0003 and Usage Stats are Merged.. I have 1 Repository .. called sha_B10003(1)

And a Merge log to make sure everything was merged correctly.

Next We have to ammend the Connection Pool details for Usage Statistics so that the data is pulled from
the schema we created earlier on.

I have renamed my merged rpd just to tidy it up as sha.rpd as this is the rpd i am going to take forward to
upload into OBIEE 11g

Open your rpd with administrator

Ammend the Physical Connection pool settings for OBI Usage Stats so that it can connect to your Schema
where the tables are
it needs to produce results.

I changed my database to Oracle 11g click ok

Ammend both Connection pools to the Schema for Usage Tracking we created earlier. Also set permissions
for Writer connection pool to read and write.

Connection Pool & Usage Tracking Writer Connection Pool
Then ammended connection pool to connect to my Usage Tracking tables schema we created earlier.
Just do an Update Row Count to make sure you can connect ok ..

I have noticed that OBIEE 11g is pretty bugged up... So just keep trying with creating connection pools..
I had to copy the connection pool over from my Pre-Merged UsageTracking.rpd and then it started
working.. Weird but hey.

Thats it the RPD is working and merged .

Upload your rpd into then OEM (Oracle Enterprise Manager Console)

Within the Business Intelligence Core Application Load rpd
Click on Lock and Edit Configuration to upload new repository.

Select RPD and Hit Apply Then Activate Changes
It will ask you to restart server so that the new rpd is firmly in place. Click on Overview

Click Restart to Restart the Server.

Once Restarted your new rpd will be in the default rpd location all merged and ready to go . Also the
Subject Area for Usage Tracking will be available within Answers.
Upgrade the Catalogue for Usage Tracking.

Just like we upgraded the RPD for Usage Tracking we will use the same application the Upgrade Assistant.
Before we do this create a directory here D:\MIDDLEWAREHOME\instances\instance2\bifoundation\OracleBIPresentationServicesComponent\c

Call it deliveries

OBIEE Requires the Delivers directory to do the Catalogue Upgrade... If you have OBIEE 10g installed
then point to the deliveries folder within OracleBIData\web\catalog

Click Next >>

Follow all steps as Upgrading the RPD , The Usage Catalogue and RPD can be done together but i like to
do them seperatly so that im in control at each stage

As you might have noticed when the catalogue is upgraded then its made the default catalogue for OBIEE.
So copy the folders for Usage Tracking Reporting from the New Catalogue in


Copy the folder usage+tracking & the usage+tracking.atr file
Paste the Files into Your Shared folder for the Catalogue you use.

Then point OBIEE back to your Catalogue and not the Usage Tracking one.

Within the Business Intelligence Core Application Ammend the Presentation Service Repository Path.

Restart Servers ….

Thats it run the reports and watch the stats roll.... By the way you have to run some reports first to see the
reports producing results.