‘A fact relationship cannot be defined. The dimension and measure group must be based on the same table in the same data source view.’
If you get the above message when defining the relationship type as Fact between the measure group and Degenerate/Fact dimension, what could possibly go wrong especially when the second part of the message clearly tells what needs to happen?
Screen Capture 1 - A fact relationship cannot be defined
Obviously the grain of the Degenerate/Fact dimension must be same as the unique key of the measure group. In other words the Degenerate/Fact dimension will have as many records as the measure group itself.
That apart – if the measure group is based on a fact table/database view/named query, then the Degenerate/Fact dimension must also be sourced from the same fact table/database view/named query respectively. For instance, if your measure group is based on a database view, then your Degenerate/Fact dimension must not be sourced from the fact table even though the view definition of the measure group references the same fact table and have the same grain. SSAS considers these two objects as different sources and hence you will get the above message. You could still link such a dimension to the measure group by a regular relationship. However this will result in duplication of the dimension data resulting in bigger cube which could have potential query performance when compared to Degenerate/Fact dimension.
Discussion
No comments yet.