Working with Measures and Measure Groups
Overview and Discussion
We
will create an Analysis Services Project within the Business Intelligence Development
Studio, to provide the environment and the
tools that we need to design and develop business intelligence solutions based
upon Analysis Services 2005. As we have noted in the past, the
pre-assembled Analysis Services Project that makes its home within the Studio
assists us in organizing and managing the numerous objects that we will
need to support our efforts to create and deploy our Analysis Services
database.
We
will leverage the Cube Wizard in this article to quickly design and
create a cube, allowing us to focus on the subject matter of the article with
minimal peripheral distraction. The Cube Wizard not only helps us simplify
the design and creation of our cubes, as it did within Analysis Services
2000: the Analysis Services 2005 Cube Wizard is more powerful,
leveraging IntelliCube technology to examine and classify many of the
attributes of our data. Analysis Services can determine, for example,
prospective fact tables, dimensions, hierarchies, levels and other structural
members of our cubes from a given database schema at which it is pointed. Regardless
of whether we make a habit of using the wizard in our cube development efforts,
it certainly provides a way to rapidly generate a cube, if only to eliminate
part of the repetitive work involved to create a "starting point"
model, which we can then "prune and groom" to more precisely meet the
business requirements of our employers and customers.
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 Analysis Services. The samples with which we are
concerned include, predominantly, the Adventure Works DW Analysis Services
database (with member objects). The Adventure Works DW database 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), 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 these references.
Hands-On Procedure
We will begin by
creating 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. This way, anyone with access to the
installed application set 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 we have noted in other articles, 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 typically includes Data
Sources, Data Source Views, Dimensions, Cubes, and
other components.
Using an Analysis Services Project template affords us many benefits, such as
helping us to organize and
manage the items that are required to build, debug, and deploy an Analysis Services database. The
development of an Analysis
Services
cube, which is our objective within this session, as well as one of the most
common tasks in Analysis
Services,
can be accelerated though the use of the Analysis Services 2005 Cube Wizard, which both
guides and simplifies the process, as is the way of wizards. IntelliCube technology, which
can detect the relationships between attributes, dimensions, and facts (as we
have already noted), makes the wizard more effective than its counterpart in Analysis Services 2000.
While we
will not go into the processes and structures that underlie our immediate
topic, working with Measure Groups, let's make a mental note that all
objects that 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 upon 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:
ANSYS048 Measure Groups
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 relevant section of
the New Project dialog appears similar to that depicted in Illustration
5.
Illustration 5: The New
Project Dialog (Relevant Section), with our Input
11. Click OK to accept our input and to create
the new Analysis Services Project.
The New Project dialog
closes. Visual Studio creates the project, which appears within solution
ANSYS048 Measure Groups, in the Solution Explorer as shown in Illustration
6.
Illustration 6: The New
Analysis Services Project Appears
Solution Explorer presents a tree view of the
objects contained in the solution, the primary management unit within
the Business Intelligence Development Studio, 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. It is, after all, the purpose of an Analysis Services
Project to group related files containing the XML code behind the objects
in a given Analysis Services database.
As we can see in the
present instance, the Analysis Services Project template, upon which our
ANSYS048 Measure Groups 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.
» 