Charlotte Foust
cfoust at infostatsystems.com
Fri Sep 28 11:06:49 CDT 2007
Robert is the person to answer your questions, but a data warehouse is for reporting, nothing else. So it's set up to allow you to slice and dice and examine information in all sorts of ways at top speed. It is NOT designed for you to enter data. In fact, the data doesn't even have to come from the same database, when you get right down to it. So you could build a data warehouse, move data into it from your existing database, and then analyze and report on the data from the data warehouse. Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of max.wanadoo at gmail.com Sent: Friday, September 28, 2007 9:02 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Mucking around Ha, Chalotte, thought so, "a mess" is usually where I end up anyway. So, if I wanted to do this in MS Access, what are the major steps, or is this something that should not be considered in Access? I guess I don't really comprehend what is meant by "from the Transaction System", or what is a "Transactional System", or what is a "Mart" or what is meant by "loads data from" - why cannot the data entry just post straight to the "Mart". Big leaning curve here. Ta Max -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Friday, September 28, 2007 4:52 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Mucking around Transactional systems are separate. The transformation loads the data from the transactional system into the data warehouse/mart. If you try and mix the two systems, you'll have a mess. Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of max.wanadoo at gmail.com Sent: Friday, September 28, 2007 7:21 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Mucking around 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 -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Robert L. Stewart Sent: Friday, September 28, 2007 3:06 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Mucking around Max, What you would normally have is an ETL (Extract-Transform-Load) process that would get the data from the transactional system and load it into the mart/warehouse. The data is usually transformed in some way. In the dimension tables, all of the associated tables to the main dimension are used to feed in the additional de-normalized data into the single dimension table. If you do not do this, it is called snowflaking. And, this causes poor performance from the mart/warehouse. Marts/warehouses are not designed for efficient data storage. They are designed for the most effective reporting possible. In some cases, summarizing years of data into a few thousand records. No data entry would ever be done to the mart/warehouse tables. They are always loaded via the ETL processes defined as part of the overall strategy the business wants to use for reporting. I am also glad that I was able to explain it so that you were able to get the concepts. It is usually difficult for "transactional" people to get the concepts of a true star schema design. And they almost always want to build snowflake schemas instead of true stars. Robert At 08:42 AM 9/28/2007, you wrote: >Date: Fri, 28 Sep 2007 13:41:35 +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: <011c01c801cc$e8a717c0$8119fea9 at LTVM> >Content-Type: text/plain; charset="us-ascii" > > >Hi Robert, >Believe it or not, I can actually understand it the way you described it. >So, using your example, the sales would feed into the Fact Table and >Reports would be run on the Fact table and pull info from the Customer >Table. Is this correct? >Am I also correct in saying that this schema is highly inefficient in >terms of data storage but highly efficient in terms of speed of reports? > >Ta > >Max -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com