[AccessD] Mucking around

It's not inefficient for data STORAGE because that's what a data
warehouse does.  It would be inefficient to use this schema for data
ENTRY.

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 5:42 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Mucking around


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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com