Let's assume we don't use SCD2 on the fact table.

ID SalesPersonID ProductID SaleMonth Quantity Revenue

110110012012-0110100

210110022012-01464

310110032012-01336

Then we bring the table to some Reporting tool like BO, OBIEE, Cognos.

We define the aggregates:

SUM(Revenue) Revenue_SUM,

AVG(Revenue) Revenue_AVG

Now users can design the report by grabbing these elements:

SalesPersonID, SaleMonth, Revenue_Revenue_SUM, Revenue_AVG

This will be the average of all sales per Month and Person.

Reporting tool will generate a query like this:

SELECT SalesPersonID, ProductID, SaleMonth, SUM(Revenue), AVG(Revenue)

FROM fact.SalesDetail

GROUP BY SalesPersonID, ProductID, SaleMonth.

Statistics: 3 deals, 3 products, total quantity 17

Revenue_SUM will be (100+64+36) =200

Revenue_AVG will be (100+64+36)/3 =66.67

Now, let's assume there was a mistake in the source and it's corrected with SCD2.

ID SalesPersonID ProductID SaleMonth Quantity Revenue Audit SourceID

110110012012-012020011

210110022012-0146412

310110032012-0133613

410110012012-01-10-10021

Statistics: 3 deals, 3 products, total quantity 17

Revenue_SUM will be (200-100+64+36) =200

Revenue_AVG will be (200-100+64+36)/4 =50

It's all the same, but AVG is wrong.

Well, it's still possible to figure out some of these aggregates.

For Revenue_AVG would be:

SUM(Revenue) / (COUNT(*) - COUNT(DISTINCT Audit) +1)

Would you be able to write one to show the MIN (Revenue) ?

In this case you need to SUM first to get the full amounts and only then do the MIN.

This is not doable using Reporting GUI for ordinary users.

I don't even dare to think of some more complicated analytical functions here like RunningTotals and etc.

Luckily there are other better solutions.