[AccessD] Warehouse design (was Mucking Around)

Thanks Robert,
I will digest all of that in due course.
Congratulations on your wedding.
I hope it all went well for you.
Regards
Max
 

-----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 2:37 PM
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

At 10:48 AM 9/28/2007, you wrote:
>Date: Fri, 28 Sep 2007 15:21:10 +0100
>From: <max.wanadoo at gmail.com>
>Subject: Re: [AccessD] Mucking around
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Message-ID: <019601c801da$d1b69000$8119fea9 at LTVM>
>Content-Type: text/plain;       charset="us-ascii"
>
>Robert, be careful now.  You are starting to drift away...
>
>Snowflake: This to me is a miltary policeman (RMP) so called because 
>they wore white hats.
>True Stars: I can understand this cos my staff often say "Max you are a 
>true star" (costs me a fortune each time).
>
>But, I *think* I am still with you.
>
>So, we have a Form which captures data.  (Is this the transactional 
>system you mention).  The code then writes each entry into a 
>Transaction Table (flat file) all data written regardless of 
>duplication in other data entries.
>
>The Transaction Table then forms part of the data mart.  There is no 
>lookup or linked tables. Everything is in the Transaction Table.
>
>Have I got it right so far?
>
>Max


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com