Usage Tracking in OBIEE 12c

These steps are required to leverage Usage Tracking on OBIEE 12c in most simple case:

• Create physical layer and edit NQSConfig.INI;
• Create logical and presentation layers;
• Make some reports.

Configuring server instance 

First of all, we have to decide which table to choose for logging?

In this example we are going to use default S_NQ_ACCT which is already exist in <PREFIX>_BIPLATFORM database schema.

To find out the <PREFIX> go to: EM→ WebLogic Domain → JDBC Data Sources → biplatform_database → Connection Pool

In this case we have BI12DEV_BIPLATFORM database schema.

Note that for heavy usage it is better to recreate S_NQ_ACCT table without indexes and periodically load data from there to another schema which is made for analytics. Instead of recreating you may drop S_NQ_ACCT indexes in <PREFIX>_BIPLATFORM schema.

Then, we have to create Usage Tracking Database manually or by using Import metadata wizard.

Make a copy and edit configuration file $BI_DOMAIN/config/fmwconfig/biconfig/OBIS/NQSConfig.INI in similar way:

  1 +--346 lines: #########################################################################
347 #  server.
348 #
349 ###############################################################################
350 
351 [USAGE_TRACKING]
352 
353 ENABLE = YES;  # This Configuration setting is managed by Oracle Enterprise Manager Fus
354 
355 #==============================================================================
356 # Parameters used for writing data to a flat file (i.e. DIRECT_INSERT = NO).
357 #
358 # Note that the directory should be relative to the instance directory.
359 # In general, we prefer directo insert to flat files.  If you are working in
360 +-- 15 lines: a cluster, it is strongly recommended you use direct insert.  If there is
375 #  Parameters used for inserting data into a table (i.e. DIRECT_INSERT = YES).
376 #
377 #  Init-Block Tracking Options are commented out and and as a result disabled.
378 #  To enable Init-Block Tracking Feature, define the two parameters for
379 #  Init-Block, INIT_BLOCK_TABLE_NAME and INIT_BLOCK_CONNECTION_POOL.
380 #
381 PHYSICAL_TABLE_NAME = "Usage Tracking Database"."BI12DEV_BIPLATFORM"."S_NQ_ACCT";  # Or
382 CONNECTION_POOL = "Usage Tracking Database"."Usage Tracking Pool";  # This Configuratio
383 # INIT_BLOCK_TABLE_NAME        = "<Database>"."<Catalog>"."<Schema>"."<Table>" ;  # Or 
384 # INIT_BLOCK_CONNECTION_POOL   = "<Database>"."<Connection Pool>" ;
385 BUFFER_SIZE = 250 MB;
386 BUFFER_TIME_LIMIT_SECONDS = 5;
387 NUM_INSERT_THREADS = 5;
388 MAX_INSERTS_PER_TRANSACTION    = 5 ;
389 +--159 lines: JOBQUEUE_SIZE_PER_INSERT_THREADPOOL_THREAD  = 100; default is 100 while 0

Be sure to have DIRECT_INSERT = YES;

Restart your instance and now information on every analytics request would be saved in BI12DEV_BIPLATFORM.S_NQ_ACCT.

Creating repository levels

This step is mostly the same as described in Setting Up Usage Tracking in Oracle BI 11g but also suggests to create two hierarchies: one for Time and one for Topic.

Some Time columns:

Year =
  CAST (
    EXTRACT( YEAR  FROM "Usage Tracking Database".""."BI12DEV_BIPLATFORM"."S_NQ_ACCT_Dim"."START_DT")
    AS VARCHAR ( 4 )
  )
Month =
  MONTHNAME("Usage Tracking Database".""."BI12DEV_BIPLATFORM"."S_NQ_ACCT_Dim"."START_DT")
Week =
  'W' || CAST ( EXTRACT( WEEK_OF_YEAR  FROM "Usage Tracking Database".""."BI12DEV_BIPLATFORM"."S_NQ_ACCT"."START_DT") AS VARCHAR ( 2 ))
Day =
  DAYOFWEEK("Usage Tracking Database".""."BI12DEV_BIPLATFORM"."S_NQ_ACCT"."START_DT")
Hour =
  'H' || SUBSTRING("Usage Tracking Database".""."BI12DEV_BIPLATFORM"."S_NQ_ACCT"."START_HOUR_MIN" FROM 1 FOR 2)

Some Topic columns:

Saw Dashboard =
  IFNULL("Usage Tracking Database".""."BI12DEV_BIPLATFORM"."S_NQ_ACCT"."SAW_DASHBOARD",'N/A')
Saw Dashboard Name =
  IFNULL(
    SUBSTRING("Usage Tracking Database".""."BI12DEV_BIPLATFORM"."S_NQ_ACCT"."SAW_DASHBOARD"
    FROM
    LOCATE('/',"Usage Tracking Database".""."BI12DEV_BIPLATFORM"."S_NQ_ACCT"."SAW_DASHBOARD",-1)+1
    FOR
    LENGTH("Usage Tracking Database".""."BI12DEV_BIPLATFORM"."S_NQ_ACCT"."SAW_DASHBOARD"))
  ,'N/A')
Saw Src Name =
  IFNULL(
    SUBSTRING(
    "Usage Tracking Database".""."BI12DEV_BIPLATFORM"."S_NQ_ACCT_Dim"."SAW_SRC_PATH"
    FROM
    LOCATE('/',"Usage Tracking Database".""."BI12DEV_BIPLATFORM"."S_NQ_ACCT_Dim"."SAW_SRC_PATH",-1)+1
    FOR
    LENGTH("Usage Tracking Database".""."BI12DEV_BIPLATFORM"."S_NQ_ACCT_Dim"."SAW_SRC_PATH")
    )
  ,'N/A')

The description of each S_NQ_ACCT may be found in Description of the Usage Tracking Data from System Administrator's Guide. Also you may check out OBIEE Sample Application, which comes with completely preconfigured usage tracking (including publisher and webcat).

Making reports 

Start with some basic query count reports, treemaps, timelines. 
Try to figure out the reasons for most rarely used reports. One of the obvious – time consumption – you should check in place, other ones may require further investigation.

Happy tracking!