Aggregate Table Granularity

View previous topic View next topic Go down

Aggregate Table Granularity

Post  darylm74 on Wed Sep 04, 2013 2:50 pm

The business table would like a financial table aggregrated at the "annual" level per site.

Revenue (could come in monthly, semi-annual, quarterly or annual)
Standard Expense (M/Q/SA/Annl)
Taxes (usually one month a year)
Repair and Maintenance (random)

They want this rolled up at the site level. This would put it at the same level as my site dimension which is a conformed dimension across the general ledger, monthly budget, etc. fact tables.

Do these aggregates belong in a fact table since they are on a site by site basis even though they are at the same level as the dimension?

Thanks,

Daryl

darylm74

Posts : 7
Join date : 2012-09-17

View user profile

Back to top Go down

Re: Aggregate Table Granularity

Post  ngalemmo on Wed Sep 04, 2013 3:50 pm

They are measures that belong in a fact table. The fact table is not at the same level as the dimension... do you not have time (year) as a dimension as well? I would also think there would be a GL account dimension as well.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Aggregate Table Granularity

Post  darylm74 on Wed Sep 04, 2013 4:16 pm

There are G/L accounts at the detail level but not at the aggregate. There is also not a time period. It is "how does it look today". Everything rolls to an annual amount so in the end this is what they want:

site id
annual lease expense amount
annual site revenue amount
annnual property tax amount
annual repair / maintenance amount
etc.

The annual lease amount consists of only one object account and they exclude about 12 sub accounts. While I could roll the sub accounts to an annual amount it would serve little if any business purpose. If there was an issue, they would go back and look at the G/L fact that is already in place. The aggregation takes some time even with partitioning/bitmapping/etc. and the customers are demanding in this area, thefore we wouldn't roll up off the G/L fact and the other fact tables required. The idea is that by tying it to the site dimension, they can pull other site related info stored in other fact tables across our conformed site dimension.

I am consistently finding that the way our business works rarely fits into the neat little order/invoice scenarios when reporting. We are heavily project driven (date / milestone) and the financial amounts are often just indicators except for those in accounting.

darylm74

Posts : 7
Join date : 2012-09-17

View user profile

Back to top Go down

Re: Aggregate Table Granularity

Post  ngalemmo on Wed Sep 04, 2013 4:28 pm

Its still a fact table.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Aggregate Table Granularity

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