SSAS incremental cube processing shows wrong distinct count

Question

I have created XMLA to process cube in incremental way. It is using type "ProcessUpdate" for dimensions and "ProcessAdd" for measurement partitions. I am facing one issue on distinct count. Let me take one example:

Order Id    CustId  Amount

1       C1  100.00

2       C2  200.00

3       C3  300.00

4       C4  400.00

5       C5  500.00

If we browse cube, SSAS shows sum of orders as 1500.00, and distinct customer count for all orders as 5. Now adding new fact record for cancelling one order, e.g.:

Order Id    CustId  Amount

3       C3  -300.00

After incremental processing, it shows sum of orders as 1200.00 which is correct one. But the distinct customer count for all orders keeps same and shows 5 which is incorrect. I can understand that the rows are getting append on incremental process which works for sum operation, but fails computing distinct count. I want to know if there is any way that can remove order #3 from all aggregate operation while processing in incremental way.

Meff Meff · Accepted Answer · 2013-08-05T13:55:56

Distinct customer count remaining at five is correct, as it doesn't know that a minus 300 means the customer shouldn't show. If you processed the cube fully it would show as 5 distinct customers.

This isn't to do with incremental processing, this is to do with how SSAS handles distinct count - It's just "Count distinct customer Ids in fact table", and C3 is there twice with a sale of 300 and a sale of -300.

You need to reconsider how to handle this, ideally at the stage where you load your data warehouse. You could handle it in MDX by not including anyone who has sales of zero or less, but then the whole distinct count calculation will be done in MDX and will be much, much slower.

SSAS incremental cube processing shows wrong distinct count

1 Answers