Defining a Data Source View (Analysis Services)

Makale
07/30/2013

A data source view contains the logical model of the schema used by Analysis Services multidimensional database objects—namely cubes, dimensions, and mining structures. A data source view is the metadata definition, stored in an XML format, of these schema elements used by the Unified Dimensional Model (UDM) and by the mining structures. A data source view:

Contains the metadata that represents selected objects from one or more underlying data sources, or the metadata that will be used to generate an underlying relational data store. For more information, see Schema Generation Wizard (Analysis Services), and Supported Data Source Types (SSAS Multidimensional).
Can be built over one or more data sources, letting you define multidimensional and data mining objects that integrate data from multiple sources.
Can contain relationships, primary keys, object names, calculated columns, and queries that are not present in an underlying data source and which exist separate from the underlying data sources.
Is not visible to or available to be queried by client applications.

Creating a DSV is not required, but unless you are using other approaches to abstract the data, it is strongly recommended that you include them in your project and use them as a basis for building your multidimensional database.

This topic includes the following sections:

Data Source View Composition

Create a DSV Using the Data Source View Wizard

Specify Name Matching Criteria for Relationships

Add a Secondary Data Source

Data Source View Composition

A data source view contains the following items:

A name and a description.
A definition of any subset of the schema retrieved from one or more data sources, up to and including the whole schema, including the following:
- Table names.
- Column names.
- Data types.
- Nullability.
- Column lengths.
- Primary keys.
- Primary key - foreign key relationships.
Annotations to the schema from the underlying data sources, including the following:
- Friendly names for tables, views, and columns.
- Named queries that return columns from one or more data sources (that show as tables in the schema).
- Named calculations that return columns from a data source (that show as columns in tables or views).
- Logical primary keys (needed if a primary key is not defining in the underlying table or is not included in the view or named query).
- Logical primary key - foreign key relationships between tables, views, and named queries.

Create a DSV Using the Data Source View Wizard

To create a DSV, run the Data Source View Wizard from Solution Explorer in SQL Server Veri Akışı Araçları (SSDT), specify a data source object that provides connection information to an external relational database, and then choose which objects to include in the DSV.

To restrict the schemas that are retrieved from the data source and to indicate whether relationship details should be retrieved, on the Select a Data Source page, click Advanced. By default, the schemas are not restricted and relationship details are retrieved. To supply multiple schemas, enter a comma-separated list of schema names in the Restrict to schema(s) box.

If no foreign key relationships are included in the specified relational data source, a Name Matching page appears in the wizard. You can use this page to select a method for creating logical relationships between the tables. The section Specify Name Matching Criteria for Relationships in this topic provides more information to help you choose a matching method.

Filter Available Objects

If the Available objects list contains a very large number of objects, you can reduce the list by applying a simple filter that species a string as selection criteria. For example, if you type dbo and click the Filter button, then only those items starting with "dbo" show up in the Available objects list. The filter can be a partial string (for example, “sal” returns sales and salary) but it cannot include multiple strings or operators.

Add a Secondary Data Source

When defining a data source view that contains tables, views, or columns from multiple data sources, the first data source from which you add objects to the data source view is designated as the primary data source (you cannot change the primary data source after it is defined). After defining a data source view based on objects from a single data source, you can then add objects from other data sources.

If an OLAP processing or a data mining query requires data from multiple data sources in a single query, the primary data source must support remote queries using OpenRowset. Typically, this will be a SQL Server data source. For example, if you design an OLAP dimension that contains attributes that are bound to columns from multiple data sources, then Analysis Services will construct an OpenRowset query to populate this dimension during processing. However, if an OLAP object can be populated or a data mining query resolved from a single data source, then an OpenRowset query will not be constructed. In certain situations, you may be able to define attribute relationships between attributes to eliminate the need for an OpenRowset query. For more information about attribute relationships, see Modifying a Data Source View (Data Mining Tutorial), Adding or Removing Tables or Views in a Data Source View (Analysis Services) and Define Attribute Relationships.

To add tables and columns from a second data source, you double-click the DSV in Solution Explorer to open it in Data Source View Designer, and then use Add/Remove Tables dialog box to include objects from other data sources that are defined in your project. 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 DSV, 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.

[!NOT]

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 DSV, including logical primary keys. For more information, see Define Logical Relationships in a Data Source View (Analysis Services) and Define Logical Primary Keys in a Data Source View (Analysis Services).

The Data Source View Wizard uses your response to match column names and create relationships between different tables in the DSV. 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

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 DSV. 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 DSV. You can change the NameMatchingCriteria property setting in Data Source View Designer. For more information, see Change Properties in a Data Source View (Analysis Services).

[!NOT]

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 Define Logical Relationships in a Data Source View (Analysis Services).