Your fact table can't say which products sold. No index can fix it.

transcript

Product managers want revenue by category. Your table has every order. And it still can't answer. Not with an index. Not with a view.

Here's the fact table. One row per order. An order ID. A total amount. And an item count. So which products were in that order? The row doesn't say.

That's the grain. The level of detail each row stores. Order rows never recorded the products. No view or partition can bring them back.

One row per order keeps a total, and loses the products. One row per line item keeps the product, quantity and price.

From line items, you can roll up to orders. Or to categories. Any level above. But orders can never drill back down to their items.

So define the grain first. The lowest level of detail you'll need. You can always roll up. You can never drill below your grain.

Full breakdown in the lecture.