OBI EE runcat Usage Scenarios

If the only requirement is to transfer the FULL catalog from development to production, then a straight copy would be enough. Once requirements are more sophisiticated then 'runcat' gets it done efficiently.

Partial catalog change is the more likely situation with the catalog. A typical scenario:

1. Developers build requests and dashboards with final versions stored in the '/shared' subfolder, so for deployment purposes the '/users' subfolder on development might be ignored. Tag the <SOURCE> catalog, so newly created objects get a unique id.

./runcat.sh -cmd tag -appVersion "1" -patchSet "01" -offline <CATALOG_LOCATION>/<CATALOG_NAME> -skipFolder /users

• tag - gives each new object a unique id
• -appVersion - useful for tagging the new objects with the release number for which they're developed 
• -patchSet - useful for tagging objects created for the specific instance of the release, after a round of defect fixing for example. This is less important.
• -skipFolder - allows you to ignore any objects created under the '/users' subfolder.

Tagging is an important activity, without it 'runcat' can only compare objects in different catalogs based on name (since no unique ID is present).  'Duplicate' objects appear after applying a patch, where objects have been renamed.

2.  You've copied the newly tagged <SOURCE> version of the catalog to the <TARGET> and backed up the current <TARGET> catalog. Now you want to apply the differences between the two catalogs as a patch on the <CURRENT_TARGET>.

./runcat.sh -cmd diff -baseline <CURRENT_TARGET> -latest <SOURCE> -outputFile <DIFF_FILE> -skipFolder /users -verbosity detail

• diff - determines the differences between two catalog files
  
• -baseline - the currently deployed catalog, before the new changes are applied
  
• -latest - the new catalog with the latest changes from step 1
  
• -outputFile - file created with all the differences between the catalogs (except for explicit -skipFolder items)
  
• -skipFolder - again, no need to compare '/users' subfolder
  
• -verbosity - the detail is important here, especially if developers have made significant changes to compound objects, dashboard pages for example.
  

 ./runcat.sh -cmd createPatch -inputFile <DIFF_FILE> -outputFile <PATCH_FILE> -production <CURRENT_TARGET>

• createPatch - creates a patch to be applied 
• -inputFile - uses the previously created <DIFF_FILE> as input
  
• -outputFile - creates an output file of patch 'instructions'
  
• -production - determines the catalog against which the patch will be applied
  

./runcat.sh -cmd applyPatch -inputFile <PATCH_FILE> -outputFile <PATCH_LOG> -persistNewApplicationRoles

• applyPatch - applies the patch to the <CURRENT_TARGET> catalog 
• -inputFile - uses the previously created <PATCH_FILE> as input
  
• -outputFile - creates a log file output
• -persistNewApplicationRoles - forces newly created application roles to be persisted from the <SOURCE>. An optional tag, but important when development is used to build application security, 'Manage Privileges' for example.

3. Script all of these steps and you're 'automatically' patching the catalog with all the changes from development in under a minute (dependent on catalog size).

Finally, a couple of other points worth mentioning:

• archive / unarchive switches from 'runcat' are not used since they currently rely on permissions (therefore application roles) being the same between catalogs. It is also not possible to specifically archive the '/system' subfolder where privileges are stored (see previous post)*
• Oracle provided OBIA web catalogs will have tags in place. Think about tagging objects once you start creating your own. Tag objects from the outset if you're creating a catalog from scratch
• Working with Linux (OEL) just now, but these commands of course work with the windows version of the utility.

Hope this helps.

DR. OBI

*Functionality of these utilities may be subject to change in future releases.

Give your OBI EE Request the Stephen Few Treatment...

Stephen Few has a couple of great books on visual display of quantitative information. Use these principles when you're building your requests.

1. Take this...

From DR. OBI

2. Then turn it into this...

From DR. OBI

DB Functions 'Evaluate' and 'Evaluate Aggr' Bypass Cache

If super-fast performance using cache is your goal, the DB functions 'Evaluate' and 'Evaluate Aggr' will cause a request to bypass any OBI Server Cache. Look for an alternative that will populate your cache instead.

Theory: Single Dashboard Prompt, Multiple Subject Areas

Frequently the requirment pops up to provide results from more than one subject area, using a single dashboard prompt.

From DR. OBI

OBI EE doesn't handle this situation directly, so the 'mid-request' concept can be used.

From DR. OBI

The steps briefly are...

1. Create dashboard prompt using subject area 1, including required columns.
2. Create a 2nd (mid) request listing columns and data returned from the prompt in Step 1.
3. Filter columns using 'Is Prompted' option. 
4. Create the 3rd and 4th requests, from subject areas 1 and 2 respectively, then make sure these requests are based on the results of the request in Step 2.

 A worked example in a future post...

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.

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.