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) that 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.