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

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.

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.

Data Masking for Demonstrations

Dashboards / Answers demonstrations happen frequently. Data privacy is important, so masking data is a common request. Run demonstrations for any audience and mask data using:

• Presentation variables and
• Answers functions to manipulate request columns (strings and numbers)

Brief steps listed here:

1. Create a dashboard / page prompt with a Presentation Variable. Set masking on by default for privacy reasons...

MASK, or @{MASK}{'Mask'}

From DR. OBI

2. Answers request columns respond to the presentation variable value, example below:

CASE '@{MASK}' = 'Mask'
THEN FUNCTION MASK(FOLDER.COLUMN)
ELSE FOLDER.COLUMN
END

3. Mask data in any way you imagine, still a good demonstration ensures even masked data makes some sense. For example:

• Numbers - easily masked by introducing variation

• CONSTANT + - / * "Sales Facts"."Amount Sold"
• PERCENT * "Sales Facts"."Amount Sold"

• Strings - masked by replacing characters (simple and more complex examples)

REPEAT('x', 5)||RIGHT(Customers."Cust Last Name", 5)

REPEAT('x', (LENGTH(Customers."Cust Last Name")/2))||RIGHT(Customers."Cust Last Name",(LENGTH(Customers."Cust Last Name")-LENGTH(Customers."Cust Last Name")/2))

4. Add the functions into a CASE statement, example:

CASE WHEN '@{MASK}' = 'Mask' THEN (5 * "Sales Facts"."Amount Sold")
ELSE "Sales Facts"."Amount Sold"
END

5. Add sophistication to your data masking capabilities by ensuring the 'Mask' prompt is available only to 'Power Users' on the dashboard page. 

From DR. OBI

Complex string replacement routines in Answers can increase processing time, so keep this in mind. This is a simple alternative to masking routines at the data source and can get your demonstrations up and running quickly.

Manipulating Fixed String Formats

As well as Saved Requests (in the Webcat Path), OBIEE 'Usage Tracking' creates fixed string formats for dashboard paths.

Create a nicely formatted dashboard name, using Answers you manipulate the string by combining SUBSTRING and POSITION functions:

SUBSTRING(Topic.Dashboard FROM (POSITION('_portal/' IN Topic.Dashboard)+8 ))

POSITION function finds the start of the specified string, so here the length of the string is added to give the correct startpoint for the SUBSTRING function.

From DR. OBI

Combining the functions this way works for shared dashboards only, so filter out individual user dashboards or one way to handle the situation is shown here:

CASE
WHEN POSITION('/users/' IN Topic.Dashboard) = 1 THEN 'My Dashboard'
ELSE SUBSTRING(Topic.Dashboard FROM (POSITION('_portal/' IN Topic.Dashboard)+8 ))
END

Of course, with more sophisticated string manipulation you can isolate the username for each users' dashboard.

Use Custom CSS Styles

Look and feel (OBIEE Skins and Styles) is difficult to change for many users. Changes to CSS classes often need a release.

Work on custom CSS styles as an Answers user. The 'Use Custom CSS Style' option processes multiple style arguments separated by a semi-colon.

From DR. OBI

Once you're happy with your custom CSS styles...

background-color:#ED0000; font-size:8pt; color:#FFFFFF

you can group them into a CSS class

.DR_OBI
{
background-color:#ED0000;
font-size:8pt;
color:#FFFFFF
}

Along with the other styles (classes) you've created get them all to be added to custom.css for the next release.

Request Design for Infrequent Users...

Infrequent users will not be so familiar with request content. Adding hints, such as subtitle (shown here), greatly improves the user experience.

From DR. OBI

As your request design includes more interactivity, consider hints / notes for users.
 

Careful Request Formatting

The 'View Selector' is useful, but make sure there are enough visual cues between different views so users relate information easily. Be consistent with:

• View names
• Column names between views
• Column sequence (a consideration for a chart / table view switch)
• Number formats
• Colour cues and...
• Expected format for each view

From DR. OBI

Switching views can feel incongruous to the user, especially when the selected view doesn't meet expectations.

Requests with Round Objects...

If the business requirement persists for a pie or even a gauge then Answers has both of these options.
Pie charts best highlight contributors to a whole, so %-ages are often the best accompanying values. Always showing values forces the pie smaller, think about showing values 'On Mouseover' only or increasing the space (if you have it) occupied by the chart to ensure easy viewing.

From DR. OBI

Stephen Few writes extensively about human desire for all things round, so its not repeated here.

Request Design

Good design will make for compelling requests. Space is always limited, so remove redundant information. For example, a chart with accompanying table of the same data.

From DR. OBI

A good use of the 'View Selector' is to switch chart / table data in one place. It gives greater design flexibility.

Saving Answers Requests

It is good practice to provide further detail in the 'Description' section when saving the request. The OBIEE catalog will grow over time; so using the 'Catalog Search' facility in Answers you'll always find your request quickly and efficiently.

From DR. OBI

Description section provides plenty of space for detailed content write-up, but keywords tend to be more relevant. Briefing books, dashboard prompts and saved filters will not appear in catalog search results in OBIEE 10.x versions.

Available Content

The blog deals with leading practices in the use of Oracle Business Intelligence (OBIEE). Topics focus on the challenges faced in implementing front end components:

• Answers
• Dashboards
• Delivers and
  
• BI Publisher...

If implementation differs because of the datasource (M-OLAP v. R-OLAP), then they are addressed.

You will not find installation steps here, or a discussion of new features except to highlight if it is possible in a specific version.

For this information, many accomplished professionals in the industry deal with these topics through consulting and blogs. Here are some I've had the pleasure of meeting or working with:

• Mark, Jon, Venkat, Adrian et al. down at Rittman Mead
• Dave Atherton and the team at Praesto
  
• Christian Berg, who writes independently on the delicate OBIEE / Essbase combination
• John Minkjan, Ciber consultant, also writing independently on OBIEE and...
• Kurt Wolff, an OBIEE practitioner of long standing writing for KPI Partners
  

And for leading practices in structured training and adoption for OBIEE, Peak Indicators do very good work.

The list is not exhaustive, but remember, in most cases free advice is worth what you pay for it.

Follow DR. OBI

Look for notifications about latest tips on Twitter.

Follow @DR_OBI, hashtag #drobi.

About DR. OBI

This is the DR. OBI Blog.