Forum Discussion
Order / invoice data modelling
I've been chipping away at the Ferrari/Russo 'Analyzing Data with Microsoft Power BI and Power Pivot for Excel' book which has some advice on this. It suggests to use:
Customers <> Orders <> OrdersInvoicesBridge <> Invoices
Orders linked with the bridge via an Order Number & Line key.
Invoices linked with the bridge via an Order Numer & Line & Invoice Number key.
Quantities invoiced can be held in the bridge table.
As my company never puts multiple orders on a single invoice, I think I can actually skip out the bridge table so:
Items / Customers <> Orders <> Invoices
It doesn't solve my issue of Invoices without Orders but I think I will have to DAX my way around that issue. I will update this once I nail more issues down.
I think I have finally come up with a solution to when there are invoices that do not have an order number attached...
In short, the solution is to just append the important data from the invoices table into the orders table so data can then flow from the dimensions through that.
To do this:
- For the invoice table:
- Fill the blank order number fields with the invoice number instead (and add a prefix so there's no possibility of a data clash).
- For the order table:
- Make a new table with invoice data for just the invoices with no order number and add the essential columns for dimension table links (eg. customer and item codes, dates, etc) and the order number (with the same prefix as above).
- Append this table to the orders table.
This doesn't affect any other data and allows filtering to flow properly. I think it'd also be possible with a bridge table but it's a rare occurence for us and it's possible that a bridge table could create larger performance issues.