Should this really be two different fact tables and not one?

Hi all,

 
I am working on a data model and am extremely puzzled on how to model our Accounts Receivable (AR). Below is a summary of the business process.
 
Bills - Finance sends out the bill to customer. The bill information is stored in Bills table. A single Bill to a given customer often means multiple records in the Bills table (one for each product the customer bought etc.). A given Bill can be posted on multiple dates as shown in the image below.
 
Payments - Finance receives payment from customer. The payment information is stored in Payments table. A single payment from a given customer often means multiple records in the payments table (one for each partial payment etc.) As with Bill, a payment can be posted on multiple dates.
 
One of the main objectives of the business is to track the AR balance. If I put these two tables into 2 different fact tables, here are my challenges that I can’t figure out:
 
1.       How can I calculate the balance since there is not a one-to-one relationship between the two fact tables? Believe it or not, but sometimes, we have one bill for the same product and customer and same order date but posted on two different dates; so there could be a man-to-many join between the two tables.
2.       We are using QlikView for reporting and the way QlikView works is it creates a “giant table” in memory by joining all the tables so this will cause issues by inflating the numbers (many-to-many join).
 
I have been thinking about making a single fact table by “proportioning” or “normalizing” (not sure what the right word) the tables. I know this sounds stupid, but I would love to be proved wrong.
For example, let’s say we sent a bill for 100$ but got two separate payments (50$ + 50$) on two different dates. This scenario would create on row in the bill table and two in the payment table in the source system. In the fact table, I’ll have two rows where the bill info will repeat on the second row except for the amount which gets divided by 2 (“proportioning” / “normalizing”) and the post date.
 
Any help in this matter would be highly appreciated…

Why do you want invoices and payments on the same row? As you have said yourself, it is a M:M relationship between invoices and payments.

You can put them in the same table if the dimensions are consistent, but as separate rows with a transaction type dimension.

Thank you very much, ngalemmo. As I mentioned before, the reason I am thinking of putting them in one fact table (one row) is because I would have to deal with it anyway when reporting and it is creating a nightmare. But I am "proportioning" the bill/payment amount by dividing it with the number of rows in the payment/bill amount respectively as shown in my screen shots.

By "dimensions are consistent", do you mean if the dimensions are same then yes. My question is, wouldn't it be too difficult to do reporting if I put them in 2 different fact tables? 

Also, if I put them in separate rows in the same fact table, wouldn't it be too difficult to do reporting off of it (example computing AR balance etc)? please advise and thanks again!

any thoughts on this? can anyone please help?

The core of a good dimensional model is based on maintaining detailed atomic level facts, such as billing and payment fact tables. Such facts can then be combined in queries, or you can always create an aggregation of the two to suite particular use cases when needed.

Billing and receiving are two different business processes. You need two different facts. Your two facts aren't even at the same grain. What ngalemmo said. If Qlikview can't do simple drill across queries without building a massive table, you will be spending all your time trying to circumvent basic dimensional modeling principles for a tool. Not a great way to spend your day.