should i convert zeros in measure columns to nulls?

Question

i have a "Sales Orders" measure group, with "Order Amount" and "Open Amount" measures.  90% of the sales order facts have an "Open Amount" value of 0.  a common query, using excel as a client, is customers in "row labels", and sales order open amount in "values", to see open sales order totals.  it occurred to me that if i change all those zeros to nulls, they'll get filtered out automatically via nonempty, with excellent query performance, rather than having to use excel pivot table filters or mdx to filter them out.  any reason not to do this?

Answer 1

the only reason not to replace 0's by NULL is if the 0's have a special meaning e.g. for inventory stock

otherwise its always a good idea to replace 0's by NULL because of
- Performance / NON EMPTY
- Storage / CubeSize

also make sure that you set the NullProcessing-Property of that measure to "Preserve"

---

- www.pmOne.com -