[AccessD] Mucking around

max.wanadoo at gmail.com max.wanadoo at gmail.com
Fri Sep 28 07:41:35 CDT 2007


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


-----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 1:25 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Mucking around

Jim,

When you are dealing with data marts/warehouses, most normalization rules go
out the window.

A fact table is highly normalized. A dimension table is highly denormalized.
It is simply how they work and how they are correctly designed.

For instance in a customer dimension table, you would store all the
information about the customer. You would denormalize it to put the address
and phone information in the same table with the customer name. The same
with the shipping terms for the customer. Essentially, all the information
that you might ever want about a customer will be in the one table.

The dimension tables are then linked to the fact table with the PK. The fact
table is extremely specific data at a specific granularity. For example,
sales by customer for a specific product on a weekly basis. The weekly is
the granularity of the fact. There would be at least 3 columns in the PK of
this fact table, customer ID, product ID, and Date ID. The last one would be
used to identify the week the sales occurred in.

If the granularity of the fact is the same for your data, having a Entry
type of actual or trial as part of the fact is perfectly fine.
It has to be part of the PK for the fact.

Robert

At 11:54 PM 9/27/2007, you wrote:
>Date: Thu, 27 Sep 2007 15:55:55 -0500
>From: "Hale, Jim" <Jim.Hale at FleetPride.com>
>Subject: Re: [AccessD] Mucking around
>To: "Access Developers discussion and problem solving"
>         <accessd at databaseadvisors.com>
>Message-ID:
>
<FBEA4C39D7934A47A057CDFD3CF070EF4CAED7 at corp-es01.fleetpride.local>
>Content-Type: text/plain;       charset=us-ascii
>
>
>But is it normalized? Seems to me that fldType has the same problems as 
>described in the list box article. As a practical matter I would NOT 
>create separate tables since different rollups are needed all the time 
>and the programmers would poison my coffee if I proposed separate 
>tables. I have already lost too many tasters that way. Along the same 
>vein you COULD add a type field to the general ledger balance file to 
>represent plan, actual or statistics but I would never combine those 
>disimilar types of data. So the line beween correct normalization and 
>practicality often blurs for me. I think there is some grey and 
>judgement in creating truly normalized table schema. After all who can 
>truly say what is Normal? :-)
>
>Jim Hale


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