✍ Kashif M
Trends

Saturday, 12 October 2013


Usage Tracking in OBIEE 11G


By on October 12, 2013




Usage Tracking which is used to monitor the query performance and the users or group of users who was seen the dashboards or reports.

Analyzing usage details by users, groups and queries requested by the user.

The detail report of log in time and log out time, time of period used by the user in a dashboard, and query analyses.

Useful information of user’s information for billing process by enabling usage tracking.

Step by step Implementation of usage tracking

Create a schema for usage tracking (It’s not mandatory, you can use the already existed schema also).

Creating a new schema using sqlplus.

Open a command prompt, login as sys.

For example, Create username as “usertrack” and password as “usertrack”.

Grant permissions to the user.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Execute the SAACCT.Oracle.sql from the following location

<OBI 11g Installed Drive>\ instances \instance2 \bifoundation\ OracleBIServerComponent\ coreapplication_obis1\schema\ SAACCT.Oracle.sql

Open the SAACCT.oracle.sql in notepad and copy the sql query and run the query in oracle sql developer.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G





Check whether the table were created or not, by exploring the command “DESC S_NQ_ACCT;”

You can see the table with list of columns.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G





Go to control panel to create a ODBC connection between BI admin tool and database for newly created schema “usertrack”.

Start à control panelà Administrative tools à Microsoft ODBC Administrator
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



ODBC Data Source Administrator Dialogue box will open to add the DSN connectivity.

Go to system DSN tab to create an ODBC Connectivity.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Click on add button to add the tracking connectivity between database and admin.

A dialogue box will open to give credentials of the schema.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



A list of drivers name will get display select the appropriate driver for the data source, here we are going to select “Oracle in oraclient11g_home1”.

According to the database installed in system the drivers may differ.

As per the dialogue box appear when click on “Oracle in oraclient11g_home1”.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Give configuration credentials to make the connection.

Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G





Data Source Name: usertrack (user defined)

Description: usertrack (user defined)

TNS Service Name: vijai (user defined According to the tns file)

User ID: usertrack

Click on test connection, to test the connection made correct or not.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G





After click on the test connection, give the password credentials for the schema we created.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Once the connection successful click ok.

Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G





You can see the “usertrack” will be added in the system DSN list.

Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G









Open the BI Admin Tool in online/offline mode,
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Click on file and choose the option Import à from Database
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G





Select Data source which we are going to import, choosing here as “OCI 11g/10g”
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Change the connection type to OCI 11g/10 by drop down in the connection type.

Give the credentials,

TNS Name: vijai

User Name: usertrack

Password: usertrack
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Click ok.

An import of the table dialogue box will open, select the appropriate table and import and give the credentials for connection pool.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Select the tracking table under that check the tables…

Click import

Give the connection pool name and the schema name and password.

Importing the data from data view to repository view.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Connection Pool Name: tracking

Call Interface: OCI11g/10g

Data Source Name: vijai

User name: usertrack

Password: usertrack

Click ok.



Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



After importing check in physical layer, whether the table is imported or not.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Create an alias for that table with the name of Alias_S_NQ_ACCT(User Defined)

Right click on the table à select New Objectà select à Alias
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



A dialogue box will open and give the username for the alias(Alias_S_NQ_ACCT).
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Make a Join for the original table and alias table.

Key Joining columns are user_name

Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Drag and Drop the tracking folder to BMM layer and also Presentation Layer.

Check the consistency and save.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Go to the OBIEE 11 Installed folder

<installed folder> \OBI11g \instances \instance2 \config \OracleBIServerComponent\coreapplication_obis1 \NQSConfig.INI

Edit the NQSConfig.INI file, by opening in notepad.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G





Go to the section of Usage tracking, Change the option from no to yes.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G





Change the Enable option from no to yes.

In parameters, change the physical table according to the Change made in Admin tool.

Change the connection pools also.

Change the credentials to,

Physical_table_name = “vijai”.”USERTRACK”.”S_NQ_ACCT”;

Connection_Pool = “vijai”.”UserTrack”;

Click save and close.

Then restart the services.

Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G





Go to sql developer and make a new connection with the user “usertrack”

Connection Name: usertrack

Username: usertrack

Password: usertrack

Hostname: local host

Port: 1521

SID: orcl

Click test connection and connect.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Check the Tables imported or not in the database
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Check the data in database for the table.

Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Go to presentation side, login and view the dashboards and reports then check the database.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



View the reports, the time and user will affect in the database.

We will get the information which user logged in and seeing what reports.
Usage Tracking in OBIEE 11G
Usage Tracking in OBIEE 11G



Kashif
mkashu: Usage Tracking in OBIEE 11G
Review : Kashif | Kashif
Update: October 12, 2013 | Rating: 4.5

Comment for "Usage Tracking in OBIEE 11G"

1 comments: