[AccessD] RE: Using Dates

Charlotte Foust cfoust at infostatsystems.com
Mon Feb 2 14:29:49 CST 2004


I'm not even sure what Ralph is saying there, and it flies in the face
of what he does in some of his books, so I have no answer for you, John.
Keep in mind, though, that datawarehouses are indexed somewhat
differently from transactional databases, so that may be where the
confusion is coming in.  If you were using surrogate keys and
meaningless values as the PK, the dates in the main records would need
to be inserted *from* the date table, and that isn't generally the way
the thing is used.

Charlotte Foust

-----Original Message-----
From: Colby, John [mailto:JColby at dispec.com] 
Sent: Monday, February 02, 2004 9:56 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] RE: Using Dates


Charlotte,

>I'm not sure what you mean about the meaningless pk in this regard.  
>You
could use an autonumber and make the date field a unique key, but I
don't know what that would buy you.  You wouldn't ever join a date time
like that.


Per the quoted text from Gustav:

(Ralph Kimball?)

The calendar date primary key ideally should be a meaningless surrogate
key but many ETL teams can't resist the urge to make the key a readable
quantity such as 20040718 meaning July 18, 2004. However as with all
smart keys, the few special records in the calendar date dimension will
make the designer play tricks with the smart key. For instance, the
smart key for the inapplicable date would have to be some nonsensical
value like 99999999, and applications that tried to interpret the date
key directly without using the dimension table would always have to test
against this value because it is not a valid date.

John W. Colby
The database guy


-----Original Message-----
From: Charlotte Foust [mailto:cfoust at infostatsystems.com]
Sent: Monday, February 02, 2004 12:36 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] RE: Using Dates


The PK of the dimension table is the date itself.  I'm not sure what you
mean about the meaningless pk in this regard.  You could use an
autonumber and make the date field a unique key, but I don't know what
that would buy you.  You wouldn't ever join a date time like that.  The
calendar date table is for dates, not time, although I suppose it could
be extended if you were into pain.  This kind of table is typically used
where the user may want to slice and dice data on the fly.  In that
case, the date fields in the data tables are already indexed for that
purpose. 

Charlotte Foust

-----Original Message-----
From: Colby, John [mailto:JColby at dispec.com] 
Sent: Monday, February 02, 2004 9:19 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] RE: Using Dates


lol.  All I needed to know is that you join any date (use the date as
the PK in the date table).

I notice that the proponent whazizname doesn't recommend this though,
rather using a "meaningless" pk (autonumber?).  When it comes back to
this it becomes less obvious how to use it.  

I can easily build a date() as a default value for a field for example.
However having to lookup that date and get the PK of the record matching
that date... a little less obvious.

I will no doubt look at this though.  I can see just thinking about it
that it isn't the end all / be all, even speed wise.  For queries that
only return 1 or a handful of records, using direct functions to build
some piece of the date would be MUCH faster than doing a join on this
table, pulling the index for 10,000 date records etc.  And there is
always the issue of joining a date directly when it has a time component
- now() instead of date().

Interesting none the less.

John W. Colby
The database guy


-----Original Message-----
From: Charlotte Foust [mailto:cfoust at infostatsystems.com]
Sent: Monday, February 02, 2004 11:57 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] RE: Using Dates


Ah, a convert! <VBG>

Charlottet Foust

-----Original Message-----
From: Colby, John [mailto:JColby at dispec.com] 
Sent: Friday, January 30, 2004 11:06 AM
To: 'Robert L. Stewart'; accessd at databaseadvisors.com
Cc: Colby, John
Subject: [AccessD] RE: Using Dates


I think I finally "got it".  This is joined to any date to pull out the
various pieces needed?  Then you build a function to populate this thing
for the dates that your app needs?  

I think I might just appreciate this one!  ;-)

John W. Colby
The database guy


-----Original Message-----
From: Robert L. Stewart [mailto:rl_stewart at highstream.net]
Sent: Friday, January 30, 2004 1:58 PM
To: accessd at databaseadvisors.com
Cc: JColby at dispec.com
Subject: Re: Using Dates


Actually, you have a "standard" date dimension table that everyone uses
and 
the overhead is minimal.

At 12:00 PM 1/30/2004 -0600, you wrote:
>Date: Fri, 30 Jan 2004 12:58:44 -0500
>From: "Colby, John" <JColby at dispec.com>
>Subject: RE: [AccessD] Using Dates
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Message-ID: <05C61C52D7CAD211A7830008C7DF6F1079BD12 at DISABILITYINS01>
>Content-Type: text/plain;       charset="iso-8859-1"
>
>I have to assume though that you only create these as required for 
>reporting?  The overhead would be enormous otherwise.
>
>John W. Colby


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


More information about the AccessD mailing list