Take the 2-minute tour ×

I am building the dimensional model for a data warehouse (as an exercise for a mini-course I am doing) and I want to build an aggregate to speed up queries.

Basically, I want the aggregate to group by:

(PizzaType), (Size), (Month), (Year)

I would have num_pizza_types * num_sizes * num_months * num_years aggregate rows. One row for all combinations of that. But how does this change my dimensional model (or aggregate schema, I suppose)? How is this represented in the dimensional model? Do I remove all columns except month/year from my Date dimension? Do I remove all columns except Size and PizzaType from my Pizza dimension?

share|improve this question

1 Answer 1

You should not change your date or pizza dimensions. What you need is a fact table that keeps your aggregate data.

Therefore you will have Date dimension with these columns:

DateId -Pk







Your Pizza dimension will be like: PizzaId -Pk





And now your fact table which will be linked to the dimensions above using their IDs:

Create Table PizzaSale (keeps the aggregate data): Id

PizzaId -Fk to Pizza dimension

SaleMonthId -Fk to Date dimension

Pizza_total_sale -Aggregate value

... Other aggregate values if needed

So as you can see by joining these 3 tables you can get the number of sold pizzas per month. The type can be got from Pizza dimension.

share|improve this answer

Your Answer


