Avoiding the CAST Function

To improve query response time, try pushing columns that are calculated with the CAST function down to the database level. You should see a significant performance improvement, especially if the DBA can add an index to the database column.

Debugging issues with Logical SQL

Have you ever encountered errors when trying to develop and test a logical SQL statement issued directly to the Oracle BI Server? One common root cause is the use of SQL constructs not supported by the OBI Server; sub-queries are particularly sensitive. According to the Oracle documentation, only the following sub-query predicates are supported (in addition to UNION and UNION ALL):

IN, NOT IN
Any, >=Any, =Any, <Any, <=Any, <>Any
All, >=All, =All, <All,<=All, <>All
EXISTS, NOT EXISTS

For example, try using &ltAny instead of &lt. If this does not fix your problem, see if a physical SQL statement is being generated when you run the logical SQL statement. If so, try running the physical SQL directly against a database, and the database error description may provide the information you need for debugging.

Differentiating Your Environments

Have you ever made a change to a report within one environment but meant to do it in another? Try modifying the style sheet whereby changing the section or heading color to differentiate your environments. Migrating the repository or web catalog will not carry forward any style sheet modifications.

Enabling Language Translation

If you are thinking about enabling language translation on your Oracle Business Intelligence web site, remember only the text that OBIEE provides out of the box will be automatically translated into the selected language. You will have to customize all data, dashboard titles, reports titles, logical columns, etc to the appropriate language(s).

Implementing Simple Column-Level Security

Some columns, such as Social Security Number (SSN), may contain sensitive information that should only be displayed to certain Users or Groups. A simple way to restrict the visibility of this data is to use column-level security. All you need to do is modify the Permissions for a given presentation column in the repository. By default, the Everyone group will have read access; you must uncheck this check box, and then explicitly grant access (check the boxes) only to the appropriate groups. Now let’s say that Basic_User does not have permission to view the SSN column, but Super_User does. If there is a Dashboard or Report that contains the SSN column, the Super_User will be able to run the report and see the SSN data, but the Basic_User will not even be able to run the report; the report will produce an error like this:

Click to read more ...

Using Group Level Permissions instead of User Level

For ease of catalog administration, only assign permissions at a group level (as opposed to an individual user level) for objects in the web catalog (i.e. reports, pages, dashboards, prompts, etc.). Adding individual users to these elements makes maintenance difficult and costly. Also, you will save yourself many hours of “hunting and pecking” to determine who has access to what.

Setting a Sort Order Column

Have you ever noticed a property called Sort order column, located in the General tab of the Logical Column Properties Dialog Box? By default, this value is set to “None”, which is usually the desired value, but to demonstrate when this property comes in handy, let’s look at an example. Suppose your repository contains a column called “Month” and a column called “Month Number”. “Month” contains these values: Jan, Feb, Mar, Apr,…Dec. “Month Number” contains these corresponding values: 1,2,3,…12. If you have a Dashboard Prompt or a Report containing the “Month” column, the “Month” values will likely be sorted in this order (alphabetically as opposed to chronologically): Apr, Aug, Dec… Sept.

Click to read more ...

Using a FILTER Function Instead of CASE Statements

Let’s face it; CASE statements are notorious for causing poor query performance. For certain kinds of simple CASE statements, there may be a more performance-friendly OBIEE alternative – the FILTER function. Like CASE statements, you can use the FILTER function to build a logical column expression. In the Expression Builder, this function can be found under Functions > Display Functions > Filter. Here is an example of how to use it:

Suppose you have two Logical Columns derived from the following expressions:

Click to read more ...

Creating Time-Series Metrics Using the Calculation Wizard

(Click to Enlarge)After creating your base metrics, try using the Calculation Wizard to automate the process of creating time-series metrics or comparison metrics such as: Change, Percent Change, Index, and Percent. The Wizard may save you many hours of time and frustration, especially if you have to create more than a handful of these metrics. In addition, the Wizard allows you to include customized logic to handle nulls in the base metrics. You may refer to the Oracle documentation for further detail.

Hiding Layers in the Administration Tool for Ease of Use

Under the View menu, consider displaying only the repository layers that you are working on. Your eyes will thank you! Viewing all three repository layers at once reduces visibility to objects with long names, especially in the logical layer.