Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating Custom Calendars in SQL Server Analysis Services


Creating Custom Calendars in SQL Server Analysis Services

Author
Message
ANDREW.MUYOBO
ANDREW.MUYOBO
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 51
Comments posted to this topic are about the item Creating Custom Calendars in SQL Server Analysis Services
Lempster
Lempster
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2683 Visits: 1657
An excellent, simple, step-by-step post illustrating how to create user-defined custom time dimensions. Thanks and well done :-)

Lempster
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
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
naveendas
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 166
Great article - can you add the fact table as a resource to the example?

thanks
Don Gilman, P.E.
Don Gilman, P.E.
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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....
pgoldy
pgoldy
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 43
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
AnnaR
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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?
ANDREW.MUYOBO
ANDREW.MUYOBO
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.:-P
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