Invoice and Credit Notes

Hi

I'm looking into creating the fact table for the Issue Billing Document is our SAP implementation. The grain is set to be on the line item of the billing document, and I'm facing a design question, that I wish to share with you.

The credit note in SAP, is also a billing document, just a different type of a document. Each credit note has a reference to the original billing document it is crediting. So there is a self reference between billing documents depending on the type:
-Some billing documents of type invoice will have a reference to a credit note
-All billing document of type credit note will have a reference to the invoice

If I add an affiliate dimension to the fact table for the credit note to invoice relation, I have no valid value to give it for the billing documents that are not of type of a credit note. I could give it a default value, but I do not know if that is a good design.

Assuming I'm not the first to facing the invoicing and credit problem, I wonder if you can share your insight on the matter. Do you keep the credit note i a separate fact table? If no, do you maintain the reference between the billing documents? How?

Regards
Rafi Asraf

Hi,

I have a couple of thoughts on this:

1) You state the grain of the fact is the line item - therefore to relate records in this fact table you would need to be able to link a billing document line item to a credit note line item. If the only link you have (or can calculate) is at the document level then you need a new fact table (with a grain of the document rather than the document line) - assuming you want to link them.

2) What is the reporting requirement that is forcing you to model this relationship in your DW? Understanding this will drive your design e.g.
i) If you want to know the balance for a customer you don't need to model the relationship as you would just sum your measures by your customer dimension in the BI tool you are using.
ii) If you are trying to model the lifecycle of a transaction then you could possibly use an accumulating snapshot fact table

Hope this is of some help?

Regards

Assuming the billing document # itself is a degenerate dimension, and that invoices and credit notes are going into the same fact table, why not simply have a 'reference document' column that is populated with the invoice document # on the credit note? (Or if you actually have an invoice dimension, just add another FK column)