DR. OBI: Usage

Showing posts with label Usage. Show all posts

Wednesday, December 15, 2010

OBI EE Usage Tracking - Part 4

In a later post we'll extend the request used in Parts 1-3, to highlight performance for individual users. Continuing the license review theme, let's clarify overall exposure.

Set this up from the beginning and a business can witness the growing number of licenses used.

1. A saved filter for the definition of 'Active Users' can be changed without affecting dependent requests, so using the Part 2 definition:

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

a filter will look similar to this...

From DR. OBI

2. So, we can count active users now at any time, but inform your Usage data a little more making the report more informative with:

Total Available Licenses
Remaining Available Licenses (Total Available - # Active Users)

3. Add these to your request and always know your license exposure, similar to the one here...

From DR. OBI

4. Alternate text is added for context using HTML markup for the column:

<HTML TITLE="# of Active Users"># of Named Users</HTML>

Add your total number of licenses using a repository variable, database table updated via writeback or other convenient method of your choosing.

Keen-eyed readers will see I'm using the SampleApp available from Oracle. Usage Tracking is available in the RPD supplied in this bundle, just follow the other steps to setup the required tables.

Wednesday, November 24, 2010

OBI EE Usage Tracking - Part 3

Part 2 explored active users at quarterly license reviews. Tightening up the request a bit more, are individual users normally active on an annualized basis? Is the average days between visits <=90?

1. Normally Active: 365/COUNT(DISTINCT "Query Time"."Date")

2. Again, add some conditional formatting:

<=90 - Green Tick
>90 - Red Cross

3. Thinking about your user activity, what you want to know when might be important. Column order improves readability of the information here...

Active Now
Normally Active
Active @ License Review Date

4. The request from Parts 1 and 2 could look a bit like this...

From DR. OBI

Tuesday, November 23, 2010

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.

Monday, November 22, 2010

OBI EE Usage Tracking - Part 1

This post kicks off a theme on Usage Tracking. The standard implementation falls short of providing true business support. There are many business (and IT) reasons for understanding usage:

License Management (Costs)
Performance Degradation (Improvement)
OBI EE (Dashboards) Popularity

Let's start with a simple question (normally motivated by license management)... when did a user last access OBI EE?

1. Access your "Usage Tracking" subject area and choose the following columns:

Users."User Name"
"Query Time".Date (x2)

2. Using the two "Query Time".Date columns edit each column formula:

Last Accessed: MAX("Query Time".Date)
Days Since Last Access:

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

3. Perhaps remove any irrelevant data from the date (e.g. 12:00:00AM) using the date short format, provide custom headings for columns with functions and possibly filter out system accounts (e.g. Administrator).

4. Load the request onto a Dashboard page with a page prompt and get something like this...

From DR. OBI

This theme will not deal with the implementation of Usage Tracking, this area is well documented by Oracle and John Minkjan has comprehensive instructions which are easy to follow. The implementation of Usage Tracking between 10.x and 11.x versions has changed little.