Charlotte Foust
cfoust at infostatsystems.com
Thu Sep 2 10:55:43 CDT 2004
Dimension tables hold the keys that allow you to slice and dice data warehouse data. It's a little hard to get your head around the first time, but after that it makes perfect sense. Time is a fairly straightforward example. Your dimension table would contain probably a datetime field as the PK and for each date, it might also have a field for month number (overall), day number (overall), day in year, month in year, year, etc. Then you would join that table to each of the date fields in your fact tables and you could easily filter out records for the past 6 months or for dates in a range or where this date was 6 months earlier than that date. It sounds ideal for the data you're working with. Russell Sinclair has several books out on datawarehousing and I highly recommend them as primers. They also include working examples. Charlotte Foust -----Original Message----- From: John W. Colby [mailto:jwcolby at colbyconsulting.com] Sent: Wednesday, September 01, 2004 8:30 PM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] VLDBs, the saga - Ramblings only Charlotte, I intend to do some experiments and calculations. What do you mean by dimension tables? Are you talking about what we call lookup tables, i.e. a state table with a PK, replacing the state text with a FK relating back to the lookup table? I thought about this and can't decide whether the time to perform the join on 65 million records would completely outweigh any benefit. I will have to index some of these fields in order to do the slicing and dicing. Any true / false from what I know don't benefit from indexes since there are only two values. Any suggestions for how to speed this up are welcome. John W. Colby www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Monday, August 30, 2004 11:44 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] VLDBs, the saga - Ramblings only >From what John has described, he has, in essence, a data warehouse. It could certainly be broken up into one or more fact tables with dimension tables to make the slicing and dicing easier, but you still wouldn't wind up with "normal" normalization, just 1NF tables. I've worked with this kind of data on a smaller scale (I used to work for a company that would have been a customer of that kind of list). I suspect the demographics they're using as selection criteria should probably be put into dimension tables, which will actually make the database bigger but will make the queries MUCH faster and easier. Charlotte Foust -----Original Message----- From: Shamil Salakhetdinov [mailto:shamil at users.mns.ru] Sent: Monday, August 30, 2004 6:49 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] VLDBs, the saga - Ramblings only John, I didn't know they are these simple T/F... Why your database is getting so huge then? Do you have any explanations/calculations? _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com