OBIEE - How to create a measure from data that is stored in a dimension table
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).
Articles Related
Steps
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.
- Add an aggregation rule to the column
- set the logical level of the other dimensions to the grand total level.
Below, a sum aggregation rule :
Support
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.