Friday, September 30, 2022
HomeBusiness IntelligenceHow To Implement a Composite Key In SSAS Tabular Mannequin

How To Implement a Composite Key In SSAS Tabular Mannequin

As you would possibly know SSAS tabular fashions don’t help composite keys so that you all the time will need to have only one column to make a singular row by means of 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 whenever you import some tables with current relationships based mostly 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 need to use the “concat” perform to mix the values. The perform combines a number of expressions no matter their information varieties. So you need to use it like this choose CONCAT (1, 1.22100001,‘First’) SQL2012 and the consequence could be one thing like this


2.  When you’re utilizing earlier variations of SQL Server then that you must 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 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 word, you’ll must take away the prevailing relationships imported from SQL Server and create the brand new relationship based mostly on the mixed keys.

Simple peasy!



Please enter your comment!
Please enter your name here

Most Popular

Recent Comments