OBI EE Usage Tracking - Part 2

In Part 1 we identified when users last accessed OBI EE. With a quarterly license review date, assume active users are 'Days Since Last Access <= 90'. Using the request from part 1 let's take it a little further:

1. Active Today (they can do license spot checks):

• Active Today: same as 'Days Since Last Access' from Part 1

            TIMESTAMPDIFF(SQL_TSI_DAY, MAX("Query Time"."Date"), Current_TimeStamp)

2. Active at Quarterly License Review Dates*:

• 31/12/2010

            TIMESTAMPDIFF(SQL_TSI_DAY, MAX("Query Time"."Date"),(EVALUATE('TO_DATE(%1,%2)' as DATE,'2010/12/31','yyyy/mm/dd')))

• 31/03/2011

             TIMESTAMPDIFF(SQL_TSI_DAY, MAX("Query Time"."Date"),(EVALUATE('TO_DATE(%1,%2)' as DATE,'2011/03/31','yyyy/mm/dd')))

• 30/06/2011 

            TIMESTAMPDIFF(SQL_TSI_DAY, MAX("Query Time"."Date"),(EVALUATE('TO_DATE(%1,%2)' as DATE,'2011/06/30','yyyy/mm/dd')))

• 30/09/2011 

            TIMESTAMPDIFF(SQL_TSI_DAY, MAX("Query Time"."Date"),(EVALUATE('TO_DATE(%1,%2)' as DATE,'2010/12/31','yyyy/mm/dd')))

3. Add the new columns to the request used in Part 1. Add conditional formatting making it more user-friendly, maybe shorten column headers to suitable values (as below).

From DR. OBI

If licensing is based on CPU cores in your implementation, these queries can be used to understand Usage recency and Dashboard popularity.

*Fixed dates used here for demo purposes, but a column identifying license review dates might be added to S_ETL_DAY. EVALUATE is used to remove ambiguity when passing values to the TO_DATE function.