Wednesday, July 13, 2011

OBIEE11g Setting up Usage Tracking

---

Update: Paul McGarrick provides you with an update RPD: http://total-bi.com/2011/09/obiee-11g-usage-tracking-rpd/

---

Before you are going to send me an email “didn’t you do this here: http://obiee101.blogspot.com/2008/08/obiee-setting-up-usage-tracking.html ?”

Yes, I did, I just wanted to do an 11g variant, after getting this screen:

image

If you look around in the BIPLATFORM repository created by the RCU you will see that the S_NQ tables already have been created for you:

image

What is missing are the S_ETL tables. You can find the scripts in <<OBIEE_HOME>>\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample\usagetracking\SQL_ServerTime

image

Add the tables to your BIPLATFORM repository:

image

Open your RPD and set up a new database and connection pool:

image

Import the usage tracking tables:

image

For the S_NQ_ACCT table switch off the cache:

image

Create an alias for each table and put them in a separate physical display folder called Usage Tracking:

image

Set up your physical joins:

"A1_S_ETL_DAY"."DAY_DT" ="A1_S_NQ_ACCT"."START_DT"

and

"A1_S_ETL_TIME_DAY"."HOUR_MIN" = "A1_S_NQ_ACCT"."START_HOUR_MIN"

image

! Deploy your RPD to the BISERVER , do not yet restart !

Edit your NQSConfig.ini (found in <<OBIEE_HOME>>\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1)

image

Restart the bi server:

image

Run some reports to check the tracking:

image

Check in your repository if the data gets loaded:

image

Next you can make a BM and presentation Layer:

image

And create a nice dashboard…..

 

Till Next Time

10 comments:

Anonymous said...
This comment has been removed by a blog administrator.
Inspector Clouseau said...
This comment has been removed by a blog administrator.
Mitra said...

Hello John,
I'm Padma an OBIEE conusltant from Bangalore,India. We are facing some issues with usages tracking- some of the queries are not getting recorded in the S_NQ_ACCT table, and the log says query length exceeded QUERY_TEXT coln length. We tried to add alter this VARCHAR column set the length to 4000 (originally 1000), but still some queries exceed this length. So the usage tracking is not effective at all with some quries not being logged into the S_NQ_ACCT. Any info to resolve this would be of great help. May be we are missing some settings?

Mitra said...

Yeah, forgot to add, we are using 11.1.1.5
Padma, Bangalore

John Minkjan said...

@Mitra,

Did you alter the size also in the RPD or only in the database/

Regards

John

Mitra said...
This comment has been removed by the author.
Mitra said...

Yes we modified it in DB and Rpd, but the query_text is exceeding 4000 for long queries. Hence its not inserting in SQ_NQ_ACCT. Server logs has ds error- nQSError: 17001] Oracle Error code: 1704, message: ORA-01704: string literal too long
at OCI call OCIStmtExecute: INSERT INTO S_NQ_ACCT........ According to Docs this issue comes when long text is being inserted into the QUERY_BLOB column, and was fixed in 11.1.1.5 . I'm afriad it's not. Just wondering if we could get into those DML scripts/Triggers to edit them to fix this issue. Any clues/ workarounds?

Lucas Rehem de Azevedo said...

Good Post! I had made one similar based on yours!

Anonymous said...

Hi,

We are using obiee 10.1.3.4.1 and am trying to customize Usage Tracking, I want to add Presentation Column Name in one of the tables(S_NQ_ACCT) - can I add, if so what would be the column name and process for customization. Please let me know.

Thanks!!

Anonymous said...

Please apply 11.1.1.5 Bp2 or Bp3 to resolve this issue.