• 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?