Showing results for 
Search instead for 
Did you mean: 
jPinhao Regular Visitor
Regular Visitor

Dimensional Modelling advice

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:


 Ambiguous Relation

(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)


Moderator v-sihou-msft

Re: Dimensional Modelling advice



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.



Helpful resources

Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 201 members 2,530 guests
Please welcome our newest community members: