As you might know SSAS tabular models do not support composite keys so you always must have just one column to make a unique row through the whole table. This is such a pain especially when you are new to the tabular models and don’t have that much detail information about it. So when you import some tables with existing relationships based on composite keys, the Table Import Wizard will ignore those relationships.
So what should we do to solve the problem?
The solution is to combine the values of the composite keys.
Here is how you can do the job?
· Creating a view on top of the source tables:
1. If you’re using SQL Server 2012 and above you can use the “concat” function to combine the values. The function combines several expressions regardless of their data types. So you can use it like this select CONCAT (1, 1.22100001,‘First’) SQL2012 and the result would be something like this
2. If you’re using earlier versions of SQL Server then you need to mind the data types. So for the above sample the SQL code would be select cast(1 as char(1)) + cast(1.22100001 as char(10))+‘First’ SQL2008 . As we expect the result is the same.
· Adding a new computed column to all tables involved in SQL Server before importing the tables to the tabular model
· Adding a new calculated column to all tables involved after importing the tables to the tabular model
As a quick note, you’ll need to remove the existing relationships imported from SQL Server and create the new relationship based on the combined keys.
Easy peasy!
wouldn’t it cause performance issue for very large table when you have a nvarchar key?
Hi Eric,
You can use hashbytes then cast the results to bigint or int.
SELECT CAST(HASHBYTES('sha2_256', CONCAT (1, 1.22100001,'First')) AS BIGINT) SQL2012Cheers