[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