Tough question...End of Month Snapshot combined with History

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 monthly snapshots, AND also keep PREVIOUS snapshots as historical information 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 but i also want to display the end of month snapshots..how to do this? any suggestions?

any help appreciated..

tnkz