Oracle Business Intelligence Applications

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Business Intelligence Applications

Categories

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

Filter Drop-Down Creates Count Distinct On Degenerate Dimension

Received Response
12
Views
5
Comments
User_R2XFA
User_R2XFA Rank 1 - Community Starter
in Oracle Business Intelligence Applications

I have degenerate attributes in the RPD that are not available in a proper conformed dimension. When using the drop down functionality while creating a filter on analysis with an attribute from the degenerate dimension OBIEE sends a  "select distinct attribute from fact". While this make sense it is causing havoc on  out DB. Is there a way to limit or disable this functionality?

Thank you in advance.

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Yes. Model this correctly and properly. Attributes do not belong in the logical facts unless needed for calculations but even then - NEVER exposed through the presentation layer.

    Also if that attribute physically only exists on the fact table and is not correctly indexed then you have a general issue.

  • User_R2XFA
    User_R2XFA Rank 1 - Community Starter

    Hi Christian,

    Logically the attributes are in dimensions only physically are they in facts. These attributes are de-normalized and do not exist in any physical dimension.

    While I wish I could control the modeling, indexing, partitioning etc.  of of our sources, this is not within scope. I am looking for a way to make the best out of the given situation.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    I think you are misunderstanding @Christian Berg 's answer -

    "Attributes do not belong in the logical facts....."

    Physically it is okay, and sometimes even more performant, that Facts have attribute fields.

    But in the business model layer the Fact should have measures, the Dimensions should have attributes.

    So you model a physical star schema; in the Business Model Layer facts have measures, dimensions have attributes.

    You model a single table source; in the Business Model Layer facts have measures, dimensions have attributes.

    You model 3NF; facts have measures, in the Business Model Layer dimensions have attributes.

    You model snowflake / degenerate dimensions; facts have measures, in the Business Model Layer dimensions have attributes.

    Make sense?

    Btw - do change your display name, people are more likely to talk to you. And do mark your answers for the benefit of the entire community.

  • User_R2XFA
    User_R2XFA Rank 1 - Community Starter

    Hi Robert,

    I understand, the attributes that happen to be on physical facts must be logically normalized into 3NF in the Business layer. This is what we are doing. The issue arises when a user attempts to add a filter to an analysis and either clicks the drop down to select a value to filter on. This action pushes a count distinct to our data base (as expected).However, without filters or use of index/partitions when issuing the count distinct this query is too demanding on our database and causes performance issues. The model is functional overall we are simply looking to protect our database from count distinct over the entire fact table.

    Thank you for the UN tip.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Look like using an ETL of somekind to model this into a true dimension table is your only option.