I could use some input on how to handle invoice adjustments
2 posters
Page 1 of 1
I could use some input on how to handle invoice adjustments
In the ER world an invoice can have many adjustments. I'm struggling on how to properly represent this in the dimensional world.
I have an invoice fact table. That was easy enough. My inclination was to take invoice adjustments, put them in their own column like thus:
fact_invoice([various FKs], invoice_amount, invoice_adjustment)
Then when a new adjustment came in I'd just add it to the existing adjustment and the grain of my fact table is preserved. Then I thought, "what if some user wants to see all the individual adjustments?". Now I'm in trouble.
I'm not sure how to capture individual adjustments and then relate them back to the invoice they belong to. I mean you can't attach a fact to a fact table can you?
I have an invoice fact table. That was easy enough. My inclination was to take invoice adjustments, put them in their own column like thus:
fact_invoice([various FKs], invoice_amount, invoice_adjustment)
Then when a new adjustment came in I'd just add it to the existing adjustment and the grain of my fact table is preserved. Then I thought, "what if some user wants to see all the individual adjustments?". Now I'm in trouble.
I'm not sure how to capture individual adjustments and then relate them back to the invoice they belong to. I mean you can't attach a fact to a fact table can you?
falcon00- Posts : 17
Join date : 2013-11-07
Re: I could use some input on how to handle invoice adjustments
What if I treated an adjustment as a negative invoice? I haven't fully looked at this yet but in general it would look just like any other invoice in the fact table except the amount field would have a negative value.
falcon00- Posts : 17
Join date : 2013-11-07
Re: I could use some input on how to handle invoice adjustments
What grain are you trying to preserve? And why? What's wrong with one row per activity?
Treat it as a series of transactions that in total affect the amount of the invoice. Include a type dimension to indicate the original invoice or an adjustment. Have one $ column and populate it with the net change.
An alternate approach is to implement an accumulating snapshot the contains multiple rows over time with restatements of the value of the invoice. The row would contain effective and expiration dates so you can locate the current or previous version of an invoice.
Treat it as a series of transactions that in total affect the amount of the invoice. Include a type dimension to indicate the original invoice or an adjustment. Have one $ column and populate it with the net change.
An alternate approach is to implement an accumulating snapshot the contains multiple rows over time with restatements of the value of the invoice. The row would contain effective and expiration dates so you can locate the current or previous version of an invoice.
Re: I could use some input on how to handle invoice adjustments
The grain of my fact table is one invoice. Unlike a lot of text book examples we're not concerned with the line items of the invoice just the invoice total. I like your first approach because it retains the individual adjustments like I want to get to. The only problem is that there are facts that related to the invoice but not to the adjustment. So in that instance do I leave those facts empty or duplicate them from the original invoice with the only difference being the value of the type dimension?
falcon00- Posts : 17
Join date : 2013-11-07
Re: I could use some input on how to handle invoice adjustments
If you load individual rows for each adjustment, measures should represent the net change. If a particular measure isn't changed by the adjustment, its value would be zero. You need to sum all rows by invoice to get the current balance of the invoice.
You would also include the adjustment ID as a degenerate dimension. It would be blank on the original invoice row.
You would also include the adjustment ID as a degenerate dimension. It would be blank on the original invoice row.
Re: I could use some input on how to handle invoice adjustments
Ah I see! Thank you. Adjustments don't really have natural keys here but if I attach it to the invoice ID I think I can make it still work right.
falcon00- Posts : 17
Join date : 2013-11-07
Re: I could use some input on how to handle invoice adjustments
Right, you would always have the invoice ID as one of the dimensions. You may want to add degenerate dimension flag to distinguish the original from the adjustments, or you can do it with two date dimensions, one for the invoice date and the other with the adjustment or transaction date (which presumably, would be the same for the original invoice).
Similar topics
» Implementing sales adjustments in DW,
» Drill-across relationships (Invoices -> Payments etc.)
» Modeling Descriptive Flex Fields (Attribute Columns) on an Order Line
» Data "input" tool
» tracking input to output (or purchase to sales for that matter)
» Drill-across relationships (Invoices -> Payments etc.)
» Modeling Descriptive Flex Fields (Attribute Columns) on an Order Line
» Data "input" tool
» tracking input to output (or purchase to sales for that matter)
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum