Search code examples
sql-serverssasolap

SSAS Multidimensional - show fact data that is not present in dimension

I have a fact table, which is identified by ID_Fact column. I also have a dimension, which looks like this:

ID_Dim | ID_Fact | DimAttribute
-------------------------------
1           1          'A'
2           1          'B'
3           1          'C'

Not all Facts will be present in this dimension. How can I model this in SSAS Multidmensional to process all Fact rows, even those without corresponding ID_Fact in the dimension? Unknown members in dimension works for Dimension data that is not present in Fact, right? Is there a way to do this the other way around?

Solution

  • "Unknown members in dimension works for Dimension data that is not present in Fact, right?" -> WRONG.

    You should change the Unknown members option on your dimension to Visible. That is exactly what you are looking for. When you do this, all the data that is on Fact table, but was not found on the dimension, will be related to the Unknown member.