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


Advantages of Time Dimension


Advantages of Time Dimension

Author
Message
amir_ayub
amir_ayub
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: 34
Hi,
Please help me to understand about time table/dimension, I have below specific questions:
a) Is this the builtin table in Management Studio or do I have to create one?
b) Why we need to create Time table in management studio (then create time dimension)?
I know it's very basic question but please send me some link which will help me to understand in deail.
Regards,
Amir
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2950 Visits: 2629
I suggest you read this article:
http://www.sqlservercentral.com/articles/T-SQL/70167/
and even more importantly read the discussion of the article. Personally I lean toward the general opinion of the discussion rather than the article, but together I think they give pretty full treatment to the Time Dimension.
amir_ayub
amir_ayub
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: 34
Thanks! Let me check ...
denglishbi
denglishbi
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 1362
Assuming you are talking about a Date dimension. Within BIDS there are options when creating an SSAS project to have the tool build a Date (Time) dimension for you. Here is an article that shows one of the options http://www.ssw.com.au/SSW/Standards/Rules/CreatingATimeDimensionIn10EasySteps.aspx.

You need to define a dimension in your SSAS database as type Time and associate the attribute hierarchies to their types in order to utilize Time based function like YTD, MTD, ParallelPeriod, PeriodsToDate, and the built-in Time Intelligence functionality.

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Greg Edwards-268690
Greg Edwards-268690
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 8252
We'd have to build a new time dimension anyways.
We use a 4-4-5 week setup, with 52 weeks in the year. The extra days vary year to year, put into the first and last weekly buckets.
Building a Time Dimension is a good way to get familiar with cube structure. You quickly learn some sort by key or value principles.
Greg E
jim pierre
jim pierre
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 339
I am working on a cube that uses the 4-4-5 accounting method. I have my time dimension setup and working properly. The problem I have is that every 5 years there are 53 weeks in the fiscal year. Can someone help me figure out how to handle comparable sales when one year has 52 weeks and another has 53 weeks.

Year 2010 has 52 weeks
Year 2009 has 53 weeks
When comparing weekly (daily, quarterly, etc.) sales of 2010 with 2009 the calendar days offset by one week.

For example:
Week 1 of 2009 is Dec 29 2008 - Jan 4 2009
Week 1 of 2010 is Jan 4 2010 - Jan 10 2010

However, when I compare week 1 2010 with week 1 2009 I need to compare it with week 2 of 2009 (Jan 5 2009 - Jan 11 2009) because of the 53 weeks in fiscal year 2009. Currently my calculated member compares week 1 2010 with week 1 2010. Normally this would be great, but in the case of a 53 week year.

If possible, I would like this to built into the cube by modifying my time dimension so complicated MDX doesn't have to be written for cube browsing.

Thank you for your ideas.

Jim
Greg Edwards-268690
Greg Edwards-268690
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 8252
Exactly why coroporate decided to do the first and last week of the year the way they did.
You avoid this for the most part.
Hopefully someone better at MDX can give an example of how they deal with it.
I would like to see how they deal with it.
Greg E
denglishbi
denglishbi
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 1362
You could possibly setup a column in your date dimension table that would specify the previous year member that would be used for reference in your calculations. This would then we configured as a member property in the dimension that could be used in your calculation.

The other way that I could think of would be to configure a SCOPE statement in the cube that would evaluate the current week member you are looking at. Based on this it could determine the number of weeks that are included in that member's year (52 or 53) and then you could determine if you want to look at the equivalent member in the parallel period or the next member in the parallel period.

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
jim pierre
jim pierre
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 339
Thanks for the replies. I'm not sure I fully understand, but I will try some things related to your suggestions.

Jim
tmitchelar
tmitchelar
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 508
There are typically a few attributes in the date dimension that are custom made and specific to that particular organization. These attributes include fiscal calendar columns and holiday/workingday indicator flags. Some of these attributes can be vital to an organization's analytics and is why a separate Date dimension that is made by the developer is needed.
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