We have an SSAS OLAP cube with some dimensions defined as KeyNotFound -> IgnoreError and KeyErrorAction -> ConvertToUnknown
One of this dimensions (and only one) doesn't work as expected, when the fact table value is not found on the dimension the values are not considered.
In other words, this particular dimension acts as if it were defined as KeyErrorAction->DiscardRecord
Looking at the detail log of the building process it's clear that the behaviour must be this one, as the select looks like (simplified)
Select fields_from_fact_table,dim_key from fact_table,dim_table where fact_table.value_key=dim_table.dim_key
A join like this one only returns records present on the dim_table and this is the reason we cannot see the non-existent values.
But we cannot see any reason why this occurs. Why this specific dimension and not another one? Using a join with a dimension defined as DiscardRecord would be logical (yes, there are some dimensions that could be choosed on our cube) but using one defined as ConvertToUnknown just defeats the declaration itself.
The only reason we can imagine is that this particular dimension is used as the intermediate dimension of another "referenced" dimension (unsure of the correct name in English, it's the case when the fact_table links to a dimension and this one to a second one).
If this was the case SSAS should warn with an error when you select the ConvertToUnknown method.
A diferent fact_table with diferent dimensions, uses also a join (no referenced dimensions here), so it seems a join is always used.
Any idea about what is happening? How can we make this dimension work as expected?
