Invoice dimensional modeling question

[blah79]

Some questions regarding the creation of a dimensional model for telecom invoice and call data.

Given an invoice that consists of three levels:

invoice -> invoice lines -> calls.

Not all the invoice lines have calls (i.e. general charges) and not all calls are billed, therefore the data needs to be available at the both the call and invoice line level.

I have created one fact table for the calls and one fact table for the invoice lines and have moved as much data from the (degenerate) invoice dimension to the invoice line fact table as possible. However, I am left with some data that cannot be allocated to the invoice line level, such as payment data.

My questions are:

a) What should I do with payment data and other data that cannot be allocated to individual invoice lines? Create an invoice dimension? Maybe create a separate fact table for payments?

b) Is it allowable to link between the lowest grain call fact table and the summarized invoice line fact table? Should I treat the invoice line fact table as a dimension table in this context? If not, how should I match calls to invoice lines?

Any help and/or tips would be greatly appreciated.

 

[johnherman]

a) The payment event is separate fact from the invoice and should be kept separately. This also allows you the ability to have one payment for multiple invoices or multiple payments for one invoice (partial payment or payment plans).
b) The invoice becomes a degenerate dimension (Kimball terminology) and there should be a foreign key from Call to Invoice.

http://www.kimballgroup.com/

If it is desirable to have an invoice summary fact table, then by all means create a separate summary/aggregation table for the invoice as a whole.

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