ATM (Automatic Teller Machine) dimensional model
3 posters
Page 1 of 1
ATM (Automatic Teller Machine) dimensional model
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.
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
Re: ATM (Automatic Teller Machine) dimensional model
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.
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.
Re: ATM (Automatic Teller Machine) dimensional model
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
Re: ATM (Automatic Teller Machine) dimensional model
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).
Re: ATM (Automatic Teller Machine) dimensional model
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.
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
Re: ATM (Automatic Teller Machine) dimensional model
Thanks for the reply guys, much appreciated!
Can you please tell me what VRU is?
Can you please tell me what VRU is?
dw_user- Posts : 8
Join date : 2011-05-11
Similar topics
» Rule based algorithm to convert an ER model to a dimensional model
» Complexities of Relational Model and Simplicities of Dimensional Model
» Meta-model of Kimball dimensional model
» Why we use Dimensional Model over De-normalized relational Model ?
» dimensional model help
» Complexities of Relational Model and Simplicities of Dimensional Model
» Meta-model of Kimball dimensional model
» Why we use Dimensional Model over De-normalized relational Model ?
» dimensional model help
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|