Thursday, June 30, 2016

SSAS Tabular Part 5 : Review Existing Relationships and Create New Relationships

If your SQL Server database already contain primary Key and foreign key relationship, then  relationships that were created automatically when you imported data table. 

Steps to view existing relationships between tables :
  • Click on 'Grid View' Panel (Bottom Right Corner)  OR click on the Model menu, then point to Model View, and then click Diagram View. (It displaying all of the tables you imported with lines between them)
  • Go to any particular Line and double click on this. (Inside 4 drop-down box, It display the source table with column name and lookup table and it's column name)

In above picture it is a relationship between 'Customer' and 'FactInternetSalesTables. 
CustomerKey is the primary key of 'Customer' and foreign key of 'FactInternetSales'. This is one to Many (1:M) relationship. 


Now we have look on how to establish a relation between tables.

Steps to view existing relationships between tables :
  • In the model designer, Select a lookup table, click and hold on the particular column, then drag the cursor to the primarykey column in the source table, and then release. (A solid line appears showing you have created an active relationship between both tables)
For example we are going to create a one to many (1:M) relationship between 'ProductSubCategory' and 'Product' table.

Now we have click and hold 'ProductSubCategoryKey' in 'Product' table then drag the cursor to the 'ProductSubCategoryKey' of ProductSubCategory table and release it. We can see a new line came in between this tow tables. By Double click on this line we can see the relation description.


There is one more way to create relationship. 
Go to 'Table' menu and then click on 'Create relationship' option. then select both tables with column.



No comments:

Post a Comment