Multiple fact tables for invoice

View previous topic View next topic Go down

Multiple fact tables for invoice

Post  Booma on Fri Apr 11, 2014 3:54 am

I keep track of the status of an invoice in a seperate fact_table, because I want to save the history of status changes.
The fact table is quite simple

fact_invoice_status
date, time, status (open, closed, whatever), invoice_id (DD)

I do the same for payments, since customers can pay only a part of an invoice
fact_payment
date, time, invoice_id (DD), amount (in $), customer

I also have a fact table for sent reminders to customers about unpaid invoices
fact_invoice_reminder
date, time, invoice_id (DD), reminder_type (first, second, etc), customer

If I want to, for example, find all invoices which status is closed, I have to do a weird subquery to find the status (so the last row for each invoice_id) of each invoice.
Is it recommended to have a invoice dimension (which I don't have right now) which has the last status, total payments made of it, and some more attributes. But total payments made is a measure, so the invoice table should probably be a fact?

Another question from management would be: Which percentage of invoices is paid before the first reminder, second reminder, etc. I tried to make this query but it was very ugly with lots of JOINs and subqueries which resulted in slow queries.

I would love to hear your thoughts on this.
avatar
Booma

Posts : 12
Join date : 2014-03-10

View user profile

Back to top Go down

Re: Multiple fact tables for invoice

Post  BoxesAndLines on Fri Apr 11, 2014 7:43 am

You can create an accumulating snapshot fact table from your invoice status fact table. This will pivot all of your invoice statuses to a single row. Counting invoices in any given status no longer requires sub-queries. The invoice dimension is typically a bad idea since it has a 1-1 relationship with the fact table. Once you have any sort of significant volume, this join will will be problematic. Your final report, invoice paid drill across to reminder fact, should also be simplified now with the accumulating snapshot.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Multiple fact tables for invoice

Post  Booma on Fri Apr 11, 2014 8:43 am

BoxesAndLines wrote:You can create an accumulating snapshot fact table from your invoice status fact table.  This will pivot all of your invoice statuses to a single row.  Counting invoices in any given status no longer requires sub-queries.  The invoice dimension is typically a bad idea since it has a 1-1 relationship with the fact table.  Once you have any sort of significant volume, this join will will be problematic.  Your final report, invoice paid drill across to reminder fact, should also be simplified now with the accumulating snapshot.
But I want to keep a history of invoice statuses. How many times did they change, and when? So I think I need them as a fact.
avatar
Booma

Posts : 12
Join date : 2014-03-10

View user profile

Back to top Go down

Re: Multiple fact tables for invoice

Post  BoxesAndLines on Fri Apr 11, 2014 9:09 am

Yes, keep both fact tables. The invoice status fact will be the source for your accumulating snapshot.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Multiple fact tables for invoice

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