Defining a Data Source View (Analysis Services)
In Microsoft SQL Server Analysis Services you use the Data Source View Wizard in Business Intelligence Development Studio to define a new data source view in an Analysis Services project or database based on a single data source. You perform the following tasks using the Data Source View Wizard.
Select a previously defined data source, or define a new data source for the data source view (which launches the Data Source Wizard). This data source becomes the primary data source for the data source view and this designation cannot be changed. In order for Analysis Services to reference multiple data sources in a single processing query (such as for a snowflake dimension), the primary data source that you specify in the wizard must support OPENROWSET queries (typically this will be a SQL Server data source).
Select tables and views from the primary data source to add to the data source view. You can also add tables that are related to those you select based on foreign key relationships or name matching criteria.
Note
You cannot add tables or views from multiple data sources when you are initially defining the data source view using the Dimension Wizard. You can only add tables and columns from a secondary data source after you have initially defined the data source view. For more information, see Adding or Removing Tables or Views in a Data Source View (Analysis Services).
Specify Name Matching Criteria for Relationships
When you create a data source view, relationships are created between tables based on foreign key constraints in the data source. These relationships are required for the Analysis Services engine to construct the appropriate OLAP processing and data mining queries. Sometimes, however, a data source with multiple tables has no foreign key constraints. If a data source has no foreign key constraints, the Data Source View Wizard prompts you to define how you want the wizard to attempt to match column names from different tables.
Note
You are prompted to provide name matching criteria only if no foreign key relationships are detected in the underlying data source. If foreign key relationships are detected, then the detected relationships are used and you must manually define any additional relationships you want to include in the data source view, including logical primary keys. For more information, see Defining Logical Relationships in a Data Source View (Analysis Services), How to: Add, Delete, View, or Modify a Logical Relationship Using Data Source View Designer (Analysis Services), Defining Logical Primary Keys in a Data Source View (Analysis Services), and How to: Set or Modify a Logical Primary Key Using Data Source View Designer (Analysis Services).
The Data Source View Wizard uses your response to match column names and create relationships between different tables in the data source view. You can specify any one of the criteria listed in the following table.
Name matching criteria |
Description |
|---|---|
Same name as primary key |
The foreign key column name in the source table is the same as the primary key column name in the destination table. For example, the foreign key column Order.CustomerID is the same as the primary key column Customer.CustomerID. |
Same name as destination table name |
The foreign key column name in the source table is the same as the name of the destination table. For example, the foreign key column Order.Customer is the same as the primary key column Customer.CustomerID. |
Destination table name + primary key name |
The foreign key column name in the source table is the same as the destination table name concatenated with the primary key column name. A space or underscore separator is permissible. For example, the following foreign-primary key pairs all match: Order.CustomerID and Customer.ID Order.Customer ID and Customer.ID Order.Customer_ID and Customer.ID |
The criteria you select changes the NameMatchingCriteria property setting of the data source view. This setting determines how the wizard adds related tables. When you change the data source view with Data Source View Designer, this specification determines how the designer matches columns to create relationships between tables in the data source view. You can change the NameMatchingCriteria property setting in Data Source View Designer. For more information, see How to: View or Change the Properties of a Data Source View Using Data Source View Designer (Analysis Services).
Note
After you complete the Data Source View Wizard, you can add or remove relationships in the schema pane of Data Source View Designer. For more information, see Defining Logical Relationships in a Data Source View (Analysis Services).
Creating a Data Source View
To view instructions about how to create a data source view, see How to: Define a Data Source View Using the Data Source View Wizard (Analysis Services).
See Also