Order Header fact and multiple payment methods

View previous topic View next topic Go down

Order Header fact and multiple payment methods

Post  AKris on Wed Mar 03, 2010 11:24 pm

I need some help in designing this particular situation. Here are some details:
1)The grain is one row per order
2)Each order can have multiple payment methods ex: cash,credit card,customer account,due bill,gift card
3)A combination of payments is possible.
ex: credit card and gift card,multiple credit cards,multiple cash payments (ex: for an order with $100 value,3 or more cash payments can be made $10,$30,$20,$40) and each cash payment will be associated with a cash transaction num(that should be displayed on the report).Similarly each credit card will have an approval code and in case of multiple credit cards all the approval codes have to be shown on the report.
I thought of having this multiple payment method options in a junk dimension.
But how do I keep track of the "baggage" that comes with these payments (like cash tran num, approval codes etc;).
I would appreciate any help..Thanks

AKris

Posts : 6
Join date : 2010-02-28

View user profile

Back to top Go down

Re: Order Header fact and multiple payment methods

Post  BoxesAndLines on Thu Mar 04, 2010 9:37 am

I would seriously consider lowering the grain. The other option is a grouper table (Grouper Link).
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Order Header fact and multiple payment methods

Post  ngalemmo on Thu Mar 04, 2010 12:35 pm

As B&L suggested, lower the grain of the fact table or, consider two fact tables... one at the order (or possibly order line level) that does not have payment information and a second fact at the payment level. The first is essentially a summary of the second and would be used where payment information is not required. The second would be used for payment level queries.

The choice to go with one table at payment level or two tables will depend on the volumes you are dealing with and the common forms of analysis. If volumes are high and most queries do not involve payment specifics, having two facts will improve performance for the bulk of the queries that don't need payment level detail.

I would not consider a group table as, from you desription, the information about each payment is unique (amount, authorization codes, etc...). Grouping does not work in situations like that.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Order Header fact and multiple payment methods

Post  AKris on Thu Mar 04, 2010 3:27 pm

Thanks B&L and Nick for your valuable suggestions.If I have to consider lowering the grain and creating two seperate order header fact tables (one row per order,one row per payment) can I show all the attributes from both the facts in one report (using drill accross)?
For ex: the first order header fact has 5 conformed dimensions (cust billto,shipto,location and two other dimensions) and the second order header fact at payment level has the same 5 dimensions + the payment dimension)?
There were a bunch of reports that were already tested using the existing order header fact and I do not want to retest them again(for all other attributes other than payment attribues) by lowering the grain of the existing fact table.
The multiple payment options that I described have surfaced recently when we had a meeting with guys from source system.This gap has been identified recently.I thought of using a bridge, but this multiple cash payments,multiple credit cards etc; were a big pain.

Thanks again for all your valuable suggestions.

AKris

Posts : 6
Join date : 2010-02-28

View user profile

Back to top Go down

Re: Order Header fact and multiple payment methods

Post  ngalemmo on Thu Mar 04, 2010 3:48 pm

If you implement a payment level grain fact table, that table would contain all foreign keys of the order level fact, plus additional foreign keys pertaining to payments. So you would have references to all the attributes the order table has in the payment table, so there is no need to cross-join.

The issue would be with measures, such as order amount, tax, shipping charges, etc. You have two options for the payment fact... carry them in the payment level or not. If you do, you would need to allocate those values at the payment level so they sum correctly and/or carry the total amounts on each row (for queries that need to show order amount and how much was paid by a specific payment method...order amounts would not be additive across payment methods) . If you do not, you would need to query both tables and combine results as needed.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Order Header fact and multiple payment methods

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