How can I adjust data model to avoid triangular relationship

[Billiam864]

 

Hi - Data model question...

I have a fact table of usage/activity data containing the user, date, and location of use. 

I have dimension tables for location, date, and user details.

Two issues:

• Part of the user data includes what location they are primarily aligned to.  I then use a connection between users and locations to identify a count of users assigned to each location (regardless of if they have any activity or not).

• Both locations and users have regional geographic data they're assigned to.  If I keep the geo data within user and location tables then I'm forced to show multiple filters for geo on visuals... if I snowflake out the geo dimension table and link to both users and locations...then when a region is selected it will always apply to both (sometimes I want to see the activity for a location regardless of if user is assigned there or not...and other times I want to see activity of user regardless of location of use)

Feels like I'm stuck in a triangle data model between fact and 2 dimension tables using inactive relationships... and not a proper data model setup...thoughts?

[Greg_Deckler]

@Billiam864 Get rid of the bi-directional relationship between TableDNA and TableWorkDayRoster. You could then potentially make the dimension relationships between your dimensions and fact tables bi-directional. If you have some problem that this configuration won't solve, solve it using measures.

[Billiam864]

@Greg_Deckler; 

 

Thanks Greg for the response. As directed, I adjusted the dimensions to be bidirectional to the Fact table and not connect to each other...  however now the dimensions don't talk to each other... so for example I cannot count users by location.   Not all locations or users necessary show up in fact table,  so I don't believe communicating through that as a medium will work... unless I'm misunderstanding your suggestion?

[Greg_Deckler]

@Billiam864 I don't know specifically what visualizations/calculations you are having problems with. But you can form relationships between your dimension tables via measures, for example. This would be a lot easier with sample data and expected results.

[Billiam864]

@Greg_Deckler 

Thanks Greg, I've tried to attach an excel file to help, but appear not allowed.  Photos are provided instead.   I unfortunately cannot attach BI data (due to scale and sensitivity).  There are 100's of various visuals across many dashboards connected to this data, so I'm trying hard to focus on the core data model, as opposed to getting lost in the weeds.  Does the excel help?