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?


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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.