Business Intelligence Architect Bill Pearson introduces
Data Source Views, a new feature in MSSQL Server 2005 Analysis Services.
About the Series ...
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:
Server 2005 Database Engine
Server 2005 Analysis Services (SSAS)
Intelligence Development Studio
Server 2005 sample databases
Services Tutorial sample projects and other samples that are available with the
installation of the above.
successfully replicate the steps of the article, you also need to have:
within one of the following:
local group on the Analysis Services computer
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.
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
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
In this article, we will:
Introduce the Analysis
Services 2005 concept of Data Source Views;
nature and relationship of Data Sources and Data Source Views;
Introduce the Business
Intelligence Development Studio;
Create a new Analysis
Define a Data
Define a Data
Introduce the Data
Source View Designer.
Data Source Views in Analysis Services
Overview and Discussion
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.
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.
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.
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
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
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
Click the Start
SQL Server 2005 within the Program group of the menu.
Server Business Intelligence Development Studio, as depicted in Illustration
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
Close the Start
--> New on the Visual Studio main
from the cascading menu, as depicted in Illustration 3.
Illustration 3: Beginning
a New Project ...
Project dialog appears.
Intelligence Projects in the Project types pane of the dialog.
Services Project in the Templates pane, as shown in Illustration
Illustration 4: Select
Analysis Services Project
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
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
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: 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:
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:
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
Define a Data Source
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.
a Data Source within our new ANSYS040 Data Source View project by taking the following steps:
Sources folder within the Solution Explorer.
Data Source from the context menu that appears, as depicted in Illustration
Illustration 9: Initial
Step in Creating a New Data Source
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 ...
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.
Click New on
the Select how to define the connection page.
The Connection Manager
Ensure that Native
OLE DB\SQL Native Client is selected in the Provider selection box
atop the Connection Manager dialog.
appropriate server name / server name with instance into the Server name
We can substitute "localhost"
or "localhost\<instance name>" here, if it is
appropriate to our environment.
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
appropriate server name / server name with instance name into the Server
AdventureWorksDW in the Select or enter a database name selector
box, within the Connect to a database section of the dialog.
Connection Manager dialog appears similar to that depicted in
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
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
We arrive at the Impersonation
Information page, where we define connection credentials for the data
14. Click the radio button to the
immediate left of Use the service account, as shown in Illustration
Illustration 14: Select
the Use the Service Account Option
The Completing the
Wizard page appears, as depicted in Illustration 15.
Illustration 15: Completing
the Wizard ...
leave the assigned Data Source name in place for purposes of our
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
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:
Source Views folder within the Solution Explorer.
Data Source View from the context menu that appears, as depicted in Illustration
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
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
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
Heterogeneous queries are supported as long as one Data Source is a MSSQL
Server Data Source.
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.
in the Available objects list to select it.
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
5 and 6 for each of the following tables, to include them in the Data
We can also
select the group listed above at once by holding down the [CTRL] key and
clicking each sequentially.
The Select Tables and
appears, with our selections, as depicted in Illustration 21.
Illustration 21: Our
Tables Selection for the Data Source View
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.
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).
Table 1: Data Source View Member Elements
Allows creation of "subdiagrams" to allow us to view subsets of the Data Source View
Tree view display of the tables and their schema elements are displayed
Graphic representation of the tables and their relationships
Data Source View Toolbar
A toolbar that is specific to Data Source View Designer
Data Source View Menu
A menu that is specific to Data Source View Designer
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.
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.