Inspired by questions frequently posted here, here's an article about why
it's important to have a time dimension table:

http://www.tomchester.net/articlesdo...s/timedim.html


tom @ the domain below
www.tomchester.net

Hi Tom,

Thank you for taking time to write in your experience so that it may help
other customers who may encounter similar issues.

Thanks for participating the community.

Thanks & Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Tom -



I'm working on an application with a requirement that time be
grouped by:



last 6 Months

last 12 Months

last 18 Months

last 24 Months



I have a time dimension very similar to the one in your article, but
with 4 additional fields I use to make a separate hierarchy.



24M_value

18M_value

12M_value

06M_value



24M_value == 24 for each time record in the last 24 months, NULL
otherwise.

18M_value == 18 for each time record in the last 18 months, NULL
otherwise.

etc.



In your opinion, is this a bad design?

Do you think the NULL values will cause problems as the dimension grows?

So far it has worked well and has helped to simplify a lot of queries.


--
Posted via http://dbforums.com

It's a bit unusual -- since these change each month you have to re-process.
I'd have been tempted to try named sets, though it depends on front-end if
named sets are usable. Once user looks at a total for last 18 mos (for
instance), do they then need to drill down into the months?

tom @ the domain below
www.tomchester.net


"__SH__" <member47234 (AT) dbforums (DOT) com> wrote

No. No drill down is required. The biggest requirement is speed.



Essentially what I'm doing is counting occurrences. For example, a
typical query might be: Give me the number of Customers that spent more
than $1000 in the last 18 months. I've been able to get it to perform
well using the technique I described.



For some reason, fast access to the last 6, 12, 18 and 24 months is
important to someone.



Anyway, thanks for your help. This whole thing is making me think about
going back to the requirements.









--Originally posted by Tom Chester--

It's a bit unusual -- since these change each month you have to
re-process.

I'd have been tempted to try named sets, though it depends on front-end
if

named sets are usable. Once user looks at a total for last 18 mos (for

instance), do they then need to drill down into the months?



tom @ the domain below

www.tomchester.net


--
Posted via http://dbforums.com