Logical Levels

One of the issues that developers regularly run into with OBIEE (especially when they are new to the application) is the setting of levels against logical fact sources.  The most common error which should instantly have you thinking about levels if you see it is:

Logical dimension table has a source that does not join to any fact source

Beyond the obvious check that all sources do actually join to a fact you should then think about levels.  Logical Levels are set against the logical source of fact tables in the BMM layer:

Levels control how the fact table connects to the dimension table and are used to create level based measures and control the use of aggregation tables.  What many people don't realise is that to make OBIEE work correctly you should always set the levels on fact tables, so that OBIEE knows that it can connect the dimension and the fact at the most detailed level.

The general rules for levels are:

1. If you create a new dimension table, build a dimension hierarchy over it
   
2. Make sure the dimension hierarchy has a detail level at the lowest level of granularity for the dimension table (i.e. integration id, ROW_WID etc)
   
3. If you are adding a join from this new dimension to any fact table then make sure that you set the level for the connected dimension hierarchy against the correct logical source of the fact (the one that physically joins to the dimension), the default level to set is the lowest level (the detail level).
4. If you create a new fact then you need to make sure you set the levels as in 3 for all the dimension hierarchies built over dimensions which this fact joins to.

Sometimes you can get away with not setting levels, but more often than not this will come back and bite you in the long run, the better option is to ensure that you always set them.