Sunday, February 24, 2013

How to Enable Usage Tracking on OBIEE 11g (11.1.1.6.5)

A usual requirement is to enable usage tracking to gather statistics about usage of OBIEE, things like performance, which Dashboards/Reports are being utilized, who is logging in, etc. Out of the box, Oracle includes a robust usage tracking capability with OBIEE, and in fact includes a sample RPD with the Usage Tracking Subject area during install.

There are three main components:

  • Changes/configuration in the RPD
  • Configuration in Enterprise Manager
  • SQL to run to setup Usage Tracking schema.

Continue on to read the steps to enable Usage Tracking.

RPD Changes

The sample RPD can be found at:

{OBIEE_11G_Instance}/bifoundation/OracleBIServerComponent/coreapplication_obis1/sample/usagetracking

You will need to copy the Physical, BMM, and Presentation components from the sample UsageTracking.rpd to your development/test/production RPD. Note that I had to make a few changes to the included RPD. If the UsageTracking subject area is not as OBIEE is expecting, then OBIEE will fail to update the table with usage details, and give an error message in the nqserver.log.
  • Open the RPD with the Admin Tool.
  • 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
  • Add the following missing fields (right click on S_NQ_ACCT, select New Object -> Physical Column…)
    • ID
      • Type: VARCHAR
      • Length: 50
      • Nullable: Unchecked
    • QUERY_BLOB
      • Type: LONGVARCHAR
      • Length: 4000
      • Nullable: Checked
    • QUERY_KEY
      • Type: VARCHAR
      • Length: 128
      • Nullable: Checked
  • Below is a list of all of the required fields, data type, length, and nullable flag:
Field NameData TypeLengthNullable?
CACHE_IND_FLGCHAR1No
COMPILE_TIME_SECDOUBLEYes
CUM_DB_TIME_SECDOUBLEYes
CUM_NUM_DB_ROWDOUBLEYes
END_DTDATETIMEYes
END_HOUR_MINCHAR5Yes
END_TSDATETIMEYes
ERROR_TEXTVARCHAR250Yes
IDVARCHAR50No
IMPERSONATOR_USER_NAMEVARCHAR128Yes
NODE_IDVARCHAR15Yes
NUM_CACHE_HITSDOUBLEYes
NUM_CACHE_INSERTEDDOUBLEYes
NUM_DB_QUERYDOUBLEYes
PRESENTATION_NAMEVARCHAR128Yes
QUERY_BLOBLONGVARCHAR4000Yes
QUERY_KEYVARCHAR128Yes
QUERY_SRC_CDVARCHAR30Yes
QUERY_TEXTVARCHAR1024Yes
REPOSITORY_NAMEVARCHAR128Yes
ROW_COUNTDOUBLEYes
SAW_DASHBOARDVARCHAR150Yes
SAW_DASHBOARD_PGVARCHAR150Yes
SAW_SRC_PATHVARCHAR250Yes
START_DTDATETIMEYes
START_HOUR_MINCHAR5Yes
START_TSDATETIMEYes
SUBJECT_AREA_NAMEVARCHAR128Yes
SUCCESS_FLGDOUBLEYes
TOTAL_TIME_SECDOUBLEYes
USER_NAMEVARCHAR128Yes
  • The join between S_NQ_ACCT and NQ_LOGIN_GROUP is broken. Select both tables, right click and choose Phyiscal Diagram -> Selcted Object(s) Only.
  • Select the red arrow, and click Delete to remove the broken join.

  • Now click on the new join icon

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

  • Click Ok.
  • Close the physical diagram window.
  • Now right click on the table S_NQ_ACCT and select properties.

  • Select the Keys tab.

  • If there is no key defined, enter ID in Key name and select the ID field from the drop down in the Columns field.

  • Click Ok.
  • Don't forget to setup proper Connection Pool information for both connection pools:
    • Usage Tracking Connection Pool
    • Usage Tracking Writer Connection Pool

Enterprise Manager Configuration

The following outlines the steps needed within Enterprise Manager to configure and start Usage Tracking.
  • Navigate to the Enterprise Manager (EM) of the host you are deploying to.

  • Login with the admin credentials (biadmin/oracle01)
  • Navigate to the Mbeans by expanding WebLogic Domain, selecting bifoundation_domain, and then from the drop down chooses System MBean Browser.

  • Lock the Mbeans by navigating to:
    • Application Defined MBeans->oracle.biee.admin->Domain: bifoundation_domain->BIDomain->BIDomain (the second one of the two)

  • Click on the Operations tab.
  • Click on lock.

  • Click on Invoke.

  • Click on Return.
  • Navigate to the Usage Tracking mbeans:
    • Application Defined MBeans->oracle.biee.admin->Domain: bifoundation_domain->BIDomain.BIInstance.ServerConfiguration->BIDomain.BIInstance.ServerConfiguration

  • Click on Attributes.
  • Set the following Attributes:
    • UsageTrackingConnectionPool = "Oracle Analytics Usage"."Usage Tracking Writer Connection Pool"
    • UsageTrackingEnabled = True
    • UsageTrackingPhysicalTableName = "Oracle Analytics Usage"."dbo"."S_NQ_ACCT"

  • Click on Apply.
  • Commit the MBeans by navigating to:
    • Application Defined MBeans->oracle.biee.admin->Domain: bifoundation_domain->BIDomain->BIDomain
  • Click on the Operations tab.
  • Click on commit (the first option).

  • Click on Invoke.

  • Click on Return.
  • Navigate back to the main EM page.
  • Under Business Intelligence, select coreapplicaiton.

  • Click on the Overview tab, if you are not already there.
  • Click on the Restart button.

  • When prompted to restart all components, click on Yes.

  • The process will take a few moments, but will let you know once it has completed.


Schema Setup

  • Using TOAD (or SQLPLUS) connect to the BIPLATFORM schema (DEV_BIPLATFORM, or whatever you called it during installation) on the database which you configured for this OBIEE 11g instance.
  • Run the four Oracle scripts to setup Usage Tracking (found in SQL_Server_Time folder, where the sample RPD is included, see top of post):
    • Oracle_create_nQ_Calendar.sql
    • Oracle_create_nQ_Clock.sql
    • Oracle_nQ_Calendar.sql
    • Oracle_nQ_Clock.sql
  • Remember to Commit!
At this point you should have everything wired up. If you log into OBIEE, and navigate a few dashboards, statistics should be gathered and recorded into the usage tracking tables within the BIPLATFORM schema.

6 comments:

  1. Which 11g is this ?? I have 11.1.1.5 and I dont see the settings for usage tracking in MBean Browser -> attributes

    ReplyDelete
    Replies
    1. This is on 11.1.1.6.5

      I'll see if I can dig up information on 11.1.1.5

      Delete
  2. Hi Ben,
    I prefer using the S_NQ_ACCT table from the XXX_BIPLATFORM user in the RCU database. This table is created when you create the RCU, with the S_NQ_DB_ACCT which contains the physical query.
    This way you avoid future errors in the NQServer.log, because sometimes OBIEE tries inserting the physical query too so it needs inserting in both tables.
    Regards
    Ana GH

    ReplyDelete
  3. This is an interesting post. I have been trying to enable usage tracking for a while now. Thanks for sharing Ben.

    ReplyDelete
  4. Hi Ben,
    Could you lend some knowledge as to why nothing would be writen to S_NQ_ACCT after set up is complete? I can query the table from the rpd and usage tracking in the front but regardless of the amount of queries I run, there are always 0 results returned. The connection pool I used is an odbc with a user that has write priviledges to the db (or so the db guy told me.) Is there any way to check the write process (or lack of)?
    Thanks much,
    Katya

    ReplyDelete
    Replies
    1. Hi Katya,
      The reason could be usage tracking didn't start. Look in the nqserver.log file (located under OBIEE_HOME\instances\instance1\diagnostics\logs\OracleBIServerComponent\coreapplication_obis1) to see if there are any error messages.
      Regards,
      Eugene

      Delete