•Suppose we are trying to
calculate the average inventory for a product
in all warehouses
•SQL Average function would count up the rows in the fact
table and that would be the cardinality; but
is it? What if a warehouse didn’t have a product?
This is an issue of sparsity.
•Solution: Load
zero rows.
–No. We would
balloon the size of the fact tbl.
•What if we want the average inventory for a product
across the company (assume our grain is
inventory by product by month and we have 4
warehouses)?
–Now we have another problem – a grain
problem. We want to compute an average at a higher level of summarization
than our fact table. In other words, our cardinality using the SQL Average function will be 4 x 12 = 48 when we really want 12
•Solution is retrieve the sum and cardinality components,
then calculate the ratio
–Cardinality could be relative (ie. period averages);
cardinality could computed based on
dimension data or based on facts (ie. Average
product sales by Brand for 1/01 - number of products in a brand would need to be the cardinality in the average
for each brand). The latter would require 2 queries.
–