SQL Server > Analysis Services > Multidimensional Modeling > Data Source Views

Data Source Views (Multidimensional Modeling)

A Data Source View (DSV) acts as an abstraction layer between your Analysis Services multidimensional model and the underlying relational data sources. It allows you to define a unified, logical view of data from one or more relational databases, simplifying the modeling process and enabling powerful data manipulation capabilities.

What is a Data Source View?

In SQL Server Analysis Services (SSAS) multidimensional projects, a DSV is a metadata object that represents the tables and views from your data sources. You can perform various operations within the DSV designer:

Select and Alias Tables: Choose which tables and views from your connected data sources will be included in the model and rename them for clarity.
Define Relationships: Establish explicit relationships between tables, mirroring the foreign key relationships in your relational database or defining new ones.
Create Calculated Columns and Measures: Define new columns based on expressions involving existing columns, or create aggregate measures that can be used in your cubes.
Tune Performance: Designate primary and foreign keys for performance optimization.
Hide Columns: Exclude unnecessary columns from the model to simplify the user experience.
Name Columns and Tables: Provide user-friendly names that differ from the physical names in the source database.

Benefits of Using Data Source Views

Data Abstraction: Decouples the multidimensional model from the physical schema of the data source. Changes to the source database schema can often be managed by updating the DSV without impacting the cube structure.
Data Integration: Enables you to combine data from multiple disparate relational databases into a single, coherent view for analysis.
Simplification: Allows you to present a simplified and business-friendly view of complex relational schemas by renaming tables and columns, removing unneeded objects, and creating calculated members.
Data Transformation: Provides capabilities to transform data, such as creating calculated columns and measures, splitting columns, or joining tables.

Key Components of a Data Source View

Tables and Views: Represent the relational objects from your connected data sources.
Relationships: Define how tables are linked, enabling the traversal of data for queries.
Primary Keys: Indicate the unique identifier for rows in a table.
Foreign Keys: Link rows in one table to rows in another table.
Named Queries: Allow you to define custom SQL queries that can be treated as tables within the DSV.
Calculated Columns: Define new columns based on expressions of existing columns.
Calculated Measures: Define new measures using MDX or DAX expressions.

Creating and Managing Data Source Views

You can create and manage Data Source Views using SQL Server Data Tools (SSDT) for Visual Studio.

Steps to Create a DSV:

In your Analysis Services multidimensional project, right-click the Data Source Views folder and select New View.
The Data Source View Designer will open, showing available data sources.
Select the tables and views you want to include in your DSV and click Add.
Define relationships between tables by dragging a column from one table to the corresponding column in another.
Right-click on tables or columns to rename them, define keys, or create calculated members.
Save your Data Source View.

Note: Ensure that the user account running the Analysis Services service has appropriate permissions to access the underlying relational data sources.

Example: Defining a Calculated Column

Suppose you have a Sales table with columns Quantity and UnitPrice, and you want to create a calculated column named ExtendedPrice.

In the DSV Designer:

Right-click the Sales table and select New Calculated Column.
In the Properties window, set the Name to ExtendedPrice.
In the expression editor, enter the following formula:
```
[Quantity] * [UnitPrice]
```

This calculated column will be available in your cube for analysis.

Advanced Scenarios

Partitioning: Data source views can be instrumental in defining partitions for large fact tables, allowing for better performance and manageability.
Security: While cube-level security is managed separately, the DSV can influence which data is exposed to the model, indirectly affecting security.
Hierarchies: Although hierarchies are primarily defined on dimensions, the structure defined in the DSV is foundational.

Tip: Regularly review your Data Source Views to ensure they accurately reflect your business requirements and are optimized for performance.

By effectively utilizing Data Source Views, you can build robust, scalable, and maintainable multidimensional models in SQL Server Analysis Services.

Last Updated: October 26, 2023