June 30, 2008 at 11:20 pm
hi,
Im tried to roll up from dates->weeks->months->quarter->year
But the date->week rollup is not possibel for me......
I have a fact table having the values
date
month
week
year
...
....
seperate table for week,month and year pointing to the fact table as a primary-foreign key realtionship.
Now the dates-> to week didnot roll up properly...Any idea on how to acheive this..When i searched through the web,i found many of them told its not possible....Any ideas mate....
July 1, 2008 at 5:14 am
Are you using a fiscal calendar, or a gregorian calendar?
In a gregorian calendar, weeks to months is a many-to-many relationship so you cannot create a hierarchy.
July 1, 2008 at 6:24 am
hi,
Actually am using the values of the fact table..not using any server time dimension what you have specified.....I had splitted out the date column 27/03/2007 into three columns in fact table...and also have the three look up table for dimension of date,month and year respectively.
I had tried the server time dimension,but i dont know how to link those to the actual fact values...How to use server time dimension in my case...Any ideas mate.
Thanks & Regards
vijay
July 2, 2008 at 7:22 am
While I have not used analysis services yet (but will soon), I have used a variety of OLAP and BI tools, some of which provide for creation of dimensions with alternate roll-ups (one for months, one for weeks). You should check to see if this is available in analysis services or via built-in calendaring functionality.
If this is not available, you will need to create separate dimensions as follows:
Date > Month > Year
Date > Week > Year
July 2, 2008 at 8:03 am
Once again, we need to remind people to provide more detaiuls on their problem.
Normally, your "fact" table will have one or more measures (like dollars, units, price, etc.) and be keyed by a time dimension table (i.e. date). From your description, this looked like a time dimension table, with the attributes month, week, year, etc.
Create your dimensin table (myDate) and make sure it references the key of the the fact table. The attributes will create the proper rollups.
Of course, there's many instances where you could treat dates as facts or measures, but I won't go into that here.
A special consideration comes up when you're dealing with non-additive measures (like Price). Price won't sum properly (obviously). Non-additive measures require additional thought:
- Examine your assumptions. Price (or Avg Price) is better calculated as Sum(Dollars) / Sum(Units).
- Think about your measure. Can it be broken down into two, additive facts, and then resolved as a calculation (i.e. Sum(Denominator) / Sum(Numerator). This is very often the case.
- There's additional handling ytou can implement via SCOPE statements. I find it helpful to think of SCOPE statements as "overrides", where I'm replacing default aggregations under certain conditions. (SCOPE only affects "physical storage", so it only works against real measures, not calculated measures.)
Special note: often, people attempt to use AverageOfChildren. Check the documentation carefully. It only applies to a time dimension.
I hope this helps.
July 7, 2008 at 3:34 am
It may be pretty complicated. The problem may be because of having overlapping weeks accross month(s) and year(s) etc..?!
May be u can think of having week start date, week end date also in your dimension table and try joining for getting unique combination.
July 7, 2008 at 8:04 am
You're right, that may be the error / problem they caused:
When you "tell" MSAS (via the dimension definition) that there is a strict hierachy (i.e. date rolls up to week, which rolls up to month , etc.) you've actually given MSAS misleading information. Weeks do not roll to months (obviously).
Now you're reported that dates did not roll up top weeks properly. What specifically are the vaklues for "dates" and what are the values for "weeks". What does your data model look like? What attribute relationships did you implement? (MSAS doesn't check your attribute relationships, just uses them to optimize the calculations.)
Note: you don't need separate dimension tables. It's actually quite easy.
The "server time" dimension" allows you to auto generate the time dimension, instead of having to persist the dimension as actual data. If you're new to OLAP and/or MSAS, I'd suggest building your time information manually. It'll help crystalize your experience.
Normally, your fact table should be "keyed" by a time field (i.e. "date"), as well as other fields like item, location, etc. Then your time dimension would be keyed by "date" and have attributes like week, week or year, year, month, etc.
I suggest you most your MSAS data source view and time dimension definitions. Make sure you explode out the critical attribute relationships. (screen caps are ok.)
July 8, 2008 at 9:45 am
As someone has pointed out, if you're using a straight Gregorian, there's little you can do. You could break each month down to five weeks, 1-7, 7-14 etc, with the fifth week being a rump week. If you want every week to be Sun-Sat, you can add that, but it will be on it's own. You can't even roll it up to a year.
Some companies have artificial months of 4-4-5 weeks. Some months don't begin exactly on that month start and the year often doesn't start on 1/1, but for warehouse purposes that system works well.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply