Problem joining tables

Question

I am trying to join a new dimension to some fact tables that already exist.  The Fact tables are all from the same physical table but have different aliases in the RPD.  The join is a complex join using two columns.  When I select columns from the Fact and dimension in an analysis it produces the "No fact table exists at the requested level of detail" error.

As a test I created a new alias for the same physical fact table and it doesn't get an error when joining with the dimension.  The existing fact aliases are joined to lots of other dimensions.  The BMM fact tables only represent a single physical fact table, not a combination.

Could the other dimensions that join to the fact tables be causing the error?  If not what else could it be?

user4109719 · Answer

After setting content level in the Fact it works now.  The Fact table alias I created as a test was working so it was confusing me.

[Deleted User] · Answer

user4109719 wrote:The content level is set in the sources tab. The dimension hierarchy has just the top level and the detail. I set it to the detail level.

If the detail level has been set in the logical fact as well as in the logical dimension tables, then the physical join must represent this exact relationship.

So things come back to the exact LTSs which are used for the respective logical tables. It's a basic concept which forms the basis of OBIEE since more than 20 years, so it definitely is a proven working concept.

user4109719 · Answer

The content level is set in the sources tab.  The dimension hierarchy has just the top level and the detail.  I set it to the detail level.

Does the fact that I can join a different alias of the same physical fact table mean anything?  If the content level is the issue I don't see why that would be possible.

One thing that may be relevant is that the dimension is defined by a query, not by a database table.  The query does a join with the Fact table which creates a large number of records in the dimension.