OBIEE Blog

Even though, I believe that OBIEE Answers is a very fine piece of software, sometimes, I’m annoyed about certain OBIEE bugs and quirks.  Here’re some of my favorites (least favorite OBIEE Answers limitations).

1. You can not use prompts on a calculated column in answers.  Workaround: you can create this calculated column in the rpd in the business layer and than add that field to the presentation layer. This also adheres to best practices (put as many metrics as you can to Business Model Mapping layer).

2. When a prompt is created based on an SQL Condition (in “Show” “Sql Results” Option), if a filter is applied when this Prompt is used the condition created originally is lost. This is most likely an OBIEE bug and I’m not aware of any workarounds.

3. Inability to display the beginning date and the end-date in the report based on the operator “between”, because with this operator, you can’t define a presentation variable. In this case, the Between Date Prompt isn’t working properly and is a known OBIEE Answers bug. The workaround is to  create a second report. This will have the date column used by the report, only twice. For the first date column, change the column formula to min(date) and for the second date column change the column formula to max(date). Then use a narrative view something like ‘Report for period beginning @1 and ending @2′.

4. In Answers, when creating a filter, you sometimes see an incorrect behavior in the Edit Filter popup where the wrong values are loaded for a column. This behavior occurs when clicking the ‘All Choices’ link in the filter popup, for a column and then cancelling it. When you then navigate to another column to create a filter and select ‘All Choices’ you get the dropdown values for the previous column being loaded. No workaround, however, you can refresh the same filter again to get correct dropdown values.

5. Download Data and Download to Excel work differently. OBIEE is downloading all the columns present in Report Crieteria page of Answers part (even if they’re not included in the Dashboard). The workaround is to use Download to Excel.

Concat- this useful function is often overlooked in BI. However, by using it – you can make your routine work much easier (at both BMM and Presentation layers of OBIEE).

There are two ways of using this function in OBIEE. The first method is to simply concatenate two character strings. The second method uses the character string concatenation character (||) to concatenate more than two character strings. You can also reuse the CONCAT function in OBIEE, but this might be an “excessive use of force”. Please note, you can only concatenate CHAR/VACHAR strings *you can use CAST function to convert from other data type*.

Method 1.

CONCAT (expression1, expression2)

where:

character_expression Expressions that evaluate to character strings (if numeric you can use CAST function).

For example – CONCAT (‘The year is ‘, CAST(TimeTable.FiscalYear AS CHAR))

Method 2:

CONCAT (expression1 || expression2 || expression3 and so forth)

where:

Very much like the previous one – expressions that evaluate to character strings, separated by the concatenation operator || (double vertical bars).

For example – CONCAT (‘Total is: $’ || SalesFact.TotalSales ||  ‘USD’)
I was having trouble using the CONCAT function type 2 expression in the Business Layer of the Analytics Admin Tool (using multiple expressions which evaluate to strings). The problem seemed to happen with functions such as
CONCAT (“DimTable”.”Org”.”STATE” ||”DimTable”.”Org”.”CITY” ||”DimTable”.”Org”.”ADDRESS”) and the error message is:   [nQSError: 27002] Near <)>: Syntax error [nQSError: 26012] .
The workaround seems to be using a nested CONCAT statement – CONCAT (CONCAT(“DimTable”.”Org”.”STATE” ,”DimTable”.”Org”.”CITY”),”DimTable”.”Org”.”ADDRESS”) Hopefully, it’ll be fixed in the OBIEE release 11g

Let’s start by identifying how to define performance management. It’s not as simple as you might think. An Internet search conducted in July 2007 on the term performance management returned 23,000,000 results. Out of the gate, the search for the “answer” begins and we find ourselves digging through disparate data sources to identify the right answer. With some initial investigation, we find that the term performance management is used and referred to as both business performance management (BPM) and corporate performance management (CPM). BPM is a set of processes that helps organizations optimize their business performance. It is a framework for organizing, automating, and analyzing the business methodologies, metrics, processes, and systems that drive business performance. BPM helps businesses efficiently use their financial, human, material, and other resources. Organizations may take components of the performance management spectrum and deliver solutions specific to the business area seeking better decision-making. Performance management then can be the next generation of BI. CPM has been defined as “a set of processes that help organizations optimize their business performance. It is a framework for organizing, automating, and analyzing business methodologies, metrics, processes, and systems that drive business performance.” Does it sound familiar?

If you’d like to have your own OBIEE column – please let us know and we’ll set you with one! We’re always looking for authors and submitters. Also, please share your feedback with us, for example, which topics you’d like us to cover.