SSAS - Data Source View (DSV)

> Database > SQL Server - SQL Server Analysis Services (OLAP)

1 - About

A data source view is used to define the specific tables that are retrieved from the data source.

The presentation of those tables and their columns can be changed.

You can define a data source view by using the Data Source View Wizard in SQL Server Data Tools.

Data source view can bring together data from multiple data sources. (Multiple data sources in the Data Source View Designer).

2 - Components

A data source view defines the following:

  • A name and a description.
Advertising

2.1 - Data model

Data model definition (retrieved from one or more data sources) including the following:

  • Table names
  • Column names
  • Data types
  • Nullability
  • Column lengths
  • Primary keys
  • Primary key–foreign key relationships

2.2 - Data Model Enhancements

  • Filters,
  • Structured Query Language (SQL) expressions.
  • Friendly names for tables, views, and columns (FriendlyName property)
  • Logical primary keys (needed if a primary key is not defined 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.
Advertising

2.2.1 - Named queries

Named queries create a data table with a SQL statement definition (from one or more data sources).

2.2.2 - Named calculations

Named calculations are extra table columns made from an expressions.

Expression Example:

FirstName + ' ' + LastName

2.3 - Diagram

Create diagrams to reduce the visual clutter when you only want to view a subset of the tables in the data source view.

You can create multiple diagrams that show different subsets of the data source view. Unlike different data source views, different diagrams reference the same schema. Therefore, any changes that you make in one diagram apply to all other diagrams in the data source view. Diagrams are useful when you create multiple cubes within the same solution.

3 - Management

3.1 - Create

Advertising