Payment and Payment Schedule

View previous topic View next topic Go down

Payment and Payment Schedule

Post  sourav.guha on Fri Nov 26, 2010 11:42 am

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

sourav.guha

Posts : 5
Join date : 2010-11-26

View user profile

Back to top Go down

Re: Payment and Payment Schedule

Post  BoxesAndLines on Sun Nov 28, 2010 12:03 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Payment and Payment Schedule

Post  warrent on Wed Dec 01, 2010 7:39 am

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
avatar
warrent

Posts : 41
Join date : 2008-08-18

View user profile

Back to top Go down

Re: Payment and Payment Schedule

Post  sourav.guha on Mon Dec 06, 2010 11:18 am

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.

sourav.guha

Posts : 5
Join date : 2010-11-26

View user profile

Back to top Go down

Re: Payment and Payment Schedule

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum