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

View previous topic View next topic Go down

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

Post  SnowShine429 on Fri Nov 07, 2014 11:23 pm

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…



SnowShine429

Posts : 36
Join date : 2013-02-16

View user profile

Back to top Go down

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

Post  ngalemmo on Sat Nov 08, 2014 4:19 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  SnowShine429 on Sat Nov 08, 2014 12:16 pm

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!

SnowShine429

Posts : 36
Join date : 2013-02-16

View user profile

Back to top Go down

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

Post  SnowShine429 on Tue Nov 11, 2014 6:25 pm

any thoughts on this? can anyone please help?

SnowShine429

Posts : 36
Join date : 2013-02-16

View user profile

Back to top Go down

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

Post  ngalemmo on Tue Nov 11, 2014 10:50 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  BoxesAndLines on Wed Nov 12, 2014 7:01 pm

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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

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

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