ATM (Automatic Teller Machine) dimensional model

View previous topic View next topic Go down

ATM (Automatic Teller Machine) dimensional model

Post  dw_user on Wed Dec 14, 2011 1:28 pm

Hi,

I am designing an ATM (automatic teller machine) star schema. I have dimensions like ATM, Account, Date, Time, Prospect, Customer, Transaction type etc. I also have Transaction Amount and Account Balance measures in the fact table.
When a person (customer or prospect) goes to an ATM and withdraws money then the Transaction Type=Withdraw and Transaction Amount would have the amount that was withdrawn and Account Balance would have the balance. Everything seems good until someone "Transfers money" from one account into another.
The way I am thinking of handling this is:
Create another Foreign Key from the Account Dimension into the Fact table as "Receiving Account FK" and add two additional measures like Withdraw Amount and Deposit Amount. this would make altogether 2 FKs coming from Account dimension into the Fact table. Problem is the "Receiving Account FK" would be null (or default) for any other type of transaction besides "Transferring money".

Is this a good practice? Is there a better way to do this?
I have already asked the business user they don't treat transferring money as 2 different transactions, but only as one transaction.

dw_user

Posts : 8
Join date : 2011-05-11

View user profile

Back to top Go down

Re: ATM (Automatic Teller Machine) dimensional model

Post  ngalemmo on Wed Dec 14, 2011 2:57 pm

I would go with two rows and add a transaction ID degenerate dimension. The transaction ID could be a simple sequentially assigned integer that would identify which rows belong to the same transaction. If you flatten things out and add another account FK, it becomes difficult to do account level queries since you would always need to do a union of two result sets so that you are assured of capturing all activity for an account.

Also, the transaction ID need not be unique over all time. You could use an integer Sequence and allow it to roll over after 2 billion or so transactions. When doing transaction level analysis of transfers, use a combination of transaction date key and transaction ID as the group by for transactions.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: ATM (Automatic Teller Machine) dimensional model

Post  dw_user on Wed Dec 14, 2011 3:59 pm

Can you please explain how the transaction id can tell us which rows belong to the same transaction?

dw_user

Posts : 8
Join date : 2011-05-11

View user profile

Back to top Go down

Re: ATM (Automatic Teller Machine) dimensional model

Post  ngalemmo on Wed Dec 14, 2011 4:18 pm

They would be assigned the same transaction ID value, so if you need to recreate the business view of a transaction, you group on this value and use column expressions (i.e. IF or CASE) to break out facts into the in and out roles of a transfer (this could be a view of the fact which would be used for transfer transaction queries).
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: ATM (Automatic Teller Machine) dimensional model

Post  Jeff Smith on Wed Dec 14, 2011 4:58 pm

The 2 rows is definitely the way to go for Transfers. You might want to add a Transaction_Count measure to the fact table to aid in rollups (how many transactions occurred at the ATM). You could make the Tranfer From transaction a 0 and the Tranfer To transaction a value of 1.

When I was in banking, we had a transaction fact table that included transactions from the ATM, Teller, VRU, Online, Call Center, etc. Made it easy to rollup transactions to the account or household.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: ATM (Automatic Teller Machine) dimensional model

Post  dw_user on Wed Dec 14, 2011 5:54 pm

Thanks for the reply guys, much appreciated!

Can you please tell me what VRU is?

dw_user

Posts : 8
Join date : 2011-05-11

View user profile

Back to top Go down

Re: ATM (Automatic Teller Machine) dimensional model

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