I could use some input on how to handle invoice adjustments

View previous topic View next topic Go down

I could use some input on how to handle invoice adjustments

Post  falcon00 on Thu Dec 19, 2013 4:54 pm

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?

falcon00

Posts : 17
Join date : 2013-11-07

View user profile

Back to top Go down

Re: I could use some input on how to handle invoice adjustments

Post  falcon00 on Thu Dec 19, 2013 5:30 pm

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

View user profile

Back to top Go down

Re: I could use some input on how to handle invoice adjustments

Post  ngalemmo on Thu Dec 19, 2013 5:36 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: I could use some input on how to handle invoice adjustments

Post  falcon00 on Thu Dec 19, 2013 6:05 pm

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

View user profile

Back to top Go down

Re: I could use some input on how to handle invoice adjustments

Post  ngalemmo on Fri Dec 20, 2013 12:33 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: I could use some input on how to handle invoice adjustments

Post  falcon00 on Fri Dec 20, 2013 11:49 am

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

View user profile

Back to top Go down

Re: I could use some input on how to handle invoice adjustments

Post  ngalemmo on Fri Dec 20, 2013 1:07 pm

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).
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: I could use some input on how to handle invoice adjustments

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