Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

Day

Month

Quarter

Year

DateName

....

Your Pizza dimension will be like: PizzaId -Pk

PizzaName

PizzaSize

PizzaType

...

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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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