• RonKyle - Friday, September 22, 2017 8:39 AM

    Anyways, my question would be how do you filter for the right calendar with OrderDateKey (FactSales) -> DateKey (DimDate) -> DateKey (DimCalender) when the CalendarKey does not exist in the FactSales when it comes from data warehouse to data mart?      

    That is the question that I'm am asking by this post.  If I had the answer, I wouldn't have needed to post it.  This design that is in the initial post is in Kimball's Data Warehouse reader as his solution to this problem.  Unfortunately his reader does not provide any technical details, let alone sufficient technical information to implement it beyond the warning to be careful with the filtering.  I assumed that meant setting the calendar to non-aggregatable, but that does not seem to be sufficient.  The answer may lie in the attribute relationships.  If you haven't implemented a solution according to the design above, you're not going to be able to help me.  It's not that I can't do multiple calendars.  There is always the option to add more columns to the date dimension table.  It's that I can't get this particular solution to work properly.  If it weren't from Kimball, I would assume there's something wrong with the design.  But as he says it's the one he uses, someone surely knows how to make the design work in SSAS.

    Well, I may be getting ahead of myself here. I'm not really focusing on Kimballs methodology here because regardless if Kimball posted an article on not, I'm more focused on the problem at hand: how can you implement multiple calendars? This is extremely easy to do and it's pretty obviously the technical details you're searching for are missing.

    If you want to implement multiple calendars to your fact then you're going to need to have those attributes to filter to the calendar. Otherwise, that calendar key is going to cause a many-to-many relationship across the various calendars, which will require you to implement a junction table that effectively do the same thing. This is assuming the Kimball article is referencing multiple calendars. If it's not, then there is a 1:1 relationship because only one unique calendar key exist for every one unique date key right? The moment you have multiple calendar keys, then that's going to duplicate the date key, which is the only key are you joining on from the fact (this is a problem).

    This leaves us with the solution outside of that article, which is you're going to have to find some way to associate your fact to your calendar. You can do this in the fact itself by again, adding a key to filter the calendar dimension or you can add a junction table that will associate an existing key in the fact to the calendar table such as maybe CustomerKey, OrderDateKey and CalendarKey together to formulate that 1:1 relationship.

    Course, the one thing we all may not be considering is maybe that date key is not a numeric value for the actual date that's reused across business units, but actually a sequential value where the dates in the DimDate key are duplicated across business units. This means every business unit in the fact has it's own set of unique date keys, which will effectively filter the DimCalendar table. But, that's no different than adding the business key to the dimension and duplicating the same values per unit.