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

What is the time span between retry attempts?

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.

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.

That is a great idea and something that never entered my mind. Much appreciated!

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

Aggregation or partitioning are your two primary options.

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

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.