Usage tracking repository and catalog setup
Finally I did usage tracking setup in OBIEE. There is a good doc/example in Oracle by example sitehttp://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/usage_tracking/usage_tracking.htm#t1 in John Minkjan's bloghttp://obiee101.blogspot.com/2008/08/obiee-setting-up-usage-tracking.html and Rittman Meadhttp://www.rittmanmead.com/2008/10/11/comparing-obiee-usage-tracking-with-nqsquerylog/. Also there are many sources of this information on other sites/bloges.
I will use my SH user to create usage tracking tables into it.
Use SAACCT.Oracle.sql to create table S_NQ_ACCT. Script can be found in ..OracleBI\server\SchemaScripts folder. After that jump into D:\OracleBI\server\Sample\usagetracking\SQL_Server_Time. There are some scripts that we need to start.
Decription of S_NQ_ACCT columns (description of usage tracking data) is available in Oracle Business Intelligence documentation Oracle Business Intelligence Server
Administration Guide in the table 42 on the page 406.
Run scripts:
Oracle_create_nQ_Calendar.sql (create S_ETL_DAY)
Oracle_create_nQ_Clock.sql (create S_ETL_TIME_DAY)
Oracle_nQ_Calendar.sql (insert into S_ETL_DAY)
Oracle_nQ_Clock.sql (insert into S_ETL_TIME_DAY)
We make these tables public:
GRANT SELECT ON S_ETL_DAY TO PUBLIC;
GRANT SELECT ON S_ETL_TIME_DAY TO PUBLIC;
Now we need to merge usagetracking.rpd into our current rpd online.
I copy/paste my current online rpd and save it as ofline. This repository I'll open at first and also I make a copy of it, and this copy is only for OBIEE to allow me to merge (this is required although they are the same).
We get the message that repositories are identical.
Now, we add usagetracking.rpd as modified repository.
We merge information from usagetracking.rpd to our tecaj.rpd.
After merging ignore delete failed message.
A new repository has been created.
Save new merged repository and owerwrite original tecaj.rpd.
We need to put some changes to OBI Usage Tracking database folder, connection pools in the physical layer and also in NQSConfig.ini in ..\OracleBI\server\Config.
I set my SH user to connection pools Usage Tracking Writer Connection Pool and Connection pool. The first is for writing and the second just for reading.
Don't forget to create vie NQ_LOGIN_GROUP:
create view NQ_LOGIN_GROUP as
select distinct USER_NAME as LOGIN, USER_NAME as RESP
from S_NQ_ACCT;
grant select on nq_login_group to public;
Changes in NQSConfig.ini:
Before merging usage tracking catalog (reports) into our current catalog online, make sure that you copy/paste your new merged repository into ..OracleBI\server\Repository folder and owerwrite the old one.
For merging usage tracking reports I'll use Content Accelerator Framework (CAF) clone option. I'll not explain procedure for installing Content Accelerator Framework (CAF), you can find it on thehttp://www.oracle.com/technology/products/bi/pdf/oraclebiee-cafv1-usage-instructions.pdf.
In ..OracleBI\server\Sample\usagetracking there is a UsageTracking.zip. If you unzip it you'll get usage tracking catalog.
Open usage tracking catalog ofline and merge it to our current online web catalog. You'll need to open two instances of catalog manager application.
Copy/paste this path to catalog:
Copy usage tracking reports and dashboards from shared folder:
Paste it to a web catalog online shared folder:
You'll se usage tracking in online web catalog shared folder. We did a usage tracking reports and dashboards migration in online web catalog.
Cloning reports with Content Accelerator Framework (CAF) V1
Now, open Content Accelerator Framework (CAF) V1 and try to clone these usage tracking reports just for test.
We use copy of online repository:
Clone all usage tracking reports:
The same repository file we are using for source and the target repository because we just want to copy/clone reports:
We choose usage tracking subject area and make required object mappings:
You can change the name of the target report, add yome prefix, etc. Cloned reports are in the cloned folder:
Finally I did usage tracking setup in OBIEE. There is a good doc/example in Oracle by example sitehttp://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/usage_tracking/usage_tracking.htm#t1 in John Minkjan's bloghttp://obiee101.blogspot.com/2008/08/obiee-setting-up-usage-tracking.html and Rittman Meadhttp://www.rittmanmead.com/2008/10/11/comparing-obiee-usage-tracking-with-nqsquerylog/. Also there are many sources of this information on other sites/bloges.
I will use my SH user to create usage tracking tables into it.
Use SAACCT.Oracle.sql to create table S_NQ_ACCT. Script can be found in ..OracleBI\server\SchemaScripts folder. After that jump into D:\OracleBI\server\Sample\usagetracking\SQL_Server_Time. There are some scripts that we need to start.
Decription of S_NQ_ACCT columns (description of usage tracking data) is available in Oracle Business Intelligence documentation Oracle Business Intelligence Server
Administration Guide in the table 42 on the page 406.
Run scripts:
Oracle_create_nQ_Calendar.sql (create S_ETL_DAY)
Oracle_create_nQ_Clock.sql (create S_ETL_TIME_DAY)
Oracle_nQ_Calendar.sql (insert into S_ETL_DAY)
Oracle_nQ_Clock.sql (insert into S_ETL_TIME_DAY)
We make these tables public:
GRANT SELECT ON S_ETL_DAY TO PUBLIC;
GRANT SELECT ON S_ETL_TIME_DAY TO PUBLIC;
Now we need to merge usagetracking.rpd into our current rpd online.
I copy/paste my current online rpd and save it as ofline. This repository I'll open at first and also I make a copy of it, and this copy is only for OBIEE to allow me to merge (this is required although they are the same).
We get the message that repositories are identical.
Now, we add usagetracking.rpd as modified repository.
We merge information from usagetracking.rpd to our tecaj.rpd.
After merging ignore delete failed message.
A new repository has been created.
Save new merged repository and owerwrite original tecaj.rpd.
We need to put some changes to OBI Usage Tracking database folder, connection pools in the physical layer and also in NQSConfig.ini in ..\OracleBI\server\Config.
I set my SH user to connection pools Usage Tracking Writer Connection Pool and Connection pool. The first is for writing and the second just for reading.
Don't forget to create vie NQ_LOGIN_GROUP:
create view NQ_LOGIN_GROUP as
select distinct USER_NAME as LOGIN, USER_NAME as RESP
from S_NQ_ACCT;
grant select on nq_login_group to public;
Changes in NQSConfig.ini:
Before merging usage tracking catalog (reports) into our current catalog online, make sure that you copy/paste your new merged repository into ..OracleBI\server\Repository folder and owerwrite the old one.
For merging usage tracking reports I'll use Content Accelerator Framework (CAF) clone option. I'll not explain procedure for installing Content Accelerator Framework (CAF), you can find it on thehttp://www.oracle.com/technology/products/bi/pdf/oraclebiee-cafv1-usage-instructions.pdf.
In ..OracleBI\server\Sample\usagetracking there is a UsageTracking.zip. If you unzip it you'll get usage tracking catalog.
Open usage tracking catalog ofline and merge it to our current online web catalog. You'll need to open two instances of catalog manager application.
Copy/paste this path to catalog:
Copy usage tracking reports and dashboards from shared folder:
Paste it to a web catalog online shared folder:
You'll se usage tracking in online web catalog shared folder. We did a usage tracking reports and dashboards migration in online web catalog.
Cloning reports with Content Accelerator Framework (CAF) V1
Now, open Content Accelerator Framework (CAF) V1 and try to clone these usage tracking reports just for test.
We use copy of online repository:
Clone all usage tracking reports:
The same repository file we are using for source and the target repository because we just want to copy/clone reports:
We choose usage tracking subject area and make required object mappings:
You can change the name of the target report, add yome prefix, etc. Cloned reports are in the cloned folder:
Posts
