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

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs)

1 - About

You may:

  • need to create some count aggregation on your dimension attribute.
  • have some column in your dimension that hold measures such as the working day for a time dimension.

In this case, we are in presence of a level-based measure with all the logical level of the other dimensions set to the grand total level. Ie the aggregation takes place only for the dimension (group by one attribute of the dimension).

Advertising

3 - Steps

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

  1. Create a new fact logical table with the physical dimension table as source.
  2. Include all fields that should be aggregated as a measure of this new fact object.
  3. Add an aggregation rule to the column
  4. set the logical level of the other dimensions to the grand total level.

Below, a sum aggregation rule :

4 - Support

4.1 - nQSError: 14026 - Unable to navigate requested expression

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:  <Measure detail>). 
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.