Transaction Fact design

View previous topic View next topic Go down

Transaction Fact design

Post  lalith160 on Thu Jul 16, 2009 4:29 pm

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

lalith160

Posts : 2
Join date : 2009-07-16
Location : 06905

View user profile

Back to top Go down

Re: Transaction Fact design

Post  ngalemmo on Thu Jul 16, 2009 5:40 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Transaction Fact design

Post  lalith160 on Fri Jul 17, 2009 9:47 am

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

View user profile

Back to top Go down

Re: Transaction Fact design

Post  VHF on Fri Jul 17, 2009 11:30 am

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

View user profile

Back to top Go down

Re: Transaction Fact design

Post  patrick_lavallee on Fri Aug 28, 2009 12:14 pm

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.

patrick_lavallee

Posts : 3
Join date : 2009-03-03
Location : Quebec, Canada

View user profile http://www.linkedin.com/in/patricklavallee

Back to top Go down

Re: Transaction Fact design

Post  ngalemmo on Fri Aug 28, 2009 12:27 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Transaction Fact design

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