Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating Custom Calendars in SQL Server Analysis Services Expand / Collapse
Author
Message
Posted Monday, September 27, 2010 8:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 20, 2011 4:29 AM
Points: 4, Visits: 51
Comments posted to this topic are about the item Creating Custom Calendars in SQL Server Analysis Services
Post #994158
Posted Tuesday, September 28, 2010 3:20 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 1,887, Visits: 1,179
An excellent, simple, step-by-step post illustrating how to create user-defined custom time dimensions. Thanks and well done

Lempster
Post #994269
Posted Tuesday, September 28, 2010 7:07 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 7:37 AM
Points: 860, Visits: 2,323
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
Post #994394
Posted Tuesday, September 28, 2010 7:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 4:28 AM
Points: 36, Visits: 155
Great article - can you add the fact table as a resource to the example?

thanks
Post #994415
Posted Tuesday, September 28, 2010 8:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 9:19 AM
Points: 6, Visits: 37
Would love it if someone could provide a desk calendar display output format in SSRS for the "Pointy Haired Bosses" to read....
Post #994472
Posted Tuesday, September 28, 2010 8:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 14, 2014 8:42 AM
Points: 3, Visits: 35
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
Post #994500
Posted Tuesday, September 28, 2010 3:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 12, 2013 2:43 PM
Points: 3, Visits: 44
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?
Post #994842
Posted Wednesday, September 29, 2010 7:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 20, 2011 4:29 AM
Points: 4, Visits: 51
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.
Post #995217
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse