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.

Note

The option to set a logical primary key is available only for tables that do not have a primary key.

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.

Note

Columns designated as primary key columns appear in the Tables pane or the Schema pane with a key symbol next to the column.

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)