Robert L. Stewart
robert at webedb.com
Tue Oct 2 08:36:32 CDT 2007
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