As you may know SSAS tabular fashions don’t help composite keys so that you at all times should have only one column to make a novel row via the entire desk. That is such a ache particularly if you find yourself new to the tabular fashions and don’t have that a lot element details about it. So once you import some tables with current relationships primarily based on composite keys, the Desk Import Wizard will ignore these relationships.
So what ought to we do to resolve the issue?
The answer is to mix the values of the composite keys.
Right here is how you are able to do the job?
· Making a view on prime of the supply tables:
1. When you’re utilizing SQL Server 2012 and above you should use the “concat” operate to mix the values. The operate combines a number of expressions no matter their knowledge sorts. So you should use it like this choose CONCAT (1, 1.22100001,‘First’) SQL2012 and the consequence can be one thing like this
2. When you’re utilizing earlier variations of SQL Server then it’s worthwhile to thoughts the information sorts. So for the above pattern the SQL code can be choose forged(1 as char(1)) + forged(1.22100001 as char(10))+‘First’ SQL2008 . As we anticipate the consequence is identical.
· Including a brand new computed column to all tables concerned in SQL Server earlier than importing the tables to the tabular mannequin
· Including a brand new calculated column to all tables concerned after importing the tables to the tabular mannequin
As a fast observe, you’ll must take away the prevailing relationships imported from SQL Server and create the brand new relationship primarily based on the mixed keys.
Simple peasy!
