Banking Data Mart - Case studies, issues

[nchapman]

Am desiging a Banking Data Mart. More specifically one which analyses transactional data, across all products. (eg, Credit card, Savings account and Deposit account transactions).

Ideally would like peoples thoughts on how best to design this. Bearing in mind I have such issues as....
How to cope with Joint Accounts ?
What about balances, running balances ? Do I have these
in a seperated data mart ?

Has anyone out there done this already ? If so, would appreciate your thoughts.

 

[krishnaa279]

Hi nchapman,
To cope up with joint accounts you link the customer dimension with account dimension which will act as a bridge table between the customer and the fact table and take a field wieghtage. and the mechanism is if the account type is single wieghtage factor is 1 if in joint account for two persons divide 1/2 so factor will be .5 and if 3 1/3 ... and so on as the cust_cd and account_cd is a composite key u can capture the different combinations uniquely and to calculate the measures you have to multiply with wieghtage factor to get the exact measures related to this.I implimented it in insuranse business and it is working fine

krishnaa


krishnaa.

 

[krishnaa279]

Hi nchapman,
continuation for your posting......

you take the balances and running balances as measures in the fact and while retrieving multiply them with the wieghting factor.

For hetrogeneous products if the measures are different(obviously the measures will be different) you create a fact and aggregation as account type and look into it for immediate totals, and create individual fact tables for each product type otherwise sparsity will occur.
I think this will work.

 

[JGirl]

Hey, if you need any more suggestions, post a reply....I have been working with a bank on their data warehouse for the last 3 years, and i'd be more than willing to let you know the way we did it, although im sure there are some better ways of doing it.

To handle the joint accounts, we have a CLIENT table, and an ACCOUNT table containing all the static information. Then there is another table to store the CLIENT_ACCOUNT_RELATIONSHIP. ie. how the clients are linked to the accounts......

We have not applied weightings etc in our measures tables as it is assumed that although many clients can be attached to an account, only one client will be the primary account holder, and any others will be secondary account holders, or have a non-account-holding relationship to the account such as ''authority to operate'.


As for the balances, the way we did it was in a monthly account measures table, keyed on the month and the account number, and this held the end of month balance information. This was useful enough to be able to analyse the data over time without the overhead of storing daily account totals.

I guess the way that you handle the design of your warehouse will depend on what the requirements are. Im not going to go into too much detail because im not sure if you have already come up with a solution to your problems yet, but if you still need help, let me know.


Cheers
J

 

[nchapman]

Krishnaa279 and JGirl, thanks for replies.

JGirl :-
With respect to joint balances. For our business, we may want to find out who in particular made a Credit Card transaction, ie. was it the primary account holder or secondary. Both people on the account will have the authority to operate.

We as a business, currently have numerous products, Credit Card, Current account, Savings account etc. At present it is just Credit card, where we can identify who on a joint account, made the transaction. The other products we cannot as yet, although investigations are in progress. We may have to make the assumption that they are just for the primary account holder.

What I am trying to get a feel for, is how others like yourself, has catered for this issue.

At present I am designing a "first draft" transactional data mart, which will provide the business with something to "play with". From thereon after, it will evolve. I see this very much as an iterative approach to designing it.

With regard to the balances. We currently have a number of derivations that are calculated daily by a series of pl/sql scripts on our oracle database. These are based upon balances. eg, average balance month to date, aggregate balance mtd. I would like if possible to design a data mart, whereby these types of derivations are done by the users, "on the fly", whenever they require them. Such types of derivations obviously require daily balances in order to be calculated.

Any further thoughts on the above would be much appreciated.

 

[JGirl]

Hey, I think your needs are a little different to ours. Firstly, we dont offer credit cards, and are not really interested in who is making the transaction - transactions are stored at an account level, rather than at a client level.....Our data warehouse was more based on account closing balances for time periods, investment account maturity details, movements of accounts between branches, loan approval values, snapshots of accounts that have certain statuses etc.....we never really catered for (or required) which one of the account holders conducts the transaction - we just really care about the transaction itself - where it was conducted / how much / type of transaction etc.

Most of the information in the data warehouse is stored at an account level, as almost all the information from the host system is also at an account level, and the client information is fairly limited. Although the clients and their accounts could be combined, we generally tend not to as we dont require it (apart from demographical information).

I cant see that this would be helping too much, as I think the requirements of your proposed system are very different, but if you want to know anything else, keep asking!

Cheers
J