Order Header fact and multiple payment methods

[AKris]

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

[BoxesAndLines]

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

[ngalemmo]

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.

[AKris]

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.

[ngalemmo]

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.

[Sponsored content]