Introduction to MSSQL Server Analysis Services: Introducing Data Source Views

Monday Oct 10th 2005 by William Pearson
Share:

Business Intelligence Architect Bill Pearson introduces Data Source Views, a new feature in MSSQL Server 2005 Analysis Services.

About the Series ...

This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services, with each installment progressively presenting features and techniques designed to meet specific real - world needs. For more information on the series, please see my initial article, Creating Our First Cube.

Note: This article examines exciting new features of MSSQL Server 2005. To follow along with the steps we undertake, the following components, samples and tools are recommended, and should be installed according to the respective documentation that accompanies MSSQL Server 2005:

  • Microsoft SQL Server 2005 Database Engine
  • Microsoft SQL Server 2005 Analysis Services (SSAS)
  • Business Intelligence Development Studio
  • Microsoft SQL Server 2005 sample databases
  • The Analysis Services Tutorial sample projects and other samples that are available with the installation of the above.

To successfully replicate the steps of the article, you also need to have:

  • Membership within one of the following:
    • the Administrators local group on the Analysis Services computer
    • the Server role in the instance of Analysis Services.
  • Read permissions within the SQL Server 2005 sample databases we access within our practice session.

Note: Current Service Pack updates are assumed for the operating system, MSSQL Server 2005 ("MSSQL Server"), MSSQL Server 2005 Analysis Services ("Analysis Services"), MSSQL Server 2005 Reporting Services ("Reporting Services") and the related Books Online and Samples. Images are from a Windows 2003 Server environment, within which I have also implemented MS Office 2003, but the steps performed in the articles, together with the views that result, will be quite similar within any environment that supports MSSQL Server 2005 and its component applications.

Introduction

The release of MSSQL Server 2005 ushers in a plethora of enhancements over the feature set available within MSSQL Server 2000. Proclaimed as the "BI Release" for good reason, the integrated Microsoft business intelligence solution has witnessed advances in power, scope and user friendliness that I have found impressive, as I have begun implementing it among my clients. As a Business Intelligence architect, I am discovering that, along with these enormous improvements, the further integration of MSSQL Server, Analysis Services and Reporting Services within the centralized Business Intelligence Development Studio, has proven to be a dramatic step in the "commoditization of BI" that I have predicted since I began working with MS OLAP Services in MSSQL Server 7.0, through its further evolution to MSSQL Server 2000 Analysis Services.

In this article, I will introduce a significant improvement that underlies the new Analysis Services development approach, the Data Source View. A Data Source View is a design-time object that makes its home in the workbench environment of the Business Intelligence Development Studio. Its purpose, as we shall see, is to provide an abstract layer that bridges front-end and back-end components in a single, unified interface. The Data Source View thus provides a much richer, productivity-oriented developer experience while making possible the implementation of consistent standards within an Analysis Services Project. An understanding of Data Source Views is critical to any development project in Analysis Services, and once we get a grasp of its utility, we can appreciate that it represents an advance in modeling, design and creation of sophisticated sources for multi-dimensional analysis.

In this article, we will:

  • Introduce the Analysis Services 2005 concept of Data Source Views;
  • Discuss the nature and relationship of Data Sources and Data Source Views;
  • Introduce the Business Intelligence Development Studio;
  • Create a new Analysis Service Project;
  • Define a Data Source;
  • Define a Data Source View;
  • Introduce the Data Source View Designer.

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.

Hands-On Procedure

Let's get some hands-on practice in creating a Data Source View. First, we will create a new project within the Business Intelligence Development Studio, wherein we will create a Data Source pointed to a database sample provided with MSSQL Server 2005, so that anyone with access to the installed application and its samples can complete the steps in the practice session.

Preparation

Create a New Analysis Services Project

We begin our preparation within Business Intelligence Development Studio, where we will create a new Microsoft SQL Server 2005 Analysis Services (SSAS) Project. As I have already mentioned, those of us who have worked within earlier versions of Reporting Services, or within the Visual Studio development environment under other circumstances, will recognize the general look and feel of the Studio. Under this new style of development for Analysis Services, to which much of the documentation refers as "project mode," we create an Analysis Services Project, which houses a set of Analysis Services objects. The object set can include Data Sources, Data Source Views, Dimensions, Cubes, and other components.

While we will not go into the processes "downstream" of our immediate topic, Data Source Views, in this article, let's make a mental note that everything we create and store within a given project are defined as files (using an XML representation) that eventually are deployed to an Analysis Services database.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Business Intelligence Development Studio, as depicted in Illustration 1.


Illustration 1: Opening SQL Server Business Intelligence Development Studio

The Microsoft Visual Studio 2005 development environment opens, beginning with the Start page, as shown in Illustration 2.


Illustration 2: The Start Page, Microsoft Visual Studio 2005 Development Environment (Compressed View)

4.  Close the Start Page tab.

5.  Select File --> New on the Visual Studio main menu.

6.  Select Project from the cascading menu, as depicted in Illustration 3.


Illustration 3: Beginning a New Project ...

The New Project dialog appears.

7.  Select Business Intelligence Projects in the Project types pane of the dialog.

8.  Select Analysis Services Project in the Templates pane, as shown in Illustration 4.


Illustration 4: Select Analysis Services Project

NOTE: The templates that appear in your template pane may differ, depending upon which SQL Server 2005 components are installed in your environment, as well as whether additional templates (for Business Intelligence Projects or other types of projects that can be created) have been defined in Visual Studio.

9.  Change the project Name (currently displaying a default) to the following:

ANSYS040 Data Source View

Notice that the Solution Name changes to match the project Name by default.

10.  Navigate to a convenient location to store the Project and Solution files, modifying the Location box accordingly, (Visual Studio will create a directory based upon our input here).

The New Project dialog appears similar to that depicted in Illustration 5.


Illustration 5: The New Project Dialog, with our Input

11.  Click OK to accept our input and to create the new Analysis Services Project.

The New Project dialog closes, as Visual Studio creates the project and solution files. The ANSYS040 Data Source View project appears in the Solution Explorer as shown in Illustration 6.


Illustration 6: The ANSYS040 Data Source View Project Appears

Solution Explorer presents a tree view of the objects contained in the solution, which can contain multiple projects. Individual projects, such as the one we have created, themselves contain folders for the objects that can be defined for projects of a similar type. As we can see in the present instance, the Analysis Services Project template, upon which our ANSYS040 Data Source View project was based, contains the following folders:

  • Data Sources
  • Data Source Views
  • Cubes
  • Dimensions
  • Mining Structures
  • Roles
  • Assemblies
  • Miscellaneous

Anytime we create a new Analysis Services Project, the Solution Explorer and the Properties window are visible and docked, as we see them in the present case. The following windows are hidden and docked, initially, and appear on the right, bottom, or left side of the development environment, depending upon where they are docked:

  • Server Explorer
  • Toolbox
  • Task List
  • Error List

Viewing any of the hidden windows is as simple as positioning the pointer over it – its mouseover behavior is to reappear. We can also click the Auto Hide button (depicted for the Solution Explorer in Illustration 7) to hide or unhide a window.


Illustration 7: The Auto Hide Button – Solution Explorer

Finally, we can always open a closed window by selecting it from the View menu atop the development environment, as shown in Illustration 8.


Illustration 8: Reopen Closed Windows from the View Menu ...

Having created a new Analysis Services Project, we are ready to define a Data Source and Data Source View.

Define a Data Source

Defining a Data Source is typically the first step we take with an Analysis Services Project. A project can have multiple Data Sources, within which we are essentially defining the connection string used to connect to the actual sources of our data. While we can define connections to source databases on remote computers in the business environment, we will, in this article, be working with one of the sample databases that are available to us when we install MSSQL Server 2005, the AdventureWorksDW database.

Let's create a Data Source within our new ANSYS040 Data Source View project by taking the following steps:

1.  Right-click Data Sources folder within the Solution Explorer.

2.  Select New Data Source from the context menu that appears, as depicted in Illustration 9.


Illustration 9: Initial Step in Creating a New Data Source

The Data Source Wizard appears, opening with the Welcome to the Data Source Wizard page, as shown in Illustration 10.


Illustration 10: Welcome to the Data Source Wizard ...

3.  Click Next.

The Select how to define the connection page appears. This page allows us to define a Data Source based upon a new or existing connection. It also allows us to base a Data Source upon a connection that has been previously created, as long as the existing Data Source definition exists within the same project, or within another project housed in the same solution within which we are currently working.

4.  Click New on the Select how to define the connection page.

The Connection Manager dialog appears.

5.  Ensure that Native OLE DB\SQL Native Client is selected in the Provider selection box atop the Connection Manager dialog.

6.  Type the appropriate server name / server name with instance into the Server name box.

We can substitute "localhost" or "localhost\<instance name>" here, if it is appropriate to our environment.

7.  Select / complete the appropriate authentication choice for your environment in the section labeled Log on to the server (I selected the radio button to the left of Use Windows Authentication in the lab environment in which I prepared this article).

8.  Type the appropriate server name / server name with instance name into the Server name box.

9.  Select AdventureWorksDW in the Select or enter a database name selector box, within the Connect to a database section of the dialog.

The Connection Manager dialog appears similar to that depicted in Illustration 11.


Illustration 11: Connection Manager Dialog in My Lab Environment

10.  Click the Test Connection button (in the bottom left corner of the Connection Manager dialog) to ascertain that we have a valid connection.

We receive a positive confirmation message, Test connection succeeded, assuming settings in the dialog are in accordance with the above steps, as appropriate for our own environments. The confirmation message box appears as shown in Illustration 12.


Illustration 12: Confirmation Message Indicating Successful Connection Test

11.  Click OK to dismiss the message box and return to the Connection Manager dialog.

12.  Click OK on the Connection Manager dialog to accept our settings and create the data connection.

We return to the Select how to define the connection page of the Data Source Wizard, which appears similar to that depicted in Illustration 13.


Illustration 13: Select How to Define the Connection Page with New Data Connection

13.  Click Next.

We arrive at the Impersonation Information page, where we define connection credentials for the data source.

14.  Click the radio button to the immediate left of Use the service account, as shown in Illustration 14.


Illustration 14: Select the Use the Service Account Option

15.  Click Next.

The Completing the Wizard page appears, as depicted in Illustration 15.


Illustration 15: Completing the Wizard ...

We will leave the assigned Data Source name in place for purposes of our practice session.

16.  Click Finish to create the new Data Source, and to dismiss the Data Source Wizard.

The Wizard closes, and the new Data Source appears in the Data Sources folder within the Solution Explorer as shown in Illustration 16.


Illustration 16: The New Data Source Appears in the Solution Explorer

Having established the basic prerequisites, we are ready, at this stage, to create a Data Source View.

Procedure

Define a Data Source View

Having defined the Data Source within our Analysis Services Project, our next steps surround the creation of the Data Source View, a procedure that is customary at this stage in most Analysis Services Projects. It is important to realize, as we work with a "live" data connection that we have defined, that we could certainly continue our development efforts with the metadata without an open connection. The Data Source View provides a single, unified view of the metadata from the tables and views that concern us within our project.

To define a Data Source View, we will take the following steps:

1.  Right-click Data Source Views folder within the Solution Explorer.

2.  Select New Data Source View from the context menu that appears, as depicted in Illustration 17.


Illustration 17: Initial Step in Creating a New Data Source View

The Data Source View Wizard appears, opening with the Welcome to the Data Source View Wizard page, as shown in Illustration 18.


Illustration 18: The Initial Page of the Data Source View Wizard

3.  Click Next.

We arrive at the Select a Data Source page, where we see our Adventure Works DW Data Source in the Relational data sources list box on the left side of the page, as depicted in Illustration 19.


Illustration 19: Adventure Works DW Appears in the Relational Data Sources

Our newly created Data Source is positioned as the default, and will serve us in meeting the objectives of our practice exercise. It is particularly useful to note, however, that we can define both a primary data source (a single Data Source like Adventure Works DW is an example), and then add tables and views from secondary data sources.

NOTE: Heterogeneous queries are supported as long as one Data Source is a MSSQL Server Data Source.

4.  Click Next.

We next arrive at the Select Tables and Views page, where we see the various tables of the Adventure Works DW data source appear in the Available objects list box on the left of the page.

5.  Click dbo.FactResellerSales in the Available objects list to select it.

6.  Click the button marked > to move dbo.FactResellerSales to the Included objects list on the right half of the page, as shown in Illustration 20.


Illustration 20: Selecting a Table for Inclusion in the Data Source View

7.  Perform Steps 5 and 6 for each of the following tables, to include them in the Data Source View:

  • dbo.DimEmployee
  • dbo.DimProduct
  • dbo.DimProductCategory
  • dbo.DimProductSubcategory
  • dbo.DimReseller
  • dbo.DimTime

NOTE: We can also select the group listed above at once by holding down the [CTRL] key and clicking each sequentially.

The Select Tables and Views page appears, with our selections, as depicted in Illustration 21.


Illustration 21: Our Tables Selection for the Data Source View

8.  Click Next.

The Completing the Wizard page appears, as shown in Illustration 22.


Illustration 22: The Final Page of the Data Source View Wizard

9.  Click Finish to create the new Data Source View, and to dismiss the Data Source View Wizard.

Our new Data Source View, Adventure Works DW, appears in the Data Source Views folder within the Solution Explorer window, as depicted in Illustration 23.


Illustration 23: The New Data Source View in the Solution Explorer

The Data Source View is also presented within the Data Source View Designer in Business Intelligence Development Studio.

10.  Select View --> Zoom from the main menu in the development environment.

11.  Select 50% from the cascading menu that appears, as shown in Illustration 24.


Illustration 24: Select View --> Zoom --> 50% ...

Our Data Source View appears, in somewhat compacted fashion, in the Data Source View Designer as depicted in Illustration 25.

Click for larger image

Illustration 25: Data Source View Designer Display: Adventure Works DW.dsv

One of several designers within the Studio, the Data Source View Designer for our new Data Source View contains the elements detailed in Table 1 (keyed to Illustration 25 above).

Legend Key

Element

Purpose

1

Diagram Organizer

Allows creation of "subdiagrams" to allow us to view subsets of the Data Source View

2

Tables Pane

Tree view display of the tables and their schema elements are displayed

3

Diagram Pane

Graphic representation of the tables and their relationships

4

Data Source View Toolbar

A toolbar that is specific to Data Source View Designer

5

Data Source View Menu

A menu that is specific to Data Source View Designer

Table 1: Data Source View Member Elements

We can add tables to our Data Source View (and perform numerous other activities) at any time by right-clicking the Diagram pane, and then clicking Add/Remove Tables, as shown in Illustration 26.


Illustration 26: Adding Tables to the Data Source View ... and More ...

We can perform many further operations within the Data Source View, the powerful advantages of which should be apparent to those of us who have implemented MSSQL Server Analysis Services 2000 and other OLAP solutions. As our series progresses we will return to the Data Source View to demonstrate many tips and techniques with regard to leveraging this new design paradigm.

1.  Select File --> Save All to save our work to this point.

2.  Select File --> Exit, to close the Business Intelligence Development Studio and Visual Studio, when ready.

Conclusion

In this article, we examined the Data Source View, a significant enhancement underlying the new Analysis Services development paradigm. We introduced the Analysis Services 2005 concept of Data Source Views, discussing the nature and purpose of Data Source Views, and the Data Sources that support them, within the context of the Business Intelligence Development Studio. We emphasized that an understanding of Data Source Views is critical to any development project in Analysis Services, and discussed how it represents an advance in the modeling, design and creation of sophisticated sources for multi-dimensional analysis.

We undertook a practice exercise whereby we created a Data Source View, for which we initially prepared by creating a new Analysis Service Project; we defined a supporting Data Source, and then created our Data Source View. We then introduced the Data Source View Designer, describing its elements and looking forward to articles where we expand upon its many uses.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved