How To Implement a Composite Key In SSAS Tabular Mannequin

As you would possibly know SSAS tabular fashions don’t assist composite keys so that you all the time will need to have only one column to make a singular row by the entire desk. That is such a ache particularly when you find yourself new to the tabular fashions and don’t have that a lot element details about it. So whenever 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 high of the supply tables:

1.  Should you’re utilizing SQL Server 2012 and above you should use the “concat” perform to mix the values. The perform combines a number of expressions no matter their knowledge varieties. So you should use it like this choose CONCAT (1, 1.22100001,‘First’) SQL2012 and the outcome could be one thing like this

2.  Should you’re utilizing earlier variations of SQL Server then you might want to thoughts the info varieties. So for the above pattern the SQL code could be choose solid(1 as char(1)) + solid(1.22100001 as char(10))+‘First’ SQL2008 . As we anticipate the outcome is similar.

·         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 notice, you’ll must take away the prevailing relationships imported from SQL Server and create the brand new relationship primarily based on the mixed keys.

Straightforward peasy!