Q:

Include Average in Fact table

This is about data warehouse Question, I heard something about, you are not allowed include Average in the fact table, I am not sure why, can anyone tell me why, an example would be nice, thank you.

A:

Take the following fact table design:
date_id, quantity_sold, amount_sold, avg_price
where avg_price is defined as amount_sold/quantity_sold, which is the avg price across every product.
Now, imagine this scenario:

on date 1 you sell 1 unit of product 1 with price 100 and 99 units of product 2 with price 1;
on date 2 you sell 10 units of product 1 and none of product 2.

You get the following table:
date_id, quantity_sold, amount_sold, avg_price
1, 100, 199, 1.99
1, 10, 1000, 100

There's no way you can ever aggregate the avg_price to give you something meaningful. Avg(100, 1.99) is not the avg price across both days; There's no way to aggregate those two numbers and get the actual average price.
The only way to get an actual avg_price out of that fact table is by fetching the sums of the two primary elements and divide them,
sum( amount_sold) / sum( quantity_sold),

which returns 1199/110 =10.9.
Hence, the avg on that fact table is a pointless number.
Put it in a different way: it's not possible to express an average over a set as a function of averages across different subsets, unless you also know the size of each subset.

