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.