OBIEE 11G - [39059] Logical dimension table has a source at level that joins to a higher level fact source

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

1 - About

This repository consistency check is a new check performed in 11G and indicates that a dimension joins:

  • on the detail level
  • but also on a higher level

3 - What is ...

3.1 - The problem

You can get this kind of data modelling when you model a snowflake dimension. Each table represents a level and you can have then in the physical layer:

  • a join on the detail level
  • a join on a higher level

Advertising

3.2 - The Solution

To avoid this message, you must create a physical alias for the level 1 and 2 tables and join this alias tables to the aggregate table.

4 - What says the documentation

Even though this fact logical table source has an aggregate grain set in this dimension, no valid physical join was found that connects to any logical table source in this dimension.

This means that either no join exists at all, or it does exist but is invalid because it connects a higher-level fact source to a lower-level dimensional source. Such joins are invalid and ignored by the Oracle BI Server because, if followed, they would lead to double counting in query answers.

For example, consider Select year, yearlySales. Even if a join exists between monthTable and yearlySales table on yearId, it cannot be used because such a join would overstate the results by a factor of 12 (the number of months in each year).

All three of the given validation 39009, 39055 and 39059 rules are related to the same issue.

5 - Documentation / Reference