many to many fact table relationship - use dimension, bridge or ?

[mcloughlins]

I am working on new design for retail sales dw. I have 2 fact tables. One contains transaction sales with 1+ products for each transaction number. The other fact table contains transaction payments with 1+ payments for each transaction number. With this many-to-many relationship between these 2 fact tables, I'm not sure the best direction to go with this.

table sampling...

transaction sales fact:

• Location (dimension)
  Date (dimension)
  Transaction #
  Transaction Line #
  Cashier (dimension)
  Product (dimension)
  Quantity (additive fact)
  extendedCost (additive fact)
  extendedPrice (additive fact)
  ...

sales payments fact:

• Location (dimension)
  Date (dimension)
  Transaction #
  Payment Line #
  PaymentType (dimension)
  PaymentAmount (additive fact)

There can be multiple payments on one transaction. For instance, part of the transaction is paid for with a gift card and the rest is paid for with credit. The link between the 2 fact tables is Location, Date and Transaction #.

Would it be best to:

1. Add a surrogate key to both fact tables and create a bridge table like you would with a many-to-many relationship between fact and dimension? It seems like this would be very impractical.
2. Create a Transaction Header dimension table, more like a relational database design. Then the transaction header dim would have a one-to-many relationship with each fact table.

or is there a better alternative to either of these ideas?

[ngalemmo]

All relationships between fact tables are assumed to be many to many.

1. No, you already have a key and it is many to many anyway.
2. No, you already have a key (the transaction # degenerate dimension).

Query each fact separately and aggregate on common dimensions. You can the join or union the two sets on those dimensions. The relationship will be 1:1.

[mcloughlins]

Thanks for the quick response. My concern is accuracy of drilling down. I understand if I aggregate the facts separately, then there won't be an issue, but if they want to see more details of the transaction combined, then duplications will occur in the reporting end.

For example, if someone drills down to the transaction level and there are 4 items sold and 2 payment types for that transaction, the details would be inaccurate and show duplicates. This is because the join between the sales fact and payment fact for one transaction is Location, Date, Transaction #. What makes each record unique in the fact table is Location, Date, Transaction #, Line #.

Am I trying to overthink this? I am still new to the dimensional design and my mind keeps leading me back to the normalized world of relational design. It is already determined that customers will be drilling into the data to see the detail that makes up the aggregations.

I keep leading towards creating a transaction header dim that contains all of the attributes of the transaction information. ie. location, date, time, transaction #, etc. Then use the surrogate key created in this dimension to use in the fact tables for the transactionKey. This would at least serve up accurate data when querying on the two facts together. But my main concern is the performance hit.

[Jeff Smith]

In a drill down, there is still the need for 2 seperate queries. The 2 fact tables can't be joined together.

[mcloughlins]

Jeff, can you explain why it couldn't be joined by a transaction header dimension?

Thanks!

[Jeff Smith]

Fact tables don't joined together. If you could join the 2 fact tables together, then they are at the same grain and should probably be in the same fact table.

You can join the results of 2 queries on fact tables as long as the queries put them on the same grain.

You already have a Transaction Header Dimension - it's called Transaction #.

Here's an alternative - create a Transaction Aggregate table. Sum the Trans fact to the Transaction and the Payment up to the transaction, and you have the 2 pieces of data on the same line. If users want to drill down further, they can drill into the Trans Facts or Payment facts.

[ngalemmo]

I keep leading towards creating a transaction header dim that contains all of the attributes of the transaction information. ie. location, date, time, transaction #, etc. Then use the surrogate key created in this dimension to use in the fact tables for the transactionKey. This would at least serve up accurate data when querying on the two facts together. But my main concern is the performance hit.

You already have that information as dimensions. Both fact tables should have the appropriate dimensions.

As far as drill down goes, it is typically handled as a second query.

The thing is, the fact tables are fine (although I would probably include cashier in the payment fact). They are many to many. There is nothing you can do to change that. Any derived dimension won''t change the relationship. You cannot show payment and sales on the same report unless you do it in two queries.

One thing you can try is two embedded selects (one sales, one payment) and do a full outer join of the sets on transaction # and line #. This will give you one table with the data side to side that you can use to produce a report.

[Sponsored content]