Payment and Payment Schedule

Hi,

I'm trying to model a DW for a Payment model from a transactional DB. I have 3 tables of Payment, Payment Schedule and Payment Instrument. I have to keep the history of all of them. Payment schedule can be "Onetime Real Time", "Onetime Future Dated", "Recurring". Once the Payment Due Date is reached it post the payment in the Payment Table and a new record is created in the Payment Schedule and the old record moves to the Payment Schedule History table. The details of the type of payment asscociated with the Payment Schedule is available in Pament Instrument, which can also change. This is happening in the transactional base. Now I made the Payment table as FACT and Payment Schedule as Dimension. But while trying to kep the history of the Schedule I've to create a surrogate key for the multiple records of a schedule and associate with the fact. While ETL is trying to find out the appropriate surrogate key it needs to download the total table and which leads to a performance issue. Also the payment schedule table becomes rapidly changing dimension. Please provide me a solution for the model.

Regards,
Sourav

Payment schedule is not a dimension. That may address some of the issues you are having.

We could use some more info here. Payment Fact makes sense, with Payment Instrument and Payment Date as dimensions. Payment Schedule does sound like a fact, but it would help to know more about the nature of the Payment Schedule, and what makes it change so often. Also, do you know who the customer is? Are they buried in the Payment Instrument? Can the same customer have multiple Payment Instruments?

Also, how big are these tables?

--Warren

The Payment Schedule is associated for One time Payment where for every payment transaction we have one schedule record. For Recurring Payment the record changed the Payment_Due_Date once the payment date is reached for the next payment, with a Modified_Timestamp. The total size of the table is 1.5GB. The Payment Instrument and Schedule is assicated with the Account. At a particular time only one Instrument is associated with an Account.