Fielded a question the other day about needing multiple calendars in a dimensional model, and wanted to pass along some thoughts here. The key requirement was to be able to analyze business events (fact data) by filtering and slicing by multiple
calendars. Pretty common requirement in many organizations.
Particularly ones that operate in multiple regions of the world and/or have business units that behave as
separate entities. Sometimes the subs
are acquisitions that continue to operate independently. Sometimes the subs operate in different
markets that observe different holidays.
Whatever the case, if you are working on a dimensional modeling assignment
and you have a requirement to consolidate fact data across multiple entities
with different calendars like this, hopefully this post will be helpful.
I had a design challenge on a project a while back where I needed to be able to capture versions of the fact table data and allow the users to analyze the facts as they looked at a chosen point in time. Sales analyses needed to reflect original amounts (as the fact originally looked), but all other analyses needed to reflect actual fact amounts (which commonly got overwritten with updates from the collection process). This was further complicated by the fact that the source system was a hosted solution and the vendor was incapable of providing incremental extracts (legacy system of course). So we simply got a full dump of history every night. This was a tough one.
I fielded a question recently on the forums about what bridge tables are and what role they can play in a dimensional design. The specific case was for someone trying to design a dimensional model for a healthcare organzation with a clinical research unit. In their case, they had observations and lab results that they envisioned as fact tables. And they had patients, physicians, procedures and diagnoses that they envisioned as dimensions. The challenge was how to capture the many-to-many relationship between the events and the procedures and diagnoses. And what benefits bridge tables would provide in this situation.
