In this post I received a revision of my original datamodel regarding information about Students, Schools and some variables describing the relationship between them (like the distance between the students residence and the school’s address).
This was my original data model:
This is what the revised data model looks like:
The most relevant change to the datamodel was the transfer of the address columns from the Address table back to the Students table and the Schools table because they can be seen as rather stable aspects of Students and Schools and belong in their proper dimension table.
The reason for me to bring all addresses together in one table had to do with the fact that I want to show the geographic location of both Students and Schools in the same map visual. And the map visual does only allow for one column for latitude coordinates and one column for longitude coordinates.!
As explained in the same post, this can be solved by joining the address columns of the Students table and Schools table using DAX:
The resulting table can indeed be used to show the geographic locations of both students and schools in the same map visual. But on the same report page are also table and other visuals displaying information about the students and information about the schools and I want those visuals to interact with the map visual. So when the user selects a school in one of the visuals, the map visual will filter on that particular school and vice versa. I would say that such interaction would require to define a relation between the Mapping Locations table and the Student table and the School table, being a one to one relationship. That would bring me back closely to the original datamodel I started with and doesn’t fit the star scheme requirements.
What would be a proper solution for this situation?