[AccessD] Warehouse design (was Mucking Around)

max.wanadoo at gmail.com max.wanadoo at gmail.com
Tue Oct 2 09:27:14 CDT 2007


Thanks again, Robert

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:54 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Warehouse design (was Mucking Around)

Max,

I sent an example to the list a little while ago. I think that will help you
with the design. But, to expand on it further.

ftblOrdersByWeek
CustID
WeekOfYear
YearText
ProductID
ShippingMethodID
TotalQtySold
SalesPrice

ftblOrdersByMonthyear
CustID
MonthYearText
ProductID
ShippingMethodID
TotalQtySold
SalesPrice

ftblOrdersByYear
CustID
YearText
ProductID
ShippingMethodID
TotalQtySold
SalesPrice


Now, there are additional fact tables that still use the same dimensions but
are now summarized differently. This is the advantage of a mart/warehouse.
All the math is already do. The data is stored at the granularity you want
to report at. Granularity is almost always related to the time dimension of
your facts.

ftbl - fact table
dtbl - dimension table

Robert

At 01:52 PM 9/28/2007, you wrote:
>Date: Fri, 28 Sep 2007 19:21:39 +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: <020e01c801fc$6ad269a0$8119fea9 at LTVM>
>Content-Type: text/plain;       charset="us-ascii"
>
>Thanks John,
>Normalised data I understand.
>What I don't understand is how we get from that to Data Marts.
>
>Q1: What do I  do with my normalised tables.  If the answer is to leave 
>the data in the normalised table and then re-post it to a flat table, 
>then why could that not have been done at data entry.
>Q2: If the answer to Q1 is go straight to flat tables, then what do I 
>do AFTER that.
>
>When it then comes to pulling the data out into reports are we talking 
>about using software other than Access?
>
>The terminology is throwing me a bit too (well, to be honest, it is 
>throwing me a lot).
>With the help of you guys, I will undertand it eventually.
>
>What is conceptually throwing me at the moment though is this:  If the 
>reason people use datamarts (Star/Snow) to quickly create reports which 
>dice/slice down through the data, then are we or are we not just moving 
>the "time Taken" from the report stage to the data input stage (which 
>would make sense to me).  But if I am completely wrong here, then I 
>really am "all at sea!"
>
>Thanks
>Max


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