Bridge table for chart of accounts

View previous topic View next topic Go down

Bridge table for chart of accounts

Post  dmorgan on Thu Feb 03, 2011 10:41 am

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

dmorgan

Posts : 1
Join date : 2011-02-03

View user profile

Back to top Go down

Re: Bridge table for chart of accounts

Post  ngalemmo on Thu Feb 03, 2011 12:03 pm

Bridge table should be fine.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Bridge table for chart of accounts

Post  LAndrews on Thu Feb 03, 2011 1:22 pm


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).

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Bridge table for chart of accounts

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum