Compare Items Using Dashboard Pages

Ideas for blog entries using OBI EE come up all the time and I should really post them straight away, but something work-related usually gets in the way ;-)

On a lunch break I was reading Stephen Few's blog entry about OECD visualizations. I don't always agree with him, but his writing is interesting; always encouraging readers to strive for high standards!

Now OBI can't display flowers yet, but it was the 'interactive comparison' that got me thinking. Hmmm, how can I achieve something similar with a dashboard? Let's get the most out of what we have in OBI EE, here's the steps:

1. Create a new dashboard page with multiple sections and columns (e.g. 'Compare Items')

• layout relates to how you want to compare the request contents (see example below)
• one section for the 'Compare Prompt', remaining sections are for the request(s) being compared

From DR. OBI

2. Create a page prompt for the dashboard page with a single column holding a list of numbers. You could use:

    SELECT CASE WHEN 1=0 THEN "Table"."Column" ELSE 0 END FROM "Subject Area"
    UNION
    SELECT CASE WHEN 1=0 THEN "Table"."Column" ELSE 1 END FROM "Subject Area"
    UNION
    SELECT CASE WHEN 1=0 THEN "Table"."Column" ELSE 2 END FROM "Subject Area"
    UNION
    SELECT CASE WHEN 1=0 THEN "Table"."Column" ELSE 3 END FROM "Subject Area"
    etc...
3. Add a presentation variable for the column (e.g. dp_comp).
4. Uncheck 'All Choices', Save, then add the prompt to your new dashboard page.

From DR. OBI

5. Create a new request with the layout you need. You'll be comparing items in one request with others, consider adding some dynamic elements for more interest (e.g. view selector, pivot page prompt).
6. In the request, create a filter that directly references your @{dp_comp} presentation variable:

    - add ANY column as a filter criteria
    - select the 'Filter Options' then 'Edit Column Formula'
    - replace this column with your presentation variable reference @{dp_comp}

7. Return to the 'Filter Options', then 'Edit Filter...'. Set filter criteria @{dp_comp} >= 1 (see below). Save the request.
8. Repeat steps 5-7 for each new comparison request, changing the value in the filter each time. I've created 5:

    - filter criteria @{dp_comp} >= 2
    - filter criteria @{dp_comp} >= 3
    - filter criteria @{dp_comp} >= 4
    - filter criteria @{dp_comp} = 5

From DR. OBI

Running these requests directly will give an error, don't worry about it... head back over to the dashboard page from step 1.

9. Add each new comparison request to one of the sections previously created. I've done this from left-to-right, but the order doesn't matter here.
10. Set the 'Guided Navigation...' for each request section:

    - Reference Source Request = Yes
    - Source Request = reference to request in section
    - Show Section = If request returns rows

11. Save the dashboard page layout.
12. Select a value from the page prompt and the same number of 'comparison' sections appear.
N.B. The usual limitations with 'Guided Navigation...' and large result sets apply. Be cautious with the size of your requests.

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...

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.

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.