Share via

  • Article

Designing Data Source Views (Analysis Services)

Online analytical processing (OLAP) and data mining projects in Microsoft SQL Server are designed based on a logical data model of related tables, views, and queries from one or more data sources. This logical data model is called a data source view. A data source view is an object that contains the metadata from selected data source objects, including any relationships between these objects defined in the underlying data source or defined in the data source view. A data source view caches the metadata from the data sources that the view is built upon. The cached metadata lets you develop an Analysis Services project without a continuous active connection to the data source.

Data source views let you define a subset of the data that populates a large data warehouse. Additionally, they let you define a homogenous schema based on heterogeneous data sources or subsets of data sources. Because data source views represent an isolated schema, you can add any required annotations without affecting the schemas of the underlying data sources

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.

Using Data Source Views for Different Objects

Analysis Services design tools use data source views to maintain a cache of relational metadata and to take advantage of some of the annotations within a data source view. By describing a subset of tables and views in a data source, a data source view makes available only the tables required by OLAP and data mining objects. A data source view handles the layout of tables, filters, SQL expressions, relationships, and other complexities of the schema. Therefore, a data source view enables simple bindings by Analysis Services cubes, dimensions, and mining models to the tables and columns in the data source view.

You can build multiple data source views in an Analysis Services project or database on one or more data sources and construct each one to satisfy the requirements for a different solution.

A single data source view supports multiple diagrams that show different subsets of the data source view. Sometimes, you may use separate diagrams to work with sections of a data source view that pertain to a particular object. Unlike different data source views, different diagrams reference the same schema. Therefore, any changes made in one diagram apply to all other diagrams in the data source view.

If a data source contains fields that are of the tinyint datatype and the AutoIncrement property is set to True, then they will be converted to integers in the data source view.

Working with Multiple Data Sources

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 Microsoft 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 and Defining Attribute Relationships.

Topics in this Section

The following are the topics in this section.