Fact Table Design RRS feed

  • Question

  • I'm stumped with a dw design.

    I am (I think appropriately) bringing in all my invoice records for "Charges" at the time invoices get posted into AR.

    The challenge I'm having is when payments get posted, months and sometimes years later :( and not all payments and credits can are identified at the product level.  So how would you design a fact table for payments that reflects

    Payment which Adjudicate Charges for specific Products and payments/credits that do not reflect any specific product but it applies at the entire invoice level?

    See Screenshot.


    • Edited by siera_gld Tuesday, March 29, 2016 7:46 PM
    Tuesday, March 29, 2016 7:45 PM

All replies

  • The picture has broken link, please re-upload.
    Wednesday, March 30, 2016 10:08 AM
  • Accounting practices are well defined and documented.  You should not reinvent the wheel.

    In the case of AR, most software allows the user to optionally "apply" a payment to invoice(s).  If your software does not do that, then all you can do is a running total.  However, you also have to understand partial payments, interest charges, disputed charges, etc. 

    This is an age old problem with AR systems and there is no way to do what you are trying to do.  Payments do not apply to a "product" they apply to a customer account.  There is no way to know what the customer paid for at a given time.

    Wednesday, March 30, 2016 11:04 AM
  • Tom thank you for your input; however, the source system is the application db which is already created and functions as expected when it relates to accounting practices.

    I am not creating an Application. I'm creating a DW  in which DW designs are different than application db designs.


    Wednesday, March 30, 2016 4:25 PM
  • If the accounting system does not attach payments to a product, you cannot invent that relationship.  I realize users may want that, but the data to support that relationship does not exist.

    Wednesday, March 30, 2016 5:48 PM