Monday, October 13, 2014

Implementation of Usage Tracking in OBIEE 11G



Step 1:Create S_NQ_ACCT, S_ETL_DAY,S_ETL_TIME_DAY in bipauth schema of database.

Create table script for S_NQ_ACCT will be available in BI_DEVPLATFORM schema.

Create table script and Data for S_ETL_DAY,S_ETL_TIME_DAY will be available in below path:


C:\Oracle11g_MiddlewareHome\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample\usagetracking\SQL_Server_Time

@Oracle_create_nQ_Calendar.sql
@Oracle_create_nQ_Clock.sql
@Oracle_nQ_Calendar.sql
@Oracle_nQ_Clock.sql

 Create the following view  in bipauth schema of database.

create view NQ_LOGIN_GROUP as
select distinct USER_NAME as LOGIN, USER_NAME as RESP
from S_NQ_ACCT;

Step 2:Take a copy of the usage tracking RPD from the below path and paste into a temporary directory   C:\TEMP

C:\Oracle11g_MiddlewareHome\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample\usagetracking





Step 3:Upgrade the Usage Tracking rpd which is in Temporary directory by following the below steps:

Open the command prompt and run the below steps.


set ORACLE_INSTANCE=C:\Oracle11g_MiddlewareHome\instances\instance1

cd C:\Oracle11g_MiddlewareHome\Oracle_BI1\bifoundation\server\bin

obieerpdmigrateutil.exe -I C:\TEMP\UsageTracking.rpd -O c:\temp\NewUsageTracking.rpd -L c:\temp\ldif -U weblogic


When asked for the encryption password, it means the new repository password which is required by 11g.Just give it a simple password as Admin123.






Step 4:Now  we can observe New Usage Tracking  rpd created in below path.
                       C:\TEMP



Step 5:We now need to add extra fields to the S_NQ_ACCT table in the physical layer in New Usage Tracking  rpd.

Expand the S_NQ_ACCT table in the physical layer and check that all of the following fields exist with the correct data type. 

Rename RUNAS_USER_NAME to IMPERSONATOR_USER_NAME and then add  ID, QUERY_BLOB and QUERY_KEY columns.








Finally change the data type for all of the DOUBLE fields below from INT to DOUBLE. It is important that the Nullable field is correct on every field.




Field Name              Data Type     Length     Nullable?
CACHE_IND_FLG           CHAR            1           No
COMPILE_TIME_SEC        DOUBLE                      Yes
CUM_DB_TIME_SEC         DOUBLE                      Yes
CUM_NUM_DB_ROW          DOUBLE                      Yes
END_DT                  DATETIME                    Yes
END_HOUR_MIN            CHAR            5           Yes
END_TS                  DATETIME                    Yes
ERROR_TEXT              VARCHAR         250         Yes
ID                      VARCHAR         50          No
IMPERSONATOR_USER_NAME  VARCHAR         128         Yes
NODE_ID                 VARCHAR         15          Yes
NUM_CACHE_HITS          DOUBLE                      Yes
NUM_CACHE_INSERTED      DOUBLE                      Yes
NUM_DB_QUERY            DOUBLE                      Yes
PRESENTATION_NAME       VARCHAR         128         Yes
QUERY_BLOB              LONGVARCHAR     4000        Yes
QUERY_KEY               VARCHAR         128         Yes
QUERY_SRC_CD            VARCHAR         30          Yes
QUERY_TEXT              VARCHAR         1024        Yes
REPOSITORY_NAME         VARCHAR         128         Yes
ROW_COUNT               DOUBLE                      Yes
SAW_DASHBOARD           VARCHAR         150         Yes
SAW_DASHBOARD_PG        VARCHAR         150         Yes
SAW_SRC_PATH            VARCHAR         250         Yes
START_DT                DATETIME                    Yes
START_HOUR_MIN          CHAR            5           Yes
START_TS                DATETIME                    Yes
SUBJECT_AREA_NAME       VARCHAR         128         Yes
SUCCESS_FLG             DOUBLE                      Yes
TOTAL_TIME_SEC          DOUBLE                      Yes
USER_NAME               VARCHAR         128           Yes


Step 6: Now right click on S_NQ_ACCT, select Physical Diagram -> Objects and Direct Joins.
The joins to S_ETL_TIME_DAY and S_ETL_DAY are fine, but the join to NQ_LOGIN_GROUP is broken – the line is red and there is no arrow. Click on it and delete it.




Create a new join from S_NQ_ACCT to NQ_LOGIN_GROUP and change the join condition to:"OBI Usage Tracking"."Catalog"."dbo"."NQ_LOGIN_GROUP"."LOGIN" = "OBI Usage Tracking"."Catalog"."dbo"."S_NQ_ACCT"."USER_NAME"





Then click ok to save and close the diagram.
Now right click on S_NQ_ACCT and select properties. If there is no Key defined, enter ID in Key name and select the ID field from the drop down in the Columns field. This defines the key on the table. Click ok.




Step 7: In the Physical layer right click on ‘OBI Usage Tracking’ database, select properties. In the General tab change the Database to Oracle 11g.




Step 8: Then click on the Features tab and click the ‘Reset to defaults’ button.





Step 9:Right click on the ‘Connection Pool’ connection pool, select Properties.


Give the DSN name as <database>.WORLD.

User Name: bipauth
Password:*********

Now edit the ‘Usage Tracking Writer Connection Pool’ connection pool and make the same changes as above.




Step 10:Check Consistency.


Step 11: Take a back up of DevRpd to merge this rpd with Usage Tracking rpd.

Step 12:create a blank repository, select File -> New Repository.  Enter a name (Test.rpd), a location (the temp directory), click the No option against import metadata then enter the password ********. Finally click Finish.




Step 13:Open the backup rpd of Dev in offline mode.
select File -> Merge

In the Merge Wizard screen select Merge Type ‘Full Repository Merge’

Then select the Blank RPD as the Original Master Repository and enter its password ********

Then select NewUsageTracking Repository as the Modified Repository and enter its password Admin123.

Leave the Save Merged Repository as the default name (usually the same name with (1) at the end) –  rename it later once the merge is complete. But  give the password for this rpd as *********.

Leave Equalize during merge unchecked. Click Next.




From drop down in the Decision column choose ‘Current’. Then click Finish.



Then  we can see the merged rpd and check the consistency.



Step 14:Update the NQSConfig.INI file, for  locate Usage Tracking section and update following highlighted information.


[USAGE_TRACKING]

ENABLE = YES; 


#  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"; 
BUFFER_SIZE = 250 MB; 
BUFFER_TIME_LIMIT_SECONDS = 5; 
NUM_INSERT_THREADS = 5; 
MAX_INSERTS_PER_TRANSACTION = 1; 

Step 15:Make the merged rpd as online and Restart the services.
Step 16:Now copy and paste UsageTracking.catalog on the desktop
Step 17: Log into Analytics and go to Shared Folders in Catalog.
Step 18:  Click on Shared Folder and then click on Unarchive option  which is in the left bottom corner.
Step 19:  Then new window will open to unarchive the catalog. Select Usage Tracking.webcatalog in browse and Select ACL as  'Inherit' and then click on 'OK'.




Step 20: Now Usage Tracking dashboard will appear under Dashboards.