How to create a relationship in an SSDT model based on multiple columns?

Question

I am creating a model for Sqlserver Analysis Services (tabular mode) using SSDT. My core measures are in a table with a combined key on UserId and WeekId. I have relations to other tables on UserId and on WeekId. Now I want to add another relation, based on the combination of the two. However, SSDT does not allow me to enter multiple fields when creating a relation.

What should I do? Should I force the measures on a single key? I am a SSAS newby, so maybe I just miss something really obvious.

score 1 · Accepted Answer · answered Dec 24 '15 at 14:09

1

You can only use a single column in relationships. You will need to concatenate UserID and WeekID in both tables and then build the relationship.

The alternative is to create a surrogate key integer which is a meaningless identifier that represents the UserID and WeekID combination and add it to both tables.

answered Dec 24 '15 at 14:09

GregGalloway

11,355
3
16
47

Could this concatenation happen in a calculated field? Or should I set it up in the source data (using a view maybe) – Teun D Dec 25 '15 at 10:11
Either approach is fine if I recall – GregGalloway Dec 25 '15 at 12:39

How to create a relationship in an SSDT model based on multiple columns?

1 Answers1