Accumulating Snapshot Fact OR Type 3 Dimension?
4 posters
Page 1 of 1
Accumulating Snapshot Fact OR Type 3 Dimension?
Hi,
We have a Payment table which has credit card processing results. Any failed attempts are retried up to 3 times. Retries update the status (approved/declined) , retry_attempt, and last_update_date_time fields.
I am creating a FactPayment table and a DimCreditStatus table and would like to track the progress of any retry attempts. As I would like only a single Fact record for a single payment, would an Accumulating Snapshot Fact make sense? Additional fields (retry1_status, retry1_date, etc..) could be added to track current/past. This will be a large Fact table so I assume Updates would be very expensive.
An alternative might be a Type 3 DimCreditStatus dimension? Since the maximum number of retry attempts is 3, the dimension table could be augmented to include additional fields for any retry attempts and flag the most current. The Dimension record may therefore require the original TransactionID for lookup purposes.
Do either of these approaches make sense? Is there a better alternative? I am very new at this and would appreciate your help.
Thanks,
Scott
We have a Payment table which has credit card processing results. Any failed attempts are retried up to 3 times. Retries update the status (approved/declined) , retry_attempt, and last_update_date_time fields.
I am creating a FactPayment table and a DimCreditStatus table and would like to track the progress of any retry attempts. As I would like only a single Fact record for a single payment, would an Accumulating Snapshot Fact make sense? Additional fields (retry1_status, retry1_date, etc..) could be added to track current/past. This will be a large Fact table so I assume Updates would be very expensive.
An alternative might be a Type 3 DimCreditStatus dimension? Since the maximum number of retry attempts is 3, the dimension table could be augmented to include additional fields for any retry attempts and flag the most current. The Dimension record may therefore require the original TransactionID for lookup purposes.
Do either of these approaches make sense? Is there a better alternative? I am very new at this and would appreciate your help.
Thanks,
Scott
srandell74- Posts : 4
Join date : 2014-04-25
Re: Accumulating Snapshot Fact OR Type 3 Dimension?
Thanks for your reply.
The retry attempts vary by client but the first retry is roughly within 48 hours, then 7 days, then 14 days post-transaction.
The retry attempts vary by client but the first retry is roughly within 48 hours, then 7 days, then 14 days post-transaction.
srandell74- Posts : 4
Join date : 2014-04-25
Re: Accumulating Snapshot Fact OR Type 3 Dimension?
One thing you may consider is a single fact table with one row per attempt. It would have two measures: requested amount, which would be the amount you are attempting to clear, and payment amount, the amount approved and charged to the account. Payment amount would be zero in rows where the charge was rejected.
The advantage of this is the fact table will be insert only and you do not need to maintain a dimension that would essentially have a 1:1 correspondence to the fact rows. You would always use payment amount for financial reporting as it would be fully additive. Requested amount can be used it analyzing customer payment performance.
The advantage of this is the fact table will be insert only and you do not need to maintain a dimension that would essentially have a 1:1 correspondence to the fact rows. You would always use payment amount for financial reporting as it would be fully additive. Requested amount can be used it analyzing customer payment performance.
Re: Accumulating Snapshot Fact OR Type 3 Dimension?
That is a great idea and something that never entered my mind. Much appreciated!
srandell74- Posts : 4
Join date : 2014-04-25
Re: Accumulating Snapshot Fact OR Type 3 Dimension?
ngalemmo, I really appreciate your help but have an additional question with this approach... with multiple decline records for the same transaction in FactPayment, what would be an efficient way to identify the number and amount of declined payments that have yet to be settled - as FactPayment will be a very large table.
An overly simplified view of the table is below. With a smaller number of records, I can think of approaches to solve this issue but am unsure how to model/query for a such a large fact table. Would some sort of materialized view or table be required to query the current state?
Transaction_ID Transaction_Date Last_Update_Date Retry_Attempt Credit_Status Requested_Amount Payment_Amount
123 20140401 20140401 0 Declined 100 0
123 20140401 20140403 1 Declined 100 0
123 20140401 20140408 2 Declined 100 0
123 20140401 20140415 3 Settled 100 100
456 20140402 20140402 0 Declined 50 0
456 20140402 20140404 1 Declined 50 0
Thanks,
Scott
srandell74- Posts : 4
Join date : 2014-04-25
Re: Accumulating Snapshot Fact OR Type 3 Dimension?
Aggregation or partitioning are your two primary options.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Accumulating Snapshot Fact OR Type 3 Dimension?
My suggestion is that you use ngalemmo's approach to create a fact table at the lowest possible level of grain and include in it all the information you can about this process. You then use this fact as the basis for creating additional facts that answer specific reporting questions you have - as aggregates/materialised views or other appropriate mechanism
nick_white- Posts : 364
Join date : 2014-01-06
Location : London
Re: Accumulating Snapshot Fact OR Type 3 Dimension?
But, realistically, unless this is for a collection agency, the number of collection attempts should be relatively small portion of the rows. It should not have a significant effect on query performance. See how it goes before building an aggregate.
![-](https://2img.net/i/empty.gif)
» Type 2 Dimension with accumulating snapshot with time stamp
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Time in fact or dimension? Accumulating snapshot
» Accumulating Snapshot Fact with Dimension at Same Grain
» Subscription as an Accumulating Snapshot Fact or a Dimension?
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Time in fact or dimension? Accumulating snapshot
» Accumulating Snapshot Fact with Dimension at Same Grain
» Subscription as an Accumulating Snapshot Fact or a Dimension?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum