Some of my team's challenge for a supply chain effort is being able to map calendars from different suppliers into the internal distributor's Gregorian/fiscal calendar for purposes of performance comparison. Each trading partner may use a different calendar that defines months based on a 4-4-5 calendar quarter or they may use a gregorian calendar as well. The goal is to be able to not only compare at the day level, but at higher levels of aggregation, such as week of year, month of year, quarter of year, etc...sounds like custom code to me, but I'd like to toss this out to see if some of you geniuses out there have solved similar problems involving reconciliation of fact tables through multiple or alternate calendars.
Also, what type of relationship and/or architecture are used to prevent looping hierarchies like these below?
Any thoughts would be appreciated.