Multi-event Fact Table?

View previous topic View next topic Go down

Multi-event Fact Table?

Post  kirksipe on Fri Nov 09, 2012 11:03 am

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

Posts : 2
Join date : 2012-11-09

View user profile

Back to top Go down

Re: Multi-event Fact Table?

Post  ngalemmo on Fri Nov 09, 2012 11:55 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multi-event Fact Table?

Post  kirksipe on Fri Nov 09, 2012 12:17 pm

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?

kirksipe

Posts : 2
Join date : 2012-11-09

View user profile

Back to top Go down

Re: Multi-event Fact Table?

Post  ngalemmo on Sun Nov 11, 2012 6:55 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multi-event Fact Table?

Post  Jeff Smith on Mon Nov 12, 2012 1:04 pm

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.


Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Multi-event Fact Table?

Post  ngalemmo on Mon Nov 12, 2012 3:13 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multi-event Fact Table?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum