Multiple Calendars

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.

Slowly Changing Facts

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.

Bridges

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. 

Learning Resources

I like quotes.  A great one that I find myself going back to fairly regularly is one by Danny Way.  Pretty sure it was the first H-Street video (late 80's i think). 

Something along the lines of:
"Anybody can learn anything they want... 
  Just gotta spend time on it..." 

Great attitude.  Just as true in technology and business as it was for him in skateboarding. 

With that in mind, I wanted to pass along some links to learning resources for people trying to stay on top of the latest developments in Microsoft BI like me. 

This should be interesting...

Can't say I'm the most talented writer, so forgive me as I get the hang of this...

Planning on using this blog as a way to capture and share a few of the hopefully useful things I learn over the next few years.  And perhaps a few of the ones I've learned over the last dozen or so working with data and analyzing things. 

Should be an interesting ride.  Plenty of Microsoft BI and Data Warehousing to go around.  Plenty to explore on the Analysis Services and PowerPivot fronts, so will try to pass along a few ideas there.  Always game for a good SQL puzzle too, so expect to see a few of those on here as well.