How to: Set or Modify a Logical Primary Key Using Data Source View Designer (Analysis Services)
Primary keys are required in Microsoft SQL Server Analysis Services to uniquely identify records in a table, identify key columns in dimension tables, and to support relationships between tables, views and named queries. These relationships are used 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 set or modify logical primary keys in an Analysis Services project or database.
To set a logical primary key
In Business Intelligence Development Studio, open the project or connect to the database that contains the data source view in which you wish to set a logical primary key.
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 appropriate table in either the Tables or the Diagram pane, right-click the column or columns that you want to use to define a logical primary key, and then click Set Logical Primary Key (or select the column or columns and then click Set Logical Primary Key on the Data Source View menu).
Notice that a key icon now identifies the primary key columns.
Note
The option to set a logical primary key is available only for tables that do not have a primary key.
Note
To locate a table or view, 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).
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 change a logical primary key
In Business Intelligence Development Studio, open the project or connect to the database that contains the data source view in which you wish to modify a logical primary key.
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 appropriate table in either the Tables or the Diagram pane, right-click the column or columns defined as the primary key and then click Delete Logical Primary Key.
Notice that a key icon identifying the primary key columns no longer appears.
Note
The option to delete a logical primary key is available only for tables that do not have a primary key defined from the underlying database table.
Note
To locate a table or view, 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).
To set the new logical primary key in the appropriate table in either the Tables or the Diagram pane, right-click the column or columns that you want to use to define a logical primary key, and then click Set Logical Primary Key (or select the column or columns and then click Set Logical Primary Key on the Data Source View menu).
Notice that a key icon now identifies the primary key columns.
Note
To locate a table or view, 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).
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.