Skip to content
Advertisement

Accounting Data Warehouse Design Question

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.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement