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

Advantages of Time Dimension Expand / Collapse
Author
Message
Posted Monday, June 14, 2010 7:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 10, 2011 8:31 AM
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
Post #936804
Posted Monday, June 14, 2010 11:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 AM
Points: 2,817, Visits: 2,563
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.
Post #937029
Posted Monday, June 14, 2010 12:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 10, 2011 8:31 AM
Points: 5, Visits: 34
Thanks! Let me check ...
Post #937048
Posted Tuesday, June 15, 2010 3:07 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 7:25 AM
Points: 347, Visits: 1,344
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
Post #937328
Posted Wednesday, June 16, 2010 6:30 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:39 PM
Points: 670, Visits: 6,720
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
Post #938109
Posted Friday, June 18, 2010 3:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 10:16 AM
Points: 7, Visits: 301
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
Post #939872
Posted Friday, June 18, 2010 8:24 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:39 PM
Points: 670, Visits: 6,720
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
Post #939914
Posted Saturday, June 19, 2010 5:06 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 7:25 AM
Points: 347, Visits: 1,344
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
Post #939952
Posted Monday, June 21, 2010 1:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 10:16 AM
Points: 7, Visits: 301
Thanks for the replies. I'm not sure I fully understand, but I will try some things related to your suggestions.

Jim
Post #940594
Posted Monday, June 21, 2010 3:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:03 PM
Points: 124, Visits: 488
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.
Post #940668
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse