Filter Drop-Down Creates Count Distinct On Degenerate Dimension

Question

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.

Robert Angel · Answer

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

User_R2XFA · Answer

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 · Answer

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.