Bridge table for chart of accounts

Hi!

I am creating a data model for an Accounting data mart. The GL accounts table has 600K rows. This is because it is a consolidation of accounts for 100+ stores. On the corporate side, there are multiple charts of accounts that roll up differently. (SEC, Ops, etc.). These are represented currently in a recursive Account Hierarchy table (ragged). That table has 1,400 records representing different levels/classifications of charts/accounts. I followed one tree and came up with 12 or 13 levels deep. The Accounting Detail fact table is expected to have about 250M rows.

It appears that the only way to manage this relationship is to have a bridge table between my account dimension, and a Chart dimension. So, this portion of the warehouse would look like: AccountingFactTable <----AccountDim<---AcctChartBridge<---ChartDim. I am just concerned with the number of rows that are going to end up in the bridge table when I build it and the what the overall performance will be.

I can probably get the account dimension down to under 200,000 rows by taking references to store out of it (that is my plan regardless), and making it purely the account info. But there is still enough non-standard classification/use of accounts to make a high number of distinct values for the account dim. (there is a data quality initiative underway that should address this in the future, thankfully)

Will a bridge table work well for me with this volume of dimension data?

At any rate... The only other option I can think of is to pre-aggregate values to the chart group levels in a separate model- thereby removing the need for the Account there, and leave the chart out of AccountingDetail datamart.

Thanks in advance for responses!
DM

Bridge table should be fine.

If you experience some performance issues due to the size of the bridge, you can always consider partitioning the bridge based on chart (SEC, OPS etc).

With a partition, the BI layer/tool can prune the partition it requires for a given report. (assuming that a specific report will only use one chart).