Introduction to MSSQL Server Analysis Services: Introducing Data Source Views - Page 2

Data Source Views in Analysis Services

Overview and Discussion

The Data Source View represents a significant design and development enhancement within Analysis Services. The Data Source View forms a central, unified view of the metadata within our Analysis Services Project, and is based upon the Data Source we have defined therein. Both the Data Source View and the Data Source are objects that we save within our Analysis Services Projects. Because Data Sources and Data Source Views belong to the project, they can be shared between cubes.

The Data Source represents a connection to a physical data source. The Data Source contains the connection string, with which most of us are familiar from Analysis Services 2000 and earlier, and which defines how Analysis Services connects to the data store via a Microsoft .NET or native OLE DB provider. The connection string contains connection-related information, including server and database identification, security and the like. Once we have access to defined Data Sources, we can define additional Data Sources based upon them, saving time and ensuring accurate and consistent setup.

The Data Source View contains the logical model of the schema used by database objects, including cubes, dimensions, and so forth. A Data Source View can be built to represent one or more Data Sources, allowing us to integrate data from multiple data stores within a single cube, or even dimension. The Data Source View serves as an abstract layer: the Analysis Services database objects are not bound directly to the underlying physical objects within the supporting data stores, but are bound, instead, to the logical objects within the Data Source View. One of many advantages that the Data Source View layer offers, then, is that it can contain logical objects, such as queries, relationships, and calculated columns, that do not exist within (and, indeed, are entirely separate from) the underlying data sources. This factor alone offers a great deal of power in scenarios where, for whatever reason, we cannot create these objects within the data sources upon which we are constructing our Analysis Services Projects.

In a manner that reminds me of database diagramming in Visio and similar applications, Data Source View metadata can also be used to actually create the underlying relational schema required to support the Data Source View. While we perform a detailed examination of the Schema Generation Wizard in an upcoming article "Introducing the Schema Generation Wizard," this tightly integrated utility can assist us in generating such a schema, among other useful activities. We will revisit the Schema Generation Wizard multiple times within this and other of my series.

The Data Source View represents a dramatic step in the evolution of business intelligence systems design. The power of this abstract layer extends beyond Analysis Services to its bedfellows within the Business Intelligence Development Studio, SQL Server 2005 Integration Services (SSIS) and SQL Server 2005 Reporting Services (SSRS). It is important to remember, as we stated earlier, the Data Source View lives in the Analysis Services Project, and is not visible to client applications. Another general consideration surrounds security: Because it is a logical representation of underlying data sources, the security defined within the connection string to those sources serves as the basis for access rights through the Data Source View.

Considerations and Comments

For purposes of the practice exercises within this series, we will be working with samples that are provided with MSSQL Server 2005. These samples include Online Transaction Processing (OLTP), Online Analytical Processing (OLAP) and Data Warehouse sample databases. The AdventureWorks (OLTP), Adventure Works DW (OLAP), and AdventureWorksDW (Data Warehouse) sample databases and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed. The topics "Running Setup to Install AdventureWorks Sample Databases and Samples" in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources, as well), provide guidance on samples installation.

Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in the references I have noted.