So Martin/Alvin

After our discussion last week, I went back to have a look at my data model and found the problem - thanks for Martin's suspicion of missing dimension keys.

Turns out due to my DimDate joining to my FACT table by a key MMYYYY (32015), there were problems when the cube refreshed as this key was not necessarily unique. I noticed that in Excel, the issue with the disappearing values happened when i selected and deselected dates from the filter drop down.

I overhauled the entire model and rebuilt the DimDate this time with surrogate keys (now unique) and with the date grain added. Now the FACT table has a proper way to join and the missing values issue is - well no longer an issue.

Thanks for the tip Martin - didnt think this would ever come back and bite me but it did.