Creating Custom Calendars in SQL Server Analysis Services

  • Comments posted to this topic are about the item Creating Custom Calendars in SQL Server Analysis Services

  • An excellent, simple, step-by-step post illustrating how to create user-defined custom time dimensions. Thanks and well done 🙂


  • Nice article and something I've done numerous times, though in past ive used it for seasons, especially usefull in the retail and the travel industries, where there are Spring/Summer, and Autumn/Winter derivations.

    The only thing I would suggest is that you set the Name column attribute on the ID hierarchy to to Calendar Date and change the name from Id to be Calendar date. This then makes the Calendar Date attribute hierarchy the key member, it also avoids confusion for the end user who will understand what a calendar date is but not an ID.

    SSC Guide to Posting and Best Practices

  • Great article - can you add the fact table as a resource to the example?


  • Would love it if someone could provide a desk calendar display output format in SSRS for the "Pointy Haired Bosses" to read....

  • Nice step-by-step example of managing a calander dimenions in SSAS. I think a good follow-on is to develop and show a stored procedure which can dynalically generate the underlying table (the .CSV file in your example) with all the columns appropriately populated and extend the accounting principle to a 4-4-5 calendar which is common in financial analysis. Let me know if you'd like an example which I have.


    P Goldy

  • Andrew - great article; however, I have a question re using Custom Calendar in MDX. In my scenario, I have created a cutstom calendar with Year/Quarter/Month/Week. All works great and data is displayed fine - but when I use the MDX with ParallelPeriod (ParallelPeriod([D_TIME].[H_DATE].[Year],1,[D_TIME].[H_DATE].CurrentMember),[Measures].[Cost]) and my previous year has incomplete data (for instance Dec 2009 only). This MDX compares January 2010 vs December 2009 and I need it to compare December 2010 vs December 2009. Using server build dimension works just fine with this MDX. Are custom calendars limited in functionality when it comes to MDX?

  • Using MDX with Custom Calendars is tricky - basically you have to avoid using MDX fuctions and simply write the code to lag back by the number of days or weeks to get the correct matching - MDX has a LAG function that you can use.:-P

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply