Fact tables exist to optimize reporting (in a general sense). The main
reasons are:

1) Since it's loaded in batch there are no transactions; reportA will tie to
reportB
2) Queries don't degrade the operational system
3) Schema optimized for querying
4) Often contains data from multiple data sources

You might want to read one of Ralph Kimball's books.

tom @ the domain below
www.tomchester.net


"Mike Morse" <mike.morse (AT) micromo (DOT) com> wrote

Hi Mike,

I am afraid that I do not understand your detailed concerns here. Do you
mean that you think the design mentioned in your post is not good?
"I have a SalesOrder Master table which is parent to SalesOrderItems which
is parent to SalesOrderItemsRels (releases). SORels contains how many
items have been relerease per each SalesOrderItem ordered."

How would you design the data storage and think it is better? What separate
"sales fact table" do you need to create? In addition, you mentioned that
you take some OLAP tutorials. Could you post the detailed text and sample
in that OLAP tutorial which you do not think makes sense?


Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------

The design I mentioned is fine. What I don't agree with
is how the food mart samples has a "fact table" and the
logic that is behind it. This table is not how people
really store sales data with a join to a table containing
dates.

-mike

If you need to store attributes for dates, you need a table, data warehouse
or not. You could use the date as the PK/FK rather than an integer.

tom @ the domain below
www.tomchester.net


"MIke Morse" <mike.morse (AT) micromo (DOT) com> wrote

I have to agree with Tom. Look into Star schema modeling and the reasons
why you create a separate table for the time dimension and then use
integer keys in all your fact tables. You can put all kinds of data in the
time dimension table (calendar year, fiscal year, day of the week, holiday
flag etc...).

You design a data warehouse for querying and reporting and not for data
entry.

Mark
"MIke Morse" <mike.morse (AT) micromo (DOT) com> wrote

Hi Mike,

You could place all time data in one separate table and build a time
dimension, so that it can be reused by several cubes in the OLAP database.

A key characteristic of a fact table is that it contains numerical data
(facts) that can be summarized to provide information about the history of
the operation of the organization. Each fact table also includes a
multipart index that contains as foreign keys the primary keys of related
dimension tables, which contain the attributes of the fact records. Fact
tables should not contain descriptive information or any data other than
the numerical measurement fields and the index fields that relate the facts
to corresponding entries in the dimension tables.

Generally we do not base OLAP cube directly on raw data entered. We will
first map it to the format we desire. Certainly we could also place time
directly in the fact table. You may compare the performance and management
easiness between these two approaches.


Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------