SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Multiple Calendars Using Kimball Outrigger Technique


Multiple Calendars Using Kimball Outrigger Technique

Author
Message
RonKyle
RonKyle
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27389 Visits: 4488
Has anyone successfully used Kimball's outrigger technique for multiple corporate calendars in SSAS? If so, can you share the technical details of how this was accomplished? I've been trying to implement this multiple ways now but can't get the dimension to aggregate correctly. I could simply add more columns to the original date table, but that would end up being a lot of columns. I could also set up the date as a many to many. The issue with this as that the main fact tables are accumulating snapshots and I would be doing this with several dates. The Kimball Reader only has a short blurb on this, but they claim to have done this many times. The only caution that they have is that you have to in the end restrict the join to only one row per date. But I do that by making he calendar portion not aggregatable and setting a default. I'm about to give up on this for now unless someone can tell me that this is really possible. Even this design raises questions, but the first priority is to just get this to work, if it's possible. Thanks,




Chris Harshman
Chris Harshman
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37362 Visits: 7124
RonKyle - Wednesday, September 13, 2017 1:36 PM
The only caution that they have is that you have to in the end restrict the join to only one row per date. But I do that by making he calendar portion not aggregatable and setting a default.
Are you using the CalendarName column in DimCalendar to filter the results to give the 1:1 relationship to DimDate? I'm assuming based on Kimball's example that the DimCalendar has multiple different calendars for the same DateKey. I haven't used this specific example with SSAS but I have used DimDate as an outrigger to other dimensions.

RonKyle
RonKyle
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27389 Visits: 4488
Are you using the CalendarName column in DimCalendar to filter the results to give the 1:1 relationship to DimDate?

Beyond setting the property as non-aggregatable and setting a default, how else would you do this? I don't rule out that I'm missing something on this point, but I can't imagine what else there would be to do.




xsevensinzx
xsevensinzx
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21711 Visits: 5890
I would assume it's pretty straight forward like a snowflake schema, but this is SSAS afterall. I don't work with it that much.

In most of my cases, I try not to let the business units expand on the date dimension to a point of needing this. I do however support multiple fiscal calendars, but that's easily solved if they are all using the same fields, but a key to filter their fiscal based on the business unit in the date dimension itself.

I take it you are trying to support various requirements like Week versus FiscalWeek versus HolidayWeek and so on and so where HolidayWeek may not apply to all units, but maybe just one. Then the question comes to mind, why can't you just create one data mart per unit versus trying to support everything in one?
RonKyle
RonKyle
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27389 Visits: 4488
I do however support multiple fiscal calendars, but that's easily solved if they are all using the same fields, but a key to filter their fiscal based on the business unit in the date dimension itself.


How do you do this? I tried setting the calendar to non-aggregatable to do this, but that didn't work. I can't think of another way to do this.




xsevensinzx
xsevensinzx
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21711 Visits: 5890
RonKyle - Thursday, September 21, 2017 5:40 AM
I do however support multiple fiscal calendars, but that's easily solved if they are all using the same fields, but a key to filter their fiscal based on the business unit in the date dimension itself.


How do you do this? I tried setting the calendar to non-aggregatable to do this, but that didn't work. I can't think of another way to do this.

Well, if you have multiple business units sharing the DimDate table, then you copy over 365 records (i.e.: one record per day indexing on the date key) for each client into that table with a business unit identifier (foreign key). Then you ensure that identifier is also present on the Fact tables so when you do JOIN the DimDate table and aggregate it, then you are only aggregating the date records for the specific business unit. This allows you to have different fiscal calendars for different clients where maybe January 1st is not Fiscal Week 1, but maybe Fiscal Week 2 or something.

Obviously doing this across the business unit as a key is not the most optimized approach. Many business units might share the same fiscal calendar and you don't want to duplicate records where the only uniqueness is that business unit foreign key. Thus, maybe changing the business unit key to a calendar key (not date key) or even fiscal key where multiple business units can share the same fiscal key where other units can have other fiscal keys would be a better more optimized option for your reporting.

So, if I have 4 fiscal calendars across 100 clients, I only need to store 4 sets of those dates across N number of years as opposed to 100 fiscal calendars across N number of years.

Make sense?

RonKyle
RonKyle
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27389 Visits: 4488
Well, if you have multiple business units sharing the DimDate table, then you copy over 365 records (i.e.: one record per day indexing on the date key) for each client into that table with a business unit identifier (foreign key). Then you ensure that identifier is also present on the Fact tables so when you do JOIN the DimDate table and aggregate it, then you are only aggregating the date records for the specific business unit. This allows you to have different fiscal calendars for different clients where maybe January 1st is not Fiscal Week 1, but maybe Fiscal Week 2 or something


This isn't per the Kimball method. If you look at the diagram with which I opened the thread, you will see that it does not require the fact table to have the calendar key in it. The way you're proposing would require a separate fact entry for each separate calendar. I've read about this before, but it's generally discouraged because of the row count increases in the fact table. It would also mean that if you added a new calendar, you would then have to take each of the transactions and create a new set for the new calendar. The advantage of the Kimball approach (in theory) is that a new calendar would simply have to be added to the calendar record. Unfortunately I can't get the solution to work completely correctly as a practical matter, and I don't know what I'm missing. Surely someone has used this method as (supposedly) it is the method by which the Kimball team approached this problem.




xsevensinzx
xsevensinzx
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21711 Visits: 5890
RonKyle - Friday, September 22, 2017 7:20 AM
Well, if you have multiple business units sharing the DimDate table, then you copy over 365 records (i.e.: one record per day indexing on the date key) for each client into that table with a business unit identifier (foreign key). Then you ensure that identifier is also present on the Fact tables so when you do JOIN the DimDate table and aggregate it, then you are only aggregating the date records for the specific business unit. This allows you to have different fiscal calendars for different clients where maybe January 1st is not Fiscal Week 1, but maybe Fiscal Week 2 or something


This isn't per the Kimball method. If you look at the diagram with which I opened the thread, you will see that it does not require the fact table to have the calendar key in it. The way you're proposing would require a separate fact entry for each separate calendar. I've read about this before, but it's generally discouraged because of the row count increases in the fact table. It would also mean that if you added a new calendar, you would then have to take each of the transactions and create a new set for the new calendar. The advantage of the Kimball approach (in theory) is that a new calendar would simply have to be added to the calendar record. Unfortunately I can't get the solution to work completely correctly as a practical matter, and I don't know what I'm missing. Surely someone has used this method as (supposedly) it is the method by which the Kimball team approached this problem.

It just depends. In many cases, the data warehouse already has a unique key per business unit in order to allow data marts to be built separately per business unit. While it's true, that business unit may not pass to the data mart because no other business unit exists in said data mart, adding a fiscal key that is specific to that business unit in the fact is not increasing the record count unless there is more than one fiscal key per business unit. The impact is more of making the fact table wider and if you are entirely row-based, that does have a slight impact to performance. In my case, I'm all column-store.

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?
RonKyle
RonKyle
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27389 Visits: 4488
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.




xsevensinzx
xsevensinzx
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21711 Visits: 5890
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search