Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Accumulating Snapshot Fact OR Type 3 Dimension?

4 posters

Go down

Accumulating Snapshot Fact OR Type 3 Dimension? Empty Accumulating Snapshot Fact OR Type 3 Dimension?

Post  srandell74 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

Back to top Go down

Accumulating Snapshot Fact OR Type 3 Dimension? Empty Re: Accumulating Snapshot Fact OR Type 3 Dimension?

Post  ngalemmo Fri Apr 25, 2014 1:34 pm

What is the time span between retry attempts?
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Accumulating Snapshot Fact OR Type 3 Dimension? Empty Re: Accumulating Snapshot Fact OR Type 3 Dimension?

Post  srandell74 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

Back to top Go down

Accumulating Snapshot Fact OR Type 3 Dimension? Empty Re: Accumulating Snapshot Fact OR Type 3 Dimension?

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

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

http://aginity.com

Back to top Go down

Accumulating Snapshot Fact OR Type 3 Dimension? Empty Re: Accumulating Snapshot Fact OR Type 3 Dimension?

Post  srandell74 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

Back to top Go down

Accumulating Snapshot Fact OR Type 3 Dimension? Empty Re: Accumulating Snapshot Fact OR Type 3 Dimension?

Post  srandell74 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

Back to top Go down

Accumulating Snapshot Fact OR Type 3 Dimension? Empty Re: Accumulating Snapshot Fact OR Type 3 Dimension?

Post  BoxesAndLines Tue Apr 29, 2014 8:44 pm

Aggregation or partitioning are your two primary options.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Accumulating Snapshot Fact OR Type 3 Dimension? Empty Re: Accumulating Snapshot Fact OR Type 3 Dimension?

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

Back to top Go down

Accumulating Snapshot Fact OR Type 3 Dimension? Empty Re: Accumulating Snapshot Fact OR Type 3 Dimension?

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

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

http://aginity.com

Back to top Go down

Accumulating Snapshot Fact OR Type 3 Dimension? Empty Re: Accumulating Snapshot Fact OR Type 3 Dimension?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum