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

Charlotte Foust cfoust at infostatsystems.com
Thu Jul 26 15:59:09 CDT 2007


Yes, and I have several of Ralph Kimball's books on Data Warehouse
design and I've built a few, but what I was looking for was an
explanation of how a dimension table inherits a single column PK  "from
the source primary table."  I assume it means that all the distinct
values for that column that exist in the primary table have a matching
PK in the dimension table, but it took me a while to sort it out to
that, and I've *worked* with the things!

Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of MartyConnelly
Sent: Thursday, July 26, 2007 1:51 PM
To: Access Developers discussion and problem solving
Subject: Re: [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

--
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