Monday, 30 December 2013

OBIEE 11g Release 11.1.1.7 – Usage Tracking in Different Schema Why? What are Benefits?


The usage tracking has been drastically improved with OBIEE 11g, it has audit on logical as well as physical queries. The usage table creation process is now part of RCU and implemented with DEV_BIPLATFORM schema. The old days of creating a schema, creating a usage tracking tables, indexes etc has gone now. So in short has came across a long for good, though there is a long wish list of nice to have type of features. I am looking forward to see that development would take into account all these additional wishes and will improve on usage tracking feature.

Recently I have come across a very different customer wish about usage tracking. Customer would like to manage usage tracking logical and physical queries into another schema. Why? What are benefits to have usage tracking in separate schema?  

This blog is an attempt to understand customer reasons to have a separate schema for usage tracking and steps required to do so if at all it is desirable.

Customer Request

Product Version - OBIEE: 11.1.1.7.0

Is it possible to transfer S_NQ_ACCT table to other schema than DEV_BIPLATFORM ?
If Yes then How?
If yes then is it supported?
Where from I can get NQACCT SQL Scripts and Time Dimension Pre-data Load Seeding

Opinion Matters
  
One of my Oracle Fellow has put forward his opinion on this, according to him he always suggest a separate schema for Usage Tracking to customer for following reasons.

  • It allows client to share usage tracking data with third party companies/users for analysis purposes

  • It allows customers to use different security of access for different users at the database level which is not desirable for DEV_BIPLATFORM

  • The usage tracking data is bound to increase exponentially, might be a good reason to store in separate schema

  • It allows moving usage data to different schema without moving BIPLATFORM schema which has BI System data

I believe every opinion matters, you may agree or not is all together a different matter.
  
Answers

Is it possible to transfer S_NQ_ACCT table to other schema than DEV_BIPLATFORM ?
Answer- Yes it is possible, but I would suggest not doing so.

If Yes then How?
Answer – Create Tables, Change the connection pool in repository for usage tracking, add usage tracking connection etc by Enterprise Manager

If yes then is it supported?
Answer – Usage tracking is additional functionality, I don’t think it is supported or not supported is a question. 

Where from I can get NQACCT SQL Scripts and Time Dimension Pre-data Load Seeding
Answer –I would suggest to login to DEV_PLATFORM and get both logical and physical usage tracking table script

I hope you find this information useful.

No comments:

Post a Comment