Gustav Brock
gustav at cactus.dk
Thu Feb 5 08:49:21 CST 2004
Hi Robert > Your example would not return the same data Charlotte's would. > You would return all months. .. Ehh (ignoring tiny typy), not here ... why do you think so?? > Hers would only return May of the years specified. Yes. > A date and/or date/time dimension is essential in data > warehousing, period. As for the use in a transactional > system, personal preference. As for the use in reporting > systems, it is a really good idea. That is what data > warehouses and data marts are for and where the date > dimension shines. OK. As I have stated, I have no experience in this area, so I believe you. /gustav >> > You're way off, Drew. <G> The reason for those divisions is because >> > data warehouses are used to query data, period. So the user wants to >> > see all fact records within a particular month for the past 5 years. >> > The point of a dimension table is that you don't need to do any date >> > calculations in the query. All you need do is specify the month number >> > (no function required), and the year number. The join filters out the >> > appropriate records. You need do no date math at all to filter the >> > records because any date math was done when the dimension table was >> > created. Since SQL is much faster than code, why would you expect this >> >> > Month([MyDateField]) = 5 And Year([MyDateField]) BetweenYear(Now())-5 >> > And Year(Now()) >> >> > to be faster than this >> >> > [MonthNo]=5 And YearNo Between 5 And 10 >> >>Because of the missing inner join, of course. Also, for this example, >>you could state: >> >> [MyDateField] Between #5/1/1999# And #5/31/1999 or >> ... >> [MyDateField] Between #5/1/2004# And #5/31/2004 >> >>But I'm guessing. I neither have a huge set of data to test with nor - >>as Drew - the time at the moment to play with it. So I'll have to rely >>on the opinion from people having experience in this area.