[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