Data Modeling for Complex Granularity


#1

I’m working with a very complex set of hierarchical lookup tables with one main fact table but I’m experiencing issues in creating all of the “correct” relationships. Although some of these hierarchical lookup tables are specific to the organization’s operation, most are geographical (e.g. state, circuit, county, etc.).

I know you’ve briefly addressed aligning data model granularity in some of your courses but do you have any specific recommendations on resources I should review? Also, this would be a great topic for an advanced modeling course.

Thanks.


#2

Hi Dave, want to post a few images with examples. Happy to walk through it once I know a bit more about what type of relationships are required and also what type of visual your ultimately looking to set up here. Chrs


#3

Dave, more information please…Alot of times you need to ensure your dimension table has unique values and your fact tables have no invalid entries. Also check the data types are the same can often be a problem. State, Country can have specific data type category so maybe that is why in Power BI the relationship is not working.

Cheers
Garry


#4

Look a developing a unique data key in required tables that can link up correctly