Defining a Data Source View (Analysis Services)
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 if you are following the top-down approach to schema generation.
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.
A DSV is a required component of a multidimensional model. Most Analysis Services developers create a DSV during the early phases of model design, generating at least one DSV based on an external relational database that provides underlying data. However, you can also create the DSV at a later phase, generating the schema and underlying database structures after the dimensions and cubes are created. This second approach is sometimes called top-down design and is frequently used for prototyping and analysis modeling. When you use this approach, you use the Schema Generation Wizard to create the underlying data source view and data source objects based on the OLAP objects defined in an Analysis Services project or database. Regardless of how and when you create a DSV, every model must have one before you can process it.
This topic includes the following sections:
Create a DSV Using the Data Source View Wizard
Specify Name Matching Criteria for Relationships
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 Data Tools.
Note
Alternatively, you can construct dimensions and cubes first, and then generate a DSV for the model using the Schema Generation wizard. For more information, see Schema Generation Wizard (Analysis Services).
In Solution Explorer, right-click the Data Source Views folder and then click New Data Source View.
Specify a new or existing data source object that provides connection information to an external relational database (you can only select one data source in the wizard).
On the same page, click Advanced to choose specific schemas, apply a filter, or exclude table relationship information.
Choose Schemas
For very large data sources containing multiple schemas, you can select which schemas to use in a comma delimited list, with no spaces.
Retrieve Relationships
You can purposely omit table relationship information by clearing the Retrieve relationships checkbox in the Advanced Data Source View Options dialog box, allowing you to manually create relationships between tables in the Data Source View Designer.
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.
For relational data sources that do not have table relationships defined, a Name Matching page appears so that you can select the appropriate name matching method. For more information, see the Specify Name Matching Criteria for Relationships section in this topic.
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 Attribute Relationships, 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.
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 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.IDOrder.Customer ID and Customer.IDOrder.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).
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 Define Logical Relationships in a Data Source View (Analysis Services).
See Also
Adding or Removing Tables or Views in a Data Source View (Analysis Services)
Define Logical Primary Keys in a Data Source View (Analysis Services)
Define Named Calculations in a Data Source View (Analysis Services)
Define Named Queries in a Data Source View (Analysis Services)
Replace a Table or a Named Query in a Data Source View (Analysis Services)
Work with Diagrams in Data Source View Designer (Analysis Services)
Explore Data in a Data Source View (Analysis Services)
Delete a Data Source View (Analysis Services)
Refresh the Schema in a Data Source View (Analysis Services)