Customer Payments - how to design..??

[chewza]

I would like your advice on a star schema that i am working on.

Essentially,
I have Payments with many attributes like (who is paid, what is the payment status, abt 7 different payment dates etc)
Each payment is made up of invoices/source documents.

I have a dimension payment,
a fact expenditure_payment which basically contains all the invoices/source docs info.

I am unsure on whether to include the payment dates into the fact expenditure_payment or onto the dimension_payment. (because the dates actually apply to the payment, not the invoices)

I am also unsure as to create seperate dimension for the invoices/source documents, mainly because it will be a 1 to 1 relationship between dimension invoice and the fact expenditure_payment.

at the moment, i have included all the invoice information onto the fact_expenditure_payment, but not the fact is getting a little wide with a couple description fields.

Many thanks!

 

[RiverGuy]

I haven't yet designed an A/R star schema so I'd be interested to hear anyone else's thoughts who has, but here is how I see it. (I'd also look at some of the Kimball books if you can as they have lots of examples for specific business case designs).

First question--are you sure that each fact record has a 1 to 1 relationship with each invoice? What happens if a customer is invoiced $100 but pays $50 now and $50 later? Secondly, I am assuming a customer might send in say, a $100 payment for two $50 invoices. So, is it possible that each payment would be applied to a fraction of an invoice to many invoices?

Assuming that is the case, the smallest grain, and the grain of the fact table, would be each payment application. Also, if that's the case, then the invoice could definitely be its own dimension since each invoice could cover multiple fact records and assuming it contains useful information.

I would definitely put the date applied in the fact table and reference the date dimension. I'm not sure about the other dates as I'm not an accounting expert and not sure what they are, however, if your users are going to slice on these other dates, then I would put them in as role-playing dimensions. If some of these dates are just extra information describing the payment or invoice, then I would probably leave them out of the fact table.

I would also think about putting the customer key in the fact table for payment applied to and payment made by (assuming you might have a situation where a parent company submits a payment for a subsidiary yet they are considered separate companies).

 

[Hans63]

Riverguy has pointed out many of the thoughts I had.

One additional note.
Depending on the (business) questions you have on all those dates related to payments, you may consider to create another fact table. Kimball calls this an accumulating snapshot.
It collects all different dates in a single record, allowing intrarecord comparison of datevalues.

 

[chewza]

thanks everyone for your response.

I made a typo - it should be supplier payments - we are dealing with creditors payments, not debtors.
My apologies!!!!
The design considerations should be similar though

We need to report on expenditure against suppliers, as well as our payment details. The way I see it, a single fact table should do the trick, and reflect expenditure as well as payment measures for suppliers/invoices. Invoices would just represent a degenerate dimension

We also need to show ageing of accounts

Any thoughts?

 

[johnherman]

We need to get back to basic dimensional analysis here. Payment is an event, a fact, with quantitative fields such as amounts. The attributes of the fact are the dimensions. Attributes of the fact include the date of payment, the customer, the invoice (although the invoice could be another fact with shared dimensions), the order (same as invoice), the product/service, and other codes such as payment type, credit terms, etc.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright