Invoice Fact with Costs

Hi All

I'm making an invoice transaction fact, the grain is at the invoice line level, fairly similar to the 'Invoice Transactions' example in chapter 5 of the Kimball book. The measures will be invoice revenue invoice costs and invoice gross profit.

Requirements/expectations are emerging whereby some of the users want data available in the fact table that's not at an invoice level. These are effectively General Ledger journal adjustments. An example is that an internal invoice between group companies may have not included freight. So a journal adjustment is made for say £20,000 that accounts for the freight cost that's been missed over the past year. The users want to see this cost at an aggregated level within the reports, as it will affect gross profit at an aggregated level.

This could work technically (e.g. put a -1 or 'Unknown' against the invoice number) but I'm concerned that it doesn't really make sense to have a fact table/model that reports on invoice lines to find a bunch of costs at a higher level that don't relate to any invoices. Would you agree?

Thank you

Yep, but I'm not your user. Unless you have some method of allocating to the invoice line or even invoice, I don't know how you accomplish what they want.

And they shouldn't be using invoices to calculate gross profit. Invoices are accounts receivable. Consider implementing the GL as a new subject area.

Thanks both for the replies,

BoxesAndLines - I can accomplish what they want, within the Analysis Services cube that will sit on top of the fact. Essentially they would only be able to see these costs/adjustments at the aggregated levels. It will work technically, but it just feels a bit wrong and messy to me.

ngalemmo - I could well implement the GL as a subject area. However, as they definitely want to analyse some costs at the invoice level and then some costs that are basically journal adjustments, then I'm struggling to see how the two worlds would come together. If I did implement GL as a new subject area, do you think I could include invoice number and product as dimensions? Looking at Chapter 7 in the DW toolkit there are some designs for a GL fact, but they are essentially just by the dimensions Post Date, GL Account and GL Organisation - they look quite basic.

You cannot analyze all costs at an invoice level if some of those costs are journal entries made once a month (or worse, at the end of the year). At best you can allocate these other costs and apply them as invoice lines, but it is an approximation. Also developing the allocation is a project unto itself. One may choose to allocate based on the value, weight, or a variety of other methods. I have seen groups spend months trying to come to agreement (or at least a consensus) as to how numbers like those are allocated back to transactional data. I have also seen organizations change their business practice and book such costs on the transaction to avoid allocation and get an accurate picture of the transaction. After all, the process of shipping to an internal organization is no different than shipping to a customer.

As far as invoices go, they are a subject area in its own right. If the invoice detail has account numbers associated with them, it could be integrated with GL data.

As far as a GL subject area goes, you are correct, they tend to be very simple from a modeling point of view (a GL is in fact a very early form of dimensional modeling), but very complex when it comes to reporting. Most GL systems have a very sophisticated reporting system to produce financial reports. It's primary use, in this case, would be as a source for the numbers if you go the allocation route.

Thank you, I see what you mean about allocating the costs down to the invoice level. I guess that's the best way to do it, but I take your warning about how difficult that could be.

To put it another way, can I build a GL fact that is extended to include invoice related dimensions such as Invoice Customer, Invoice Number, Invoice Line Number? I expect the answer is no as not every row in a GL fact would have an invoice number, but I must admit I'm not 100% sure.

A journal feeding into a GL is usually an aggregate of the activity that generated it. It is unlikely it references back to the invoice. But, you never know until you check. So it may be possible, but it doesn't solve your original problem since the adjustments did not come from an invoice.

You cannot expand the dimensions of a GL journal fact simply because journals come from AR, AP, Fixed Assets, Payroll, and a host of other systems. If the journals contain a reference back to documents, the fact would be a mile wide with all the possible things it could reference. Besides, such a structure would not provide any more information than what your invoice fact already provides. All a general ledger is is a consolidation of the sub-ledgers. Accounts Receivable is one of those sub-ledgers, which is made up of invoicing and cash receipts. It is also atypical to build a journal fact for a GL subject area, primarily because it is redundant and doesn't have much analytic value.

If the information is available, you would carry an account dimension that references the revenue account on the invoice detail so it can be tied to the GL.

You're right, it doesn't solve the problem that the adjustments don't come an invoice.

That's my concern over the fact table being too wide - it sounds like it was a valid concern.

Thanks very much for your help