Cash Targets Data Model Fact

Hi,

I am not sure where to start.

We want to know for each credit controller how much the Amount they have open for each customer, invoice etc..

At the moment each day I am loading into the same table what is currently open and a extra field which is called "Injected Date' which is just a datetimestamp telling me the date of when this loaded into the table.

So if I load into the same table the next day and that invoice is currently not open then it would not appear on that table for that day.

So I'm thinking of adding another field to say when this was paid as this is the only wait to find out when this was paid.

The users want to report on the following:

Amount due - All amount due at end of current month (current month and past)
Amount not due - All amount with due date next months (future)
Paid amount - All payment done in the current month
Due date end of the month - last day of the current month
Start position - Split by due and not due amount.
Target - Target is done in amount and from manual calculation
Current outstanding = Target – Payment received
% achieved -Payment received / target



Thanks

What makes an invoice closed?
You need following dims:
Customer or account dim
Credit controller dim
Date dim

You need daily snapshot fact table with following columns. Data for this Fact table comes from your existing table that you described.

Account id or customer id from the dim
credit controller id from the dim
Date dim id
De-generate invoice number
Invoice Due date
Payment receive date
Paid amount
Un-paid amount