Dimensional Modelling advice

[jPinhao]

Hi, 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? 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)

[v-sihou-msft]

@jPinhao

In this scenario, if you only care about the Features either Used (realted to Action dimension) or Owned (related to Session dimension), your schema is fine. But if you need to know the count of Features sliced by both Action dimension and Session Dimension, you'd better build a fact table comtains Action, Session, Feature information between Dim_Action and Dim_Session connecting Dim_Feature.

Regards,