OBIEE Blog

There was a problem in drill down in one of the hierarchy with the order it displays the lowest level attributes.

When drilling down on a certain category – “Organization”, Answers always shows columns in the following order:
Organization Name,Department Name, Sub-Department Category, Org Detail Number, Org Detail Name

The desired order is:
Organization Name,Department Name, Sub-Department Category, Org Detail Name, Org Detail Number

The hierarchy was defined as following in the RPD (please note that Org Detail Name and Org Detail Number are at the same lowest level defined as keys):

Organization Name
Department Name
Sub-Department Category
Org Detail Name | Org Detail Number

It has been found that it doesn’t sort alphabetically.

With Oracle’s OBIEE support and help – the solution has been found:

1. Use the Query Repository feature under Tools menu in the RPd to do a search of these 2 columns in the RPD.
2. In the Query Repository window note down the IDs for these 2 columns. Please check which one is bigger than the other.
3. The logical table, delete the 2 columns (Org Detail Name and Org Detail Number) and add them again in the desired order. Make sure you add them in the correct order. Query for the columns again, the IDs should be in the reverse order as step 2.
4. Add them in the hierarchy again
5. Change the order of level keys in Dimension hierarchy
6. Save the changes and test again.

After RPD re-deployment – it was working.

There must be an easier way to change order of composite key (complex key) columns.

Sometimes, we need to define an Aggregated Measure based on a Dimension Table.

If aggregated calculations are performed directly from a dimension logical table field, an error similar to the following will appear:

A general error has occurred. [nQSError: 14026] Unable to navigate requested expression: ). Please fix the metadata consistency warnings.

To resolve this type of error, put the measure indicated by the error message in a fact table object.

To define an aggregated measure of a dimension table, complete the following steps:

- Create a new fact logical table with the physical dimension table as source.

- Include all fields that should be aggregated as a measure of this new fact object.

In the Siebel Analytics version 7.5.3 repository, a good example is the Fact – Campaign Metrics fact table that is based on the W_PRG_CAMP_D physical table:

There’s an excellent article by John Minkjan about OBIEE caching. He covers most of the important functions of OBIEE caching in detail as well as providing valuable information in regards to the NQConfig.ini cache settings. This is probably the best OBIEE caching tutorial available publicly. It has helped me a lot when preparing documentation for deployment of a new project.

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