---
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:
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:
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
Add the tables to your BIPLATFORM repository:
Open your RPD and set up a new database and connection pool:
Import the usage tracking tables:
For the S_NQ_ACCT table switch off the cache:
Create an alias for each table and put them in a separate physical display folder called Usage Tracking:
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"
! Deploy your RPD to the BISERVER , do not yet restart !
Edit your NQSConfig.ini (found in <<OBIEE_HOME>>\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1)
Restart the bi server:
Run some reports to check the tracking:
Check in your repository if the data gets loaded:
Next you can make a BM and presentation Layer:
And create a nice dashboard…..
Till Next Time
10 comments:
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?
Yeah, forgot to add, we are using 11.1.1.5
Padma, Bangalore
@Mitra,
Did you alter the size also in the RPD or only in the database/
Regards
John
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?
Good Post! I had made one similar based on yours!
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!!
Please apply 11.1.1.5 Bp2 or Bp3 to resolve this issue.
Post a Comment