-1

Image of different fees that relate to a transaction

I have a Transactional Fact table showing transactions done by a particular client. I want to relate this fact table to a Dimension Containing Different transaction Fees that might occur on a transaction. Each transaction will be linked to a range of between 1-5 different Transaction Fees which are all listed in different rows within the Transaction Fees Dimension. What is the best way to implement this ? Should I implement something such as Role Play Dimension and have multiple Keys for each type of Transaction Fee ?

Regards, K

Kurt Desira
  • 3
  • 2
  • This post risks being removed as it is very general and asks for potentially opinionated answers. – Shaun Nov 24 '15 at 13:38
  • Ok. Can you give me your opinionated suggestion then ? – Kurt Desira Nov 24 '15 at 13:52
  • I came across this post in review, nothing more. And welcome to SO. – Shaun Nov 24 '15 at 13:54
  • Pls check [answer](http://stackoverflow.com/questions/33570611/how-to-connect-a-fact-and-dimension-table-that-are-in-1-n-relationship/33581405#33581405) to a similar question. Transaction and Fee Types are N:M, but Transactions to Fees are 1:N. – Marmite Bomber Nov 24 '15 at 16:35

1 Answers1

0

I would create a fact table with an atomicity of transaction + transaction fee.

If you know the fees at the time that the transaction is created then you can perform the join between the transaction and its fees during the data load process.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • Hi David, Thanks for the answer, however what you have just explained is what i am trying to accomplish. The problem is that for each Transaction in the fact table, the Fees dimension Table will contain multiple Fee Types in different rows thus creating a many to many environment. Now i was thinking of either a Bridge Dimension or a Role Playing Dimension. – Kurt Desira Nov 24 '15 at 14:09
  • I have attached a Picture showing some Fees that are associated to a particular transaction. – Kurt Desira Nov 24 '15 at 14:21
  • It should be as simple as joining the transactions to the fees table prior to inserting into the fact table. What you probably have at the moment is a fact table with one row per transaction, and you need one row per transaction-fee, so if you have 100,000 transactions each with an average of 5 fees, the transaction table has 500,000 rows. – David Aldridge Nov 25 '15 at 09:05