Friday, 24 April 2015

OBIEE On-Board Performance Monitoring

Yesterday I presented at the DOAG 2015 BI, the German Oracle User Group BI conference. As assumed there was a quite good interest and discussion concerning my OBIEE performance monitoring topic.

OBIEE UsageBI performance is a complex topic and there are often many ways to increase it. When checking existing client solutions we recognize frequently that cost-intensive solutions are the first choice and customers don't know what user really do. So at first, start analysing your analytics application:
Enjoy OBIEE,
Gerd

Thursday, 16 April 2015

OBIEE Filter Sliding Last Day of Month

In general OBIEE repositories provide a bundle of date repository variables which update for example every 30 minutes to easily calculate a sliding window on the date dimension. So, users don't have to change their reports in the course of time:

OBIEE Line-Bar Chart

OBIEE Date DimensionWhen having a look to the Oracle BI line-bar chart above it shows figures by the last day of the month in a sliding window. Such a request isn't very common and typically there are no date repository variables for such a requirement. But more likely two variables like

CURRENT_MONTH_YAGO
PREVIOUS_MONTH

should be available. But filtering the last day of several months on a standard date dimension in a sliding window is not possible with these Oracle BI repository variables. The solution is based on the same functionality already introduced in blog post OBIEE Substitute a Filter Variable. When applying the filter set a SQL Expression value as follows:

CASE
  WHEN "Calendar Date"."Month Code" IN
    (1,3,5,7,8,10,12) THEN 31
  WHEN "Calendar Date"."Month Code" IN
    (4,6,9,11) THEN 30
  ELSE 28
END

In the Edit Filter dialogue box it looks like:

OBIEE Edit SQL Expression Filter

The filter of the report will be:

OBIEE Filter

Thursday, 9 April 2015

OBIEE Dashboard Recursive Master-Detail Navigation

Dashboards and its reports are sometimes designed to present as well overview and detailed information. Obviously switching between detail levels can be done by filtering via an OBIEE dashboard prompt which is not very user-friendly, because users have to type the values into the prompt even if the field is already visible in the result set, like Product No in the example dashboard page:

OBIEE Dashboard Master View

It's just an example based on two reports. Such dashboard pages could be much more complex. Intended functionality is quite the same as Master-Detail events do between views of a specific analysis. So, behaviour should be drilling on Product No results in:

OBIEE Dashboard Recursive Detail View

To implement this functionality just set an action link on the report column with destination to be the dashboard page itself so that the same set is filtered when drill down is performed:

OBIEE Dashboard Recursive Action Link

Of course, the Product No value is propagated to the dashboard prompt (if your OBIEE server patch set is not too old). Shown solution also works fine when filtering the dashboard master view on Title first.

Wednesday, 8 April 2015

OBIEE Dashboard Prompt Eliminate Required Field with Implicit Defaults

Sometimes report requisitioners insist on getting table views showing each data row in detail. Without applying a filter such reports in general don't return results within an acceptable runtime. Additionally the Oracle BI server and database are stressed unnecessarily. To avoid long running queries when opening a dashboard you can implement a simple solution. Just set a filter to a value which doesn't exist:


Such a filter implies to provide a prompt for the same field which must be set to be required (see asterisk):


If the user doesn't need to insert a value then the value n/a in the report isn't overriden and query returns an empty result set again. Setting a proper default for the field isn't always possible or makes no sense at all, because users don't know adequate values or want to filter other detail fields, e.g. Title in our case. Thus, Product No has to be set with a default implicitly after selecting a Title. To do so OBIEE dashboard prompt settings should be:


Especially the options Include "All Column Values" choice in the list and Limit values by Title are necessary. Implication after setting the Title:


Product No is set to (All Column Values) without the need to insert values explicitly with the result that the required field is eliminated.