Data Source View ( DSV ) in SSIS and SSAS - Metadata management and Advantages

Definition of DSV as per MSDN goes this way: "A DSV is a metadata layer between the object that consumes / processes data and the underlying data source that allows us to build our objects without being connected to the underlying source". For reference, a DSV is similar to Business Objects (BO) universe. A data source view is the metadata definition, stored in an XML format.

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.
• Can be built over one or more data sources, letting you define 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. Data sources views are not used when you build objects programmatically.

Analysis Services Perspective for DSV: A DSV is the metadata definition of these schema elements used by the Unified Dimensional Model (UDM) and by the mining structures. A data source view contains the logical model of the schema used by Analysis Services database objects—namely cubes, dimensions, and mining structures. In Analysis Services, a developer is embedding business metadata throughout the application when they create Data Source Views (DSVs).

Analysis Services database objects are bound to the logical objects contained in the data source view, instead of being directly bound to the underlying physical objects in the underlying data sources. Therefore, you can define logical objects, such as calculated columns or named queries, that do not exist in an underlying data source and which you might not have permissions to define in an underlying data source.

Integration Services Perspective for DSV: Advantages of using data source views include the following:

• A DSV can be defined once and then used by multiple data flow components. Because a data source view is not created within the context of a package, it can be used by tasks and data flow components in multiple packages.
• A DSV can be refreshed to reflect changes in its underlying data sources.
• A DSV also allows developers to develop packages without having live connections to the source data systems by caching the metadata from the data source from which it’s built.
• A DSV can focus the lists of objects that appear in the user interface of data flow components, speeding up the design interface.

Reference: MSDN BOL