How to: Add, Delete, View, or Modify a Logical Relationship Using Data Source View Designer (Analysis Services)
Relationships are required in Microsoft SQL Server Analysis Services to identify fact and dimension tables, to construct queries for retrieving data and metadata from underlying data sources, and to take advantage of advanced business intelligence features. Use Business Intelligence Development Studio to delete, modify, or view existing relationships or to define new logical relationships in an Analysis Services project or database.
To add a relationship between tables, named queries, or views
In Business Intelligence Development Studio, open the project or connect to the database that contains the data source view in which you wish to add a logical relationship.
In Solution Explorer, expand the Data Source Views folder, then open the data source view in Data Source View Designer by doing one of the following:
Double-click the data source view.
Right-click the data source view and click Open.
Click the data source view and then on the View menu, click Open.
Open the Add Relationship dialog box by doing one of the following:
Right-click the table, named query or view to which you want to add a relationship in the Diagram pane and then click New Relationship.
Right-click the table, named query or view to which you want to add a relationship in either the Tables pane and then click New Relationship.
On the Data Source View toolbar, click New Relationship.
In Data Source View Designer, in either the Diagram or Tables pane, drag a column from the source table of the relationship to be created to a column in the destination table.
Note
To locate a table, view or named query, you can use the Find Table option by either clicking the Data Source View menu or right-clicking in an open area of the Tables or Diagram panes. For more information, see How to: View Objects in a Data Source View Using Data Source View Designer (Analysis Services).
In the Create Relationship dialog box, do the following:
Select the appropriate table, named query, or view in the Source (foreign key) table list.
Select the appropriate table, named query, or view in the Destination (primary key) table lists.
Select columns from the Source Columns and Destination Columns lists to create a relationship between the two tables.
If Business Intelligence Development Studio detects, by sampling the data in the underlying table, view, or named query, that you have defined the relationship in the wrong direction (from the primary key to the foreign key rather than from the foreign key to the primary key), you will be prompted to reverse the order. To quickly reverse the order, click Reverse.
If Business Intelligence Development Studio detects that a relationship already exists for the columns you have selected, you will be prompted. You cannot define duplicate relationships.
Optionally, in the Description box, type a description for the relationship.
Click OK to define the relationship.
Save the modified data source view by doing one of the following:
On the File menu, click Save Selected Items or Save All.
On the Data Source View Designer toolbar, click Save Selected Items or Save All.
Close the Data Source View Designer in Business Intelligence Development Studio and then click Save when prompted.
To view or modify a relationship between tables, named queries, or views in the Diagram pane
In Business Intelligence Development Studio, open the project or connect to the database that contains the data source view in which you wish to view or modify a logical relationship.
In Solution Explorer, expand the Data Source Views folder, then open the data source view in Data Source View Designer by doing one of the following:
Double-click the data source view.
Right-click the data source view and click Open.
Click the data source view and then on the View menu, click Open.
In the Diagram pane, right-click the relationship that you want to view and click Edit Relationship (or simply double-click the relationship arrow).
Review or modify the relationship in the Edit Relationship dialog box and then click OK or Cancel, as appropriate.
Save the modified data source view by doing one of the following:
On the File menu, click Save Selected Items or Save All.
On the Data Source View Designer toolbar, click Save Selected Items or Save All.
Close the Data Source View Designer in Business Intelligence Development Studio and then click Save when prompted.
To view or modify a relationship between tables, named queries, or views in the Tables pane
In Business Intelligence Development Studio, open the project or connect to the database that contains the data source view in which you wish to view or modify a logical relationship.
In Solution Explorer, expand the Data Source Views folder, then open the data source view in Data Source View Designer by doing one of the following:
Double-click the data source view.
Right-click the data source view and click Open.
Click the data source view and then on the View menu, click Open.
In the Tables pane, locate and then expand the table, view or named query containing the relationship that you wish to view or modify.
Expand the Relationships folder.
The relationships between the selected table, view or named query and other tables, views and named queries appear with the relationship column listed.
To edit or view the relationship, right-click the desired relationship and then click Edit Relationship.
Review or modify the relationship in the Edit Relationship dialog box and then click OK or Cancel, as appropriate.
Save the modified data source view by doing one of the following:
On the File menu, click Save Selected Items or Save All.
On the Data Source View Designer toolbar, click Save Selected Items or Save All.
Close the Data Source View Designer in Business Intelligence Development Studio and then click Save when prompted.
To delete a relationship between tables, named queries, or views in the Diagram pane
In Business Intelligence Development Studio, open the project or connect to the database that contains the data source view in which you wish to delete a logical relationship.
In Solution Explorer, expand the Data Source Views folder, then open the data source view in Data Source View Designer by doing one of the following:
Double-click the data source view.
Right-click the data source view and click Open.
Click the data source view and then on the View menu, click Open.
In the Diagram pane, right-click the relationship that you want to view and click Delete Relationship (or simply click the relationship arrow and then press DELETE).
In the Delete Objects dialog box, verify that you are deleting the desired relationship and then click OK or Cancel, as appropriate.
Save the modified data source view by doing one of the following:
On the File menu, click Save Selected Items or Save All.
On the Data Source View Designer toolbar, click Save Selected Items or Save All.
Close the Data Source View Designer in Business Intelligence Development Studio and then click Save when prompted.
To delete a relationship between tables, named queries, or views in the Tables pane
In Business Intelligence Development Studio, open the project or connect to the database that contains the data source view in which you wish to delete a logical relationship.
In Solution Explorer, expand the Data Source Views folder, then open the data source view in Data Source View Designer by doing one of the following:
Double-click the data source view.
Right-click the data source view and click Open.
Click the data source view and then on the View menu, click Open.
In the Tables pane, locate and then expand the table, view or named query containing the relationship that you wish to view or modify.
Expand the Relationships folder.
The relationships between the selected table, view or named query and other tables, views and named queries appear with the relationship column listed.
To edit or view the relationship, right-click the desired relationship and then click Delete Relationship (or simply click the relationship that you want to delete and then press DELETE).
In the Delete Objects dialog box, verify that you are deleting the desired relationship and then click OK or Cancel, as appropriate.
Save the modified data source view by doing one of the following:
On the File menu, click Save Selected Items or Save All.
On the Data Source View Designer toolbar, click Save Selected Items or Save All.
Close the Data Source View Designer in Business Intelligence Development Studio and then click Save when prompted.