High Level OBIEE report design strategy

So often an organization will take on the task of installing OBIEE, establishing the infrastructure to support it, and even re-organizing their data appropriately. But when it comes to the actual report design, standards fail to be established, reports and dashboards become awkward and cumbersome, and user adoption suffers.

Since OBIEE is often seen as an Ad-Hoc reporting tool, this problem can often be the result of many developers developing with an Ad-Hoc mentality. The result is mismatched dashboards and confusing layouts. Other times, it’s the result of too many people involved in the development process. Once something gets approved by 15 people in a Power Point mock up, it’s difficult to get changes approved when something doesn’t pan out as expected. As a result, the small imperfections get set aside, but never fail to add up.

The following is a high level list of items to keep in mind when entering the report development stage of any OBIEE project. Keeping each of these items in mind will help you design attractive and useful reports and dashboards, which will ultimately lead to greater user adoption and pervasive use of the tool.


1. Trust your users - If your users don’t use your BI or other technology systems, it’s because they either get no value from it, or the system is too slow. These same users will spend their evenings shopping online and paying bills over the internet. It’s ignorant to believe they can’t use a well designed online reporting tool.

2. Think Insight, not Reports - Take the time to understand what the business user searches for, what they mark up with a yellow highlighter, and what they look at next after discovering an “exception”

3. Move Business Skills into your IT shop, not IT skills into your Business – Make an effort to understand the business needs, and then apply the technology accordingly. Don’t ask the business to “make due” with what IT provides.

4. Design your Dashboard in OBI EE itself using an iterative methodology – Don’t use Power Point to design your reports. Designing directly in OBIEE allows you to know immediately what will and will not work. Plan to make a series of adjustments throughout the design process, using feedback from the business users.

5. Establish a set of standards – If your users can’t derive insight in the first few seconds of looking at a Dashboard, it’s a bad design. This is no longer a world in which IT programmers lead the user’s impression of standards. Amazon, Google, Yahoo!, Apple iPhones, etc. all lead the pack when it comes to quality interfaces and standardized interfaces.

Column variables in the Narrative or Ticker views

Here's an important trick to remember when using column variables in the Narrative or Ticker views (or elsewhere, such as a Writeback configuration): Variable IDs map to request columns in the order the column was originally added to the request, even if you delete columns along the way.

Try this exercise in the Paint demo repository (or just follow along with your mind's eye) -- Open the Paint subject area and add or remove columns in this order:


add "Market"
remove "Market"
add "Region"
add "Units"
add "District"
add "Dollars"

... the final variable references would look like this (using the @{cN} syntax):
Region = @{c1}
Units = @{c2}
District = @{c3}

Dollars = @{c4}

To illustrate the results, go into the Compound view and add a "Narrative" view, then copy & paste the references into the "Narrative" text box as typed above, inserting a line break after each one (and why not, a line break in the "Row Separator" text box too). The view should look like this:

Click OK and observe the results.


One more subtlety: From here reopen the view for editing. Notice the server translated the @{cN} syntax into the @N syntax:







At this point you can play with formatting, row counts, placement of the view, HTML markup, etc etc -- but I think you get the idea.


Have fun...

Building Calculations in Oracle BI Pivot Tables

Recently, I ran into a situation with designing a report to appear exactly like an Excel spreadsheet that our client's finance department used for years. Using the pivot table view, I was able to mimic 90% of the Excel spreadsheet. The remaining 10% that remained incomplete was creating group subtotals and grand totals. A colleague of mine, Kevin McGinley, an individual known for his dashboard prowess, suggested creating the pivot table report by building out each row calculation using the option "New Calculated Item," which can be found by clicking on the More Options button in the column.



Following the advice of my colleague, I was able to mimic the Excel spreadsheet 100%.

To build a calculation for an item in an Oracle BI pivot table, you need to do the following:

1. In the Pages, Sections, Rows, or Columns area, click the More Options button for the measure on which you want a calculation performed.

2. Select the option New Calculated Item. The Calculated Item window appears.

3. Assign a name for the calculation in the Name field.

4. To build a formula, choose the Formula function.

• NOTE: A formula creates a dynamic custom grouping within the pivot table. All measures references in a formula must be from the same logical column and must be present in the results. Formulas can be inserted into, or combined with, other calculations.

The mathematical operators become visible. The operators are shown in the following table.

+ Plus sign, for an addition operation in the formula.
- Minus sign, for a multiplication operation in the formula.
/ Divide by sign, for a division operation in the formula.
$ Dollar sign, for acting upon the row position of an item in a formula.
( Open parenthesis, to signify the beginning of a group operation in the formula.
) Close parenthesis, to signify the ending of a group operation in a formula.

• In the Function field, build the formula by typing or clicking measure names, and clicking operators to insert them into the formula.
• Use parentheses where appropriate.

5. When the calculation is complete, click Finished.

Keep in mind that this option isn't perfect. In fact, the main drawback is that each calculated row CANNOT be moved up and down the list of calculated items. If you've created 25 new calculated items and forgot to create an item that should be located at the 10th spot, then you'll have to recreate ALL of the calculated items after #10.

Another draw back that I have encountered is that if your column formulas contain calculations, those calculations may or may not work correctly. This example is more specific to the situation that I encountered and may not apply, but take notice.

The alternative to doing this is building union queries using "Combine with Similiar Request" option in Criteria. In my particular situation, union queries was not an option because there would be too many queries and performance would have suffered significantly.