June 14, 2010 at 7:09 am
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
June 14, 2010 at 11:35 am
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.
June 14, 2010 at 12:09 pm
Thanks! Let me check ...
June 15, 2010 at 3:07 am
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
June 16, 2010 at 6:30 am
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
June 18, 2010 at 3:19 pm
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
June 18, 2010 at 8:24 pm
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
June 19, 2010 at 5:06 am
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
June 21, 2010 at 1:24 pm
Thanks for the replies. I'm not sure I fully understand, but I will try some things related to your suggestions.
Jim
June 21, 2010 at 3:53 pm
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply