How to create a measure from data that is stored in a dimension table.

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:

OBIEE Caching

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.

Some OBIEE Answers limitations

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 function

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)


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)


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

Business Performance Management

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?