The finance module in our ERP has a general ledger and sub-ledgers (accounts receivable, accounts payable etc.). All the sub-ledgers are rolled up into the GL. Standard accounting schema I believe
I have already built a fact_general_ledger table and the business now requires reporting on AP and AR.
The end goal would be to do a reconciliation between the GL and AP/AR and give more granularity into the journal postings for AP/AR
Should AP/AR be facts or dimensions?
I feel AP/AR are fact tables and they can be joined onto the GL to then give a cross comparison and help with reconciliation
However, I know joining two fact tables is asking for trouble
What is the best design practice for this?
Advertisement
Answer
I would create a transactions table, with an account dimension, where amounts are recorded with + or – signs according to direction, and the accounts dimension takes care of the rolling up of accounts into larger categories.
AP & AR are now just queries for totals of a single account or account category.