Removed aggregation rule in RPD and reports get error — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Removed aggregation rule in RPD and reports get error

Received Response
31
Views
4
Comments
aPsikus
aPsikus Rank 6 - Analytics Lead

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?

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist
  • John_K
    John_K Rank 5 - Community Champion

    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

  • Gopi  Mannava
    Gopi Mannava Rank 3 - Community Apprentice

    Good info john.

  • 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.