Transaction Fact design
4 posters
Page 1 of 1
Transaction Fact design
hi I am faced with a problem designing a transaction fact in an investment firm. each transaction represents a trade the traders put in (buy,sale etc).
The problem I am facing is: the metrics of a transaction change from one day to another day so a transaction which was say recorded at 1M on day one may come back in day two as 2M. When this happens I have read through a lot and got 4 approaches:
1. Approach 1: snapshot way:
Take a snapshot of all the transactions available as of that day and load it in Fact table with the corresponding date dimension key. This way the data in a table grows in an arthemetic progression as days go by the snapshots size increases.
2. Approach2: Record the Fact as a slowly changing dimension:
Record the fact only if there is a change and end date the old one with a "effective from" and"effective to" dates. this way transaction history is captured effectively but the querying of this table is complicated and would require "effective from" and "effective to" dates.
3. Approach 3: make a junk SCD for transaction to capture change history and have a factless fact which captures the relation of this SCD dimension with rest all dimensions as of any particular day. In this way we further save space since the fact table will be only having few columns.
4. Approach 4: Offsetting Transaction:
Offset the transaction received on day one with a negative transaction on day 2 and insert a new transaction as of day 2 to record the change. this way the hisotry is preserved and reporting is also possible.
Let me know which way is better
The problem I am facing is: the metrics of a transaction change from one day to another day so a transaction which was say recorded at 1M on day one may come back in day two as 2M. When this happens I have read through a lot and got 4 approaches:
1. Approach 1: snapshot way:
Take a snapshot of all the transactions available as of that day and load it in Fact table with the corresponding date dimension key. This way the data in a table grows in an arthemetic progression as days go by the snapshots size increases.
2. Approach2: Record the Fact as a slowly changing dimension:
Record the fact only if there is a change and end date the old one with a "effective from" and"effective to" dates. this way transaction history is captured effectively but the querying of this table is complicated and would require "effective from" and "effective to" dates.
3. Approach 3: make a junk SCD for transaction to capture change history and have a factless fact which captures the relation of this SCD dimension with rest all dimensions as of any particular day. In this way we further save space since the fact table will be only having few columns.
4. Approach 4: Offsetting Transaction:
Offset the transaction received on day one with a negative transaction on day 2 and insert a new transaction as of day 2 to record the change. this way the hisotry is preserved and reporting is also possible.
Let me know which way is better
lalith160- Posts : 2
Join date : 2009-07-16
Location : 06905
Re: Transaction Fact design
I would go with a modified option 4... store the net change rather than a negative and positive.
But, I don't fully understand the issue... normally there are at least 2 fact table groups involved... orders and executions, to record a trade, so I am assuming you are talking about order information. Execution data should already be received in transactional (net change) form.
But, I don't fully understand the issue... normally there are at least 2 fact table groups involved... orders and executions, to record a trade, so I am assuming you are talking about order information. Execution data should already be received in transactional (net change) form.
Re: Transaction Fact design
I am sorry to have missed this out.Our data source is a trade entry system.(not the trading system). Its like business putting order for a trade and it gets executed in third party environment in multiple systems for integration purposes we have taken the data from trade entry system and hence the modifications are possible.settlement information is captured in form of settlement date. after the settlement date the data for the transaction is frozen.
lalith160- Posts : 2
Join date : 2009-07-16
Location : 06905
Re: Transaction Fact design
I would concur with the modified Approach 4 recording net changes if possible, otherwise Approach 4 with offsetting transactions as originally described.
VHF- Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US
Re: Transaction Fact design
Hi,
I would recommend keeping your solution 1 as it is. The net change record will work but it is quite possible that the change in your data is not only on the amount of the transaction but in one of the dimension. For example, let say your adjustment needs to be assign a new trader, so you will anyway need to reverse the transaction of the old trader and create a new record for the new trader. That would not be supported with only a net change. You could always use an hybrid of the 2 and use net change only when your grain (combinaison of all dimensions) is the same. You might then save some rows in your fact and make sure its significant to justify a dual reversal logic.
I would recommend keeping your solution 1 as it is. The net change record will work but it is quite possible that the change in your data is not only on the amount of the transaction but in one of the dimension. For example, let say your adjustment needs to be assign a new trader, so you will anyway need to reverse the transaction of the old trader and create a new record for the new trader. That would not be supported with only a net change. You could always use an hybrid of the 2 and use net change only when your grain (combinaison of all dimensions) is the same. You might then save some rows in your fact and make sure its significant to justify a dual reversal logic.
Re: Transaction Fact design
You can do that with net change... it all depends on how you generate it.
The method I use is to create a negative image of the current state and a positive image of the new state and put both into a staging table. I then aggregate that table by the dimensions and insert the aggregate (less any rows whose measures are all zero) into the fact table. It's simple and works with any combination of measure or dimension change. The staged data can also be used to insert net change to any summary tables that may be based on the fact... simply summarize on the dimensions appropriate to the target summary.
The method I use is to create a negative image of the current state and a positive image of the new state and put both into a staging table. I then aggregate that table by the dimensions and insert the aggregate (less any rows whose measures are all zero) into the fact table. It's simple and works with any combination of measure or dimension change. The staged data can also be used to insert net change to any summary tables that may be based on the fact... simply summarize on the dimensions appropriate to the target summary.
Similar topics
» Accumulating Snapshot and Transaction Fact tables : question to design and use them together
» Fact table design: Sales Transaction with multiple Discount rows
» Guidelines for FACT Table Design for High-Transaction Volume and High # of Measures ...
» Transaction fact without obvious transaction type field
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» Fact table design: Sales Transaction with multiple Discount rows
» Guidelines for FACT Table Design for High-Transaction Volume and High # of Measures ...
» Transaction fact without obvious transaction type field
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum