[AccessD] Re: DatePart Question

I think you guys are missing the point.  You're dealing with specific
dates in  [MyDateField] Between #5/1/1999# And #5/31/1999.  But if
someone just wants to see a particular month's data, you can specify
MonthNumberSinceStart and immediately filter out all the records where
their date values have the same MonthNumberSinceStart value in the Date
dimension table.   You don't have to compare [MyDateField] to anything
because it's already joined to the dimension table.

Charlotte Foust

-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk] 
Sent: Wednesday, February 04, 2004 11:44 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Re: DatePart Question


Hi Charlotte

> 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.

/gustav


> I understand more 'complex' date 'info', such as holidays, fiscal 
> period, etc.  I still don't understand the reason for basic date 
> information, such as day, month, year, day of week, etc.  I have never

> actually run any tests, but my gut says that a query where I wanted 
> all records in the month of May (ANY year), that if I put 
> Month([MyDateField])=5 in the Where clause, that it would be faster 
> then having a relationship to a date dimension table.

> Data entry, or data warehousing, the speed should still be a factor, 
> correct? Or am I way off on my gut feeling (really too busy to build 
> an appropriate test.)

> Drew

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte 
> Foust
> Sent: Tuesday, February 03, 2004 4:38 PM
> To: Access Developers discussion and problem solving
> Subject: RE: [AccessD] Re: DatePart Question


> The essential difference is that the date dimension table is generally

> used in data warehouse applications, not in regular data entry 
> databases.  They can be useful in the latter for reporting purposes 
> only, but you can get by nicely if you've never had to slice and dice 
> very large tables based on a bunch of date criteria.  Note that fact 
> tables in a datawarehouse are usually not normalized in the same way 
> as regular database tables either (they are commonly 1NF), so 
> dimension tables give you flexibility that it's hard to get any other 
> way.  Data warehousing may be slightly off-topic, but only because 
> Access developers don't have to deal with it very often.  I have, so I

> can appreciate both sides of the discussion.

> Charlotte Foust

> -----Original Message-----
> From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com]
> Sent: Tuesday, February 03, 2004 2:05 PM
> To: accessd at databaseadvisors.com
> Subject: RE: [AccessD] Re: DatePart Question


> Nothing personal, but I have to agree with Gustav's point of view.  I
> can see (and I believe he does too), where a table would help certain
> situations.  However, I know first hand, the extreme lack of
> understanding on how a date works.  I'm not saying you don't
understand
> that, however, to a computer, it is MUCH faster for many functions, to
> just let the processor do a logic operation on a number, then to have
it
> pull other data up, and compare that.  Holidays, etc, those require
> heavier logic, so a table could be faster (depending on the amount of
> data).

> I'm not knocking your approach.  But in my experience, I have never
> needed to do anything like that.  

> We better be careful that this doesn't turn into another bound/unbound
> issue.

> Drew

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com