Colby, John
JColby at dispec.com
Mon Feb 2 11:55:35 CST 2004
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