Multiple fact tables for invoice

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.

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.

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.

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