Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Store Aggregated data in dimension

3 posters

Go down

Store Aggregated data in dimension Empty Store Aggregated data in dimension

Post  chade25 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 : 42
Location : Oregon

Back to top Go down

Store Aggregated data in dimension Empty Re: Store Aggregated data in dimension

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

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

http://aginity.com

Back to top Go down

Store Aggregated data in dimension Empty Re: Store Aggregated data in dimension

Post  chade25 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 : 42
Location : Oregon

Back to top Go down

Store Aggregated data in dimension Empty Re: Store Aggregated data in dimension

Post  chade25 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 : 42
Location : Oregon

Back to top Go down

Store Aggregated data in dimension Empty Re: Store Aggregated data in dimension

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Store Aggregated data in dimension Empty Re: Store Aggregated data in dimension

Post  chade25 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 : 42
Location : Oregon

Back to top Go down

Store Aggregated data in dimension Empty Re: Store Aggregated data in dimension

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

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

http://aginity.com

Back to top Go down

Store Aggregated data in dimension Empty Re: Store Aggregated data in dimension

Post  chade25 Mon Feb 18, 2013 5:55 pm

Thanks for all of your replies!

chade25

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

Back to top Go down

Store Aggregated data in dimension Empty Re: Store Aggregated data in dimension

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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