Dimension that indirectly relates to fact data through intermediate dimension. In this case, [Business Unit] is reference dimension through [Sales] dimension as intermediate dimension
Materialize=True for reference dimension will put the key attribute for the reference dimension in the fact data during processing. Therefore the reference dimension is the same as regular dimension with this setting.
Materialize=False will not include attribute from reference dimension to the fact data during processing. So access reference dimension members will happen on the fly by going through intermediate dimension. As one can see, this may impact query performance. Therefore the default setting is have Materialize=True for reference dimension
What the correct behavior should be
When Materialize is set to False for reference dimension, aggregation should not include any attribute from the reference dimension. The correct behavior should be the processing would raise an metadata validation error. A design change request to implement this has been filed to the next version of Analysis Services.
What is the problem
With this aggregation included in the cube, the aggregation creation routine does not know how to aggregate the measure from fact and the reference dimension attribute as the fact does not have the attribute from the reference dimension, therefore the measure is null for this aggregation. In the pool of aggregations created, there is another aggregation which is a good one that also covers this query, but the good aggregation is at the upper level. After full processing, both aggregations are created. When the query is executed, the query execution engine starts from the top level and goes down to find the covering aggregation, in this case, it picks good aggregation (Aggregation 34) and returns the correct result.
After Process Update on Sales dimension, Process Update will drop all the aggregations that would be affected by the data change, in this case, good agg Aggregation 34 got dropped, but the “bad” aggregation stayed as it is deeper than the good aggregation. So now the query would start picking the “bad” aggregation as the good one was gone, and returned null for the measure.
The direct solution to this problem is to remove this aggregation (Aggregation 30). To keep this aggregation, one would need to set Materialize to True for the reference dimension.
Since Process Update will always drop affected aggregations, it is recommended to rebuild the aggregation by running Process Index after Process Update. In this case, Process Update will recreate the good aggregation and avoid the problem for this particular query.
We will be working with content team on a KB article for this behavior as well.