How to: Set or Modify a Logical Primary Key Using Data Source View Designer

Primary keys are required in Microsoft SQL Server 2005 Analysis Services (SSAS) 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

1. 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.

2. 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.

3. 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.

1. 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

1. 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.

2. 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.

3. 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.

1. 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.

1. 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.

See Also

Tasks

How to: View Objects in a Data Source View Using Data Source View Designer

Concepts

Defining Logical Primary Keys in a Data Source View (Analysis Services)

Other Resources

Working with Data Source Views How-to Topics (SSAS)
Working with Data Sources How-to Topics (SSAS)

Help and Information

Getting SQL Server 2005 Assistance