When to use multiple_fact table ?

View previous topic View next topic Go down

When to use multiple_fact table ?

Post  peace1aparna on Mon Feb 21, 2011 6:58 pm

I am very new to data modeling, and I am trying to built a design for retail operation

Say it will have a sale transaction and refund transation (but sales transaction receipt and refund transacation receipt) are completely different operations. But again using the refund receipt my report should be able to track old sale receipt (refund receipt have the information of old_receipt_number and old_sales_date). In this scenario, do I need to build two fact tables, each for refund and sale or will one fact table will work?

All of the process are identified by codes say (IT) - all item sold, (RF) all refunds, PM price modifier,
So the report should show like
How many refunds per location, employee per day?


I am very new to DW so would really appriciate your replies..

peace1aparna

Posts : 7
Join date : 2011-02-21

View user profile

Back to top Go down

Re: When to use multiple_fact table ?

Post  Jeff Smith on Tue Feb 22, 2011 10:13 am

I would do 1 fact table. By having both sales and refunds in the same fact table enables you to count total transactions and calculate net sales from the same fact table.


Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: When to use multiple_fact table ?

Post  peace1aparna on Tue Feb 22, 2011 4:28 pm

Thankx Jeff, for your reply

I have another question, for now each transaction (not line item) is uniquely identified by the combination of date, location Id, register Id and transaction Id (different registers can generate same transaction id).

And in the fact table i have the location-sk and date-sk as the foreign key which are the surrogate key from location_dim and date_dim. So to uniquely identify a transaction, do I need to add receipt_id and transaction_id as degenerate dimension ??? Open to any other ideas too ...

Thankx.

peace1aparna

Posts : 7
Join date : 2011-02-21

View user profile

Back to top Go down

Re: When to use multiple_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