Store Aggregated data in dimension

The situation is this: I have courses that have enrollment. I capture the registration in the fact table, but I was wondering, what about storing aggregated data in the course dimension? So, I could have the current enrollment of that course.

What is your thoughts?

Thanks!

What about storing the aggregate data in a fact table, where it belongs?

If you have aggregate measures about classes, it's just as easy to get it from an aggregate fact table as anywhere else. And, if the reason you are thinking of doing it so you can include the count in a class list report, why not let the report calculate the count on the fly?

When you are dealing with aggregate measures, you have to deal with two query situations: queries that require a finer grain of detail and those that don't. In the first case, you use the fine grain fact and calculate aggregates if you need them. In the latter case, you should construct an aggregate fact that contains all the measures appropriate for that aggregation. Use that fact to support aggregate queries.

There is no need to store and maintain the aggregate value in a dimension. And besides, it breaks the modeling form, introduces an unnecessary update step, and causes confusion.

Just to be clear the values are in the source database already calculated and sit with the course, so, I wouldn't be doing the calculation, since the source has already done that.

Would you avoid aggregated data from the source and just depend on the grain to build up to those values?

Does it match 100% all of the time? Do you use the aggregated measures to filter facts? If not, don't do it.

I dont think I will include them, because say if something changes between the dimension load and the fact load, it would appear to be wrong. I will just aggregate across what is loaded for conisistancy.

Thanks guys for your input!

chade25 wrote:Would you avoid aggregated data from the source and just depend on the grain to build up to those values?

The decision to aggregate is one of performance. Unless you are dealing with millions of class seats in a semester, I doubt an aggregate is necessary. Even with millions, the detail would all fit into memory these days, even on a $400 PC.

Thanks for all of your replies!