Forum Discussion
How can I adjust data model to avoid triangular relationship
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?
4 Replies
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.
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?
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.
