Removed aggregation rule in RPD and reports get error

Question

In RPD we have table containing Employees Performance reviews, and column EP_Rating in BMM has aggregation rule set as SUM.

When we create headcount report per employee per year, there is no possibility to not sum rating.

We have decided to remove aggregation rule in RPD, but then in report there is error:

State: HY000. Code: 14025. [nQSError: 14025] No fact table exists at the requested level of detail

What I have missed in setup when changing aggregation rule to have it working?

Or maybe where it is defined in other way, that it is measure not dimension?

Gianni Ceresa · Answer

aPsikus wrote:We have decided to remove aggregation rule in RPD, but then in report there is error:State: HY000. Code: 14025. [nQSError: 14025] No fact table exists at the requested level of detail

You are abusing a bit too much here

OBIEE requires the business model to be a perfect star, a star schema has dimensions (attributes without any aggregation) and facts (columns with an aggregation rule).

You can't have a model without facts, you need them.

If you have a fact column which plays the double role of fact and attribute (so once aggregated, once not), you just have to model it correctly. It's a degenerated dimension and can perfectly be modelled in OBIEE.

And if you keep things clean (in the fact table only measures and no attributes) you will never have issues.

It's maybe worth you make a step back and think at your dataset, at your model and clearly identify the role of each column: attribute? fact? And you then model accordingly.

Gopi Mannava · Answer

Good info john.

John_K · Answer

You might want to alter the rule to be based on dimensions and use Last() on the date and Sum() on other dimensions.

http://www.wegobeyond.co.uk/blog/aggregation-based-on-dimensions-in-obiee