Creating Custom Calendars in SQL Server Analysis Services

Question

Creating Custom Calendars in SQL Server Analysis Services

ANDREW.MUYOBO

SSC Enthusiast

Points: 190
More actions
September 27, 2010 at 8:45 pm

#241019

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

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

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

Lempster SSCoach Points: 15651 More actions · Answer 1

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

Lempster

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 2

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

naveendas Mr or Mrs. 500 Points: 590 More actions · Answer 3

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

thanks

dgilman@tamu.edu SSC Journeyman Points: 90 More actions · Answer 4

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

pgoldy SSC-Addicted Points: 486 More actions · Answer 5

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.

PaulG

P Goldy

AnnaR SSC Rookie Points: 35 More actions · Answer 6

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?

ANDREW.MUYOBO SSC Enthusiast Points: 190 More actions · Answer 7

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