I believe your problem is already starting with your fact table, from here your measures are linked to only GeoID and StudyID. You then detail your Studies into sites and then trying to link back your sites to countries via the sitemanager. This design doesn't seem appropriate to me (but of course I don't really know what you want to analyze).
Your dimensions, namely the study sites, are more detailed than your fact table, so you won't really get your measures per site. I assume that the GeoID and StudyID based measures are targets (you called them Milestone), so you are probably thinking of bringing in some more actual study results?
Maybe you could tell us a bit more about the big picture and your analysis requirements?
P.S: It's quite easy to break the loop by removing the GeoID form Dim_RSM, but I assume that's not what you want. I wouldn't expect to assign a GeoId to a RSM, but maybe to a site, why do you need this information at this place?
LoopEg3_SW.qvw 156.8 K
Thanks for your quick reply.
The table structure which I have shared with you is a mockup of original datamodel where I have multiple attributes within each table.Also the fact milestone is having multiple dates as per the different milestone. The geography table contains a hierarchy which is Region-->Country-->Subregion.
I do not have Sub-region coming in Dim_Site from the source. It is only present in Dim RSM. I can manage to populate Sub-region at site level using the fact that only one RSM can be assigned to a Site for a study.
Doing this if I create Dim_Geography join with Dim_Site then also Fact_monitoring_milestone will create loop by joining with Study and Gegography table.
Thanks & Regards,