August 5, 2005 at 1:00 am
Guys hi,
i need your help on this...
i am preparing a BI system for a bank.
I am currently on the process of building a cube for the banks products which are devided in credit cards and loans.
The 3 cubes i want to built are:
1. summerized analysis of portfolio of both cards and loans, refering to End of Month Snaphshot (mothly level of analysis). Dimensions are, period_day (days dimension), billing cycle, product (differend credit cards, loans) and current bucket (bucket is how many months a customer owes monthly installments. i.e. You owe 3 months of minimum payments to your credit card, then you are bucket three).
Measures: Number of cases, debt amount
2. portfolio of cards end of month snaphshot, with dimensions period_day (days dimension) billing cycle, product (different types of credit cards), current bucket.
Measures, number of cases, debt amount.
3. and the portfolio of loans. with the same dimensions and measures as the cards cube. However, loans are of two types, (loans for home buy, and loans for personal buys), that have different billing cycles. i.e home loans always get billed by the bank on the first of each month, however personal buy loans get billed at moving dates, in respect to holiday calendar. i.e. if the 1 st of the month is normal working day, we take the snapshot. however if the first of the month is weekend or holiday, we need the last day of the previous month, snaphot.
My question is...can you suggest a way to built the cubes so as to have these snapshots, AND historical information of any previous snapshots of previous time periods? that is, lets say i want to compare this months snapshot of cards, with last years snapshot of the same month for the cards. the same goes for loans.
my problem in other words is how to make the cubes that hold the products (cards, loans, etc) show each month the snaphsot of the products but also be able to hold historical information of last month, 6 months ago, or a year ago.
SO! i need to keep a history of snapshots i suppose..how to do this? any suggestions?
any help appreciated..
August 8, 2005 at 8:00 am
This was removed by the editor as SPAM
August 8, 2005 at 9:22 am
Ralph Kimball's The Data Warehouse Toolkit 2nd edition has a chapter on building All sorts of various data warehouse schemas. One is an Inventory Balance Snapshot. Im sure the design would be similar to what you have to do as well as giving you other tips and insights. There is also a Financial Services Chapter with a section on Point in Time Balances
August 8, 2005 at 8:41 pm
Hi,
Your question is very broad in nature and I think, it would be difficult to get a comprehensive answer. You need to work with somebody for about 2 weeks to come out with a proper cube design.
If you agree, i would like to work with you on this project. For past 2 1/2 years, I am working on DW projects in manufacturing, financial services and General ledger data warehouses. I would like to understand more on credit cards domain and extend my thoughts on this to you.
For this, i would need the blank database (for RDBMS metadata). If any test data is there, that would be better for testing. From there, we can work together on the cube design and exchange our thoughts.
Regards,
Ajit Singh
August 23, 2005 at 1:32 am
Ajit,
sorry for the delayed reply, i was on vacations.
Thank you for your concern. My supervisor is currently dealing with the deployment of the portfolio cubes, and he had a hard week trying to figure out a solution, with no solution in the horizon untill now.
I also haven't come up with a solution yet.
I can provide you with a blank database. i am unable to provide you with data for testing as you understand, as it contains personal credit card information of people in several countries. (Probably its a good idea to take their credits number and head on to Jamaica for ever! 🙂 ). You must bare in mind, that the database is especially complex as it receives data from 3 different bank-systems.
However, i can send you the documentation we have produced that is loud and comprehensive. Inform me if you are still interested in working with me.
Kind Regards,
Dionisis
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply