Multi-event Fact Table?

I have been assigned to create a Data Warehouse for my company. The business flow:

1. A spec is created for a client.
   Vendors provide quotes for all or selected items and shipping.
   Vendors are selected.
   An order is created.
   An invoice is sent out.
   

My question: I feel this is an event driven dimensional model - is it there for an accumulation snapshot granularity, and is it advisable to maintain all events (regardless of whether the vendor wins the order) in one fact table?

Thanks for your consideration.

Kirk

kirksipe wrote: ...is it advisable to maintain all events (regardless of whether the vendor wins the order) in one fact table?
Kirk

No. A hundred times NO!

Facts represent specific events, not any event.

I don't believe I asked this question a hundred times, but I am assuming your reply is that there should be a separate fact table for each type of transaction.yes?

For the most part. A fact reflects a particular business process. For example, deposits and withdrawals from an account can be considered different transaction types, but they are both part of the same process, so they would be carried in the same fact table. A mortgage payment, on the other hand, is related to a much different process and would be carried in a different fact table.

I'm not sure I agree with the example ngalemmo provided.

At a bank branch, ATM, online, or through the mail, I can make transactions on credit accounts and deposit accounts. I can make deposits or withdrawls on Deposit accounts. I can transfer money from one deposit account to another.

I can treat a line of credit similarly. I can withdraw money from the LOC or pay the LOC. I can transfer month from the LOC to a Deposit account and vice versa. Essentially, the LOC can be used exactly like a Deposit account.

Transactions involving Loans, including mortgages, are usually limited to to payments and transfers to the account, but like a LOC, I can transfer money from the deposit accounts to the credit accounts.

I have seen transaction fact tables include all of these types of transactions.

If there are situations where some types of transactions have more dimension attributes than others, then it might be neccessary to put the transactions into different fact tables based on how the transaction was performed, but even then, it is advantageous to create views that union the different transaction fact tables (how many transactions did customer A perform, and how were the transactions dispersed across the different mechnisms).

I don't think the type of transaction would determine the fact table, but how the transaction was performed.

However, I would not combine the different "transactions" occuring on a loan application with transactions against an account.

There are always exceptions... dimensional design is more art than science.

But, generally speaking, the example holds true when dealing with atomic level facts. Usually there are differences in granularity, measures, and the nature of updates and frequency that warrant separate facts. For example, you would not collect orders, shipping, and invoicing in the same atomic fact, but at the same time, having an aggregate across all three subject areas is very useful.