Dimensional Modelling - ambiguous relations

Question

I've been trying to solve an issue, and to date I haven't been able to reach what I'd say is an optimal solution. I have a dimension (Features) which needs to be referenced in 2 other dimensions (Actions and Sessions), which in turn are referenced from the same Fact table (UserAction). This creates ambiguity and I can't complete the schema:

(note: snip of the model, not the whole thing) (included the bridge tables to show some of the added complexity in the model with many-to-many relationships)

I think the issue might be with Dim_Features technically having different meaning between both dimensions, but I'm still trying to use it as the same? It means both:

• An Action belongs to this Feature / Feature Area
• A Session had this Feature / Feature Area available (owned)

What I need to accomplish is being able to filter/slice Fact_UserActions by Sessions where certain features are available / unavailable, to then analyse things like:

• Which Features are used when Feature 'A' is owned (as in, correlations between certain features being ownes, and others being used)?
• How many users who own a Feature have not used it?
• How often is a Feature used? (constrained by population of sessions that own it, ie. where it could actually be used)

Any ideas on what I might be doing wrong, or how I might improve the model?

EDIT: In case it helps, the sort of thing we'd want to get out of this is a table such as:

Where we can see the impact a feature has on the population as a whole, and within the population that owns it.

Answer 1

I think your problem is that you're working at the wrong grain. The standard Kimball advice for star schemas is to always find the absolute lowest grain, because you'll always be able to aggregate up.

Have a look at all of the questions you want to be able to answer - they're all about use of Features, yet the Fact table you're using to analyse Features isn't at Feature usage level. The Bridge tables exist to try to work around this.

It's important to remember that the vast majority of the time, your Dimensions should only be related indirectly, though Fact tables. Sometimes you need a Bridge table, but relatively rarely.

It's hard to come up with a suggested schema here without knowing how it fits in to the rest of the model, but consider the following:

• Replace Fact_UserAction with something like Fact_FeatureUsage.
• Have action_id, session_id, and feature_id in Fact_FeatureUsage.
• Get rid of your Bridge tables.

Answer 2

I would remove the relationships to Dim_Features and then hide it.

Then I would create two New Tables (while on the Report or Data view, go to the Modeling ribbon and click New Table). The DAX expressions for each would be something like:

Features (Actions) = 'Dim_Features'

Features (Sessions) = 'Dim_Features'

Now you have 2 independent copies of the Dimension table, and you can create the relationships to each of those in the Relationships window.