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

The Data Source View Wizard and Data Source View Designer automatically define a primary key for a table that is added to a data source view based on underlying database table. However, you may need to manually define a primary key in the data source view.

For performance or design reasons, tables in a data source may not have explicitly defined primary key columns. Named queries and views may also omit the primary key column for a table. If a table, view, or named query does not have a physical primary key defined, you can manually define a logical primary key on the table, view or named query in Data Source View Designer.

Any column can be used for the logical primary key, including a named calculation. When you create a logical primary key, a unique constraint is created in the data source view and marked as a primary key constraint. Any other existing logical primary key specified in the selected table is deleted.

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.

To view instructions about setting a logical primary key in a data source view, see How to: Set or Modify a Logical Primary Key Using Data Source View Designer (Analysis Services)