should i convert zeros in measure columns to nulls?


  • 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?
    19 เมษายน 2555 6:59


  • 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"

    - -

    • แก้ไขโดย Gerhard BruecklMVP, Editor 19 เมษายน 2555 7:17
    • เสนอเป็นคำตอบโดย Lola Wang 20 เมษายน 2555 8:20
    • ทำเครื่องหมายเป็นคำตอบโดย almaplayera 20 เมษายน 2555 8:22
    19 เมษายน 2555 7:17