[AccessD] Warehouse design (was Mucking Around)

Congratulations on the wedding! 


John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert L. Stewart
Sent: Tuesday, October 02, 2007 9:37 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Warehouse design (was Mucking Around)

No.  The transactional tables are always part of a totally different system.

You use a process of some kind to load the data from the transactional
system, say orders, into the data mart.

tblCustomer                 tblCustomerType
CustID                      CustomerTypeID
CustName                    CustomerTypeDesc
CustTypeID
PmtTermsID

tblCustomerAddress          tblPmtTerms
CustAddressID               PmtTermsID
CustID                      PmtTermsDesc
AddressTypeID
AddressLine1                tblAddressType
AddressLine2                AddressTypeID
City                        AddressTypeDesc
State
PostalCode


tblOrder                    tblOrderDetail
OrderID                     OrderDetailID
CustID                      OrderID
ShippingMethodID            ProductID
OrderDate                   QtyOrdered
ShipDate                    QtyShipped

tblProducts                 tblShippingMethod
ProductID                   ShippingMethodID
ProductName                 ShippingMethodDesc
QtyOnHand
QtyOnOrder
ReorderPoint
LastCost
SalesPrice

Assuming the above as our transactional system and all the data entry would
be done in the above tables.

Our star schema would look something like this:

dtblCustomer                 dtblDates
CustomerID                   DateID
CustName                     YearText
CustTypeDesc                 YearNbr
PmtTermsDesc                 MonthText
ShippingAddressLine1         MonthNbr
ShippingAddressLine2         MonthAbrev
ShippingCity                 MonthYearText
ShippingState                YearMonthText
ShippingPostalCode           DayText
BillingAddressLine1          DayNbr
BillingAddressLine2          DayOfYearNbr
BillingCity
BillingState
BillingPostalCode

dtblProducts                dtblShippingMethod
ProductID                   ShippingMethodID
ProductName                 ShippingMethodDesc

ftblOrders
CustID
DateID
ProductID
ShippingMethodID
TotalQtySold
SalesPrice

In ftblOrders the PK would be CustID, DateID, ProductID, ShippingMethodID.
Notice in dtblCustomer, the address information has been denormalized so
that we do not have to join to the address table.  If we did not do that, it
would be called a snowflake.

Sorry that I did not get back to you quicker. I would out of touch with
email due to my wedding :0)

Robert