[AccessD] Data Warehouse (Was: Primary Key Best Practices)

Here is a quick overview, think cubes and hypercubes for storage not 2-D 
Tables

http://en.wikipedia.org/wiki/OLAP_cube

Yup Codd got into this too.


Charlotte Foust wrote:

> Can you give an example of a typical dimension table and what you mean
>by the source primary table?  I've worked primarily with date dimensions
>using the date as the PK.  
>
>Charlotte Foust
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert L.
>Stewart
>Sent: Thursday, July 26, 2007 11:04 AM
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] Primary Key Best Practices
>
>I have watched all the flaming and was surprised at the lack of
>intelligence and total stubborness in some of it.
>
>I do not know if what I do is best practice or not. But, I will go with
>consistency.
>
>In a transactional database, all tables have a surrogate, autonumbering
>primary key. They also have one or more unique indexes on the candidate
>key or keys. Also, indexes on all foreign key columns.
>
>In a data warehouse or data mart, all dimension tables will have a
>single column primary key inherited from the source primary table.
>All fact tables will have multiple columns that make up the primary key.
>There are no snowflakes. It is a true star schema.
>
>Best practice or not, as long as I am consistent in my designs, I will
>always know how to use it properly. And, I can always explain it to the
>monkeys that come after.
>
>Robert
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada