Aggregate Table Granularity

[darylm74]

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

[ngalemmo]

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.

[darylm74]

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.

[ngalemmo]

Its still a fact table.

[Sponsored content]