Store Aggregated data in dimension

View previous topic View next topic Go down

Store Aggregated data in dimension

Post  chade25 on Mon Feb 18, 2013 1:19 pm

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!

chade25

Posts : 29
Join date : 2012-04-12
Age : 37
Location : Oregon

View user profile

Back to top Go down

Re: Store Aggregated data in dimension

Post  ngalemmo on Mon Feb 18, 2013 3:41 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Store Aggregated data in dimension

Post  chade25 on Mon Feb 18, 2013 3:48 pm

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.

chade25

Posts : 29
Join date : 2012-04-12
Age : 37
Location : Oregon

View user profile

Back to top Go down

Re: Store Aggregated data in dimension

Post  chade25 on Mon Feb 18, 2013 3:49 pm

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

chade25

Posts : 29
Join date : 2012-04-12
Age : 37
Location : Oregon

View user profile

Back to top Go down

Re: Store Aggregated data in dimension

Post  BoxesAndLines on Mon Feb 18, 2013 4:32 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Store Aggregated data in dimension

Post  chade25 on Mon Feb 18, 2013 4:42 pm

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

Posts : 29
Join date : 2012-04-12
Age : 37
Location : Oregon

View user profile

Back to top Go down

Re: Store Aggregated data in dimension

Post  ngalemmo on Mon Feb 18, 2013 5:12 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Store Aggregated data in dimension

Post  chade25 on Mon Feb 18, 2013 5:55 pm

Thanks for all of your replies!

chade25

Posts : 29
Join date : 2012-04-12
Age : 37
Location : Oregon

View user profile

Back to top Go down

Re: Store Aggregated data in dimension

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