Accumulating Snapshot Fact OR Type 3 Dimension?

View previous topic View next topic Go down

Accumulating Snapshot Fact OR Type 3 Dimension?

Post  srandell74 on Fri Apr 25, 2014 11:05 am

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

srandell74

Posts : 4
Join date : 2014-04-25

View user profile

Back to top Go down

Re: Accumulating Snapshot Fact OR Type 3 Dimension?

Post  ngalemmo on Fri Apr 25, 2014 1:34 pm

What is the time span between retry attempts?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Accumulating Snapshot Fact OR Type 3 Dimension?

Post  srandell74 on Mon Apr 28, 2014 10:45 am

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.

srandell74

Posts : 4
Join date : 2014-04-25

View user profile

Back to top Go down

Re: Accumulating Snapshot Fact OR Type 3 Dimension?

Post  ngalemmo on Mon Apr 28, 2014 3:42 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Accumulating Snapshot Fact OR Type 3 Dimension?

Post  srandell74 on Mon Apr 28, 2014 3:44 pm

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

srandell74

Posts : 4
Join date : 2014-04-25

View user profile

Back to top Go down

Re: Accumulating Snapshot Fact OR Type 3 Dimension?

Post  srandell74 on Tue Apr 29, 2014 9:45 am


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

View user profile

Back to top Go down

Re: Accumulating Snapshot Fact OR Type 3 Dimension?

Post  BoxesAndLines on Tue Apr 29, 2014 8:44 pm

Aggregation or partitioning are your two primary options.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Accumulating Snapshot Fact OR Type 3 Dimension?

Post  nick_white on Wed Apr 30, 2014 2:58 am

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 : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Accumulating Snapshot Fact OR Type 3 Dimension?

Post  ngalemmo on Wed Apr 30, 2014 3:09 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: Accumulating Snapshot Fact OR Type 3 Dimension?

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