Leverage multiple fact tables within a cube with Measure
Groups. Join Architect Bill Pearson in a hands-on exploration of Measure
Groups, another exciting feature that debuts with Analysis Services 2005.
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
Note: 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
Server 2005 Integration Services
Intelligence Development Studio
Server 2005 sample databases
The Analysis Services
Tutorial sample project 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:
Read permissions within any SQL
Server 2005 sample databases we access within our practice session, as
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, 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.
About the Mastering Enterprise
BI Articles ...
Having implemented, and developed within, most of the major
enterprise BI applications for over fourteen years, and having developed an
appreciation for the marriage of ease of use and analytical power
through my background in Accounting and Finance, I have come to appreciate the
leadership roles Cognos and other vendors have played in the evolution of OLAP
and enterprise reporting. As I have stated repeatedly, however, I have become
convinced that the components of the Microsoft integrated business intelligence
solution (including MSSQL Server, Analysis Services, and Reporting
Services) will commoditize business intelligence. It is therefore easy to
see why a natural area of specialization for me has become the conversion of Cognos
(and other) enterprise business intelligence to the Microsoft solution. In addition to converting formerly
dominant business intelligence systems, such as Cognos, Business Objects / Crystal,
MicroStrategy and others, to the Reporting Services architecture, I
regularly conduct strategy sessions about these conversions with large
organizations in a diverse range of industries the interest grows daily as
awareness of the solution becomes pervasive. Indeed, the five-to-six-plus
figures that many can shave from their annual IT budgets represent a compelling
sweetener to examining this incredible toolset.
The purpose of the Mastering
Enterprise BI subset of my Introduction to MSSQL Server Analysis
Services series is to focus on techniques for
implementing features in Analysis Services that parallel or outstrip - those
found in the more "mature" enterprise OLAP packages. In many cases, which I try to
outline in my articles at appropriate junctures, the functionality of the OLAP
solutions within well-established, but expensive, packages, such as Cognos
PowerPlay Transformer and Cognos PowerPlay, can be met often
exceeded in most respects by the Analysis Services / Reporting Services
combination at a tiny fraction of the cost. The vacuum of documentation
comparing components of the Microsoft BI solution to their counterparts among
the dominant enterprise BI vendors, to date, represents a serious "undersell"
of both Analysis Services and Reporting Services, particularly
from an OLAP reporting perspective. I hope to contribute to making this arena
more accessible to everyone, and to share my implementation and conversion
experiences as the series evolves and, within the context of the Mastering
Enterprise BI articles, to demonstrate that the ease of replicating popular
enterprise BI features in Analysis Services will be yet another reason
that the Microsoft solution will commoditize business intelligence.
more information about the Mastering Enterprise BI articles, see
the section entitled "About the
Enterprise BI Articles" in my article Relative
Time Periods in an Analysis Services Cube, Part I.
The advent of MSSQL
Server Analysis Services 2005 witnessed the introduction of many new
concepts within a dramatically more sophisticated design environment. Measure
Groups represent one of myriad enhancements that we encounter early in
exploring and implementing Analysis Services 2005 for use within
enterprise Business Intelligence environments. A Measure Group not only
holds the measures from a given fact table, but it also houses the aggregations
of those measures for various dimensional hierarchies that we designate.
When we couple a
dimension with a Measure Group, we associate the measures within the
group with the appropriate levels of the hierarchy within that dimension. This
allows us the flexibility of using the same "grain mapping" between
the level and other measures we might wish to add to the same group. The most
obvious advantage that accrues is the capability to maintain different Measure
Groups with different meaningful levels, eliminating confusion and delivering
new levels of design friendliness.
Measure Groups are, therefore, logical
collections of related measures, whose purpose is to make life easier for
solution and application designers. In this article, we will examine Measure
Groups, and get hands-on exposure to the process of adding them to a basic cube
we construct within the new Business Intelligence Development Studio. We
will overview the creation of Measure Groups, and discuss ways in which
they can offer flexibility in cube and solution / application design and
development. As a part of our examination of the steps, we will:
Services, and our environment, by creating an Analysis Services Project
to house our development steps, and to serve as a platform for the design of a
quick cube model, within which to perform subsequent procedures in our session;
Create a Data
Source containing the information Analysis Services needs to connect
to a database;
Create a Data
Source View containing schema information;
Build a cube
based upon our Data Source and Data Source View, containing data
from our sample relational tables;
of Measure Groups as part of cube design;
the Dimensional Usage tab of the Designer, granularity at measure
/ dimension intersects for representative members of the new Measure Groups;
Deploy our Analysis
Browse the Cube,
focusing on the new Measure Groups and associated details.
Working with Measures and Measure Groups
Overview and Discussion
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
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
The topics "Running
Setup to Install AdventureWorks Sample Databases and Samples" in SQL
Server Setup Help or "Installing AdventureWorks Sample Databases and
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.
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
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
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
cube, which is our objective within this session, as well as one of the most
common tasks in Analysis
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.
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
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.
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 upon whether
additional templates (for Business Intelligence Projects or other types
of projects that can be created) have been defined in Visual Studio.
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
The relevant section of
the New Project dialog appears similar to that depicted in Illustration
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
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
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, as we have discovered in past articles, is typically the
first step we take with a new 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.
create a Data Source within our new ANSYS048 Measure Groups project by taking the following
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
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 ...
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.)
Click New on
the Select how to define the connection page.
The Connection Manager
Ensure that .Net
Providers\SqlClient Data Provider is selected in the Provider
selection box atop the Connection Manager dialog.
Type / select
the 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. (My server name with instance is MOTHER1\MSSQL2K5,
as may be seen in several of the illustrations in this and other articles.)
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.)
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
Connection Manager Dialog in My Lab Environment
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
Confirmation Message Indicating Successful Connection Test
10. Click OK to dismiss the
message box and return to the Connection Manager dialog.
11. 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
13. Click the radio button to the
immediate left of Use the service account (or supply other appropriate
local credentials), as shown in Illustration 14.
Illustration 14: Select
the Use the Service Account Option
The Completing the
Wizard page appears, as depicted in Illustration 15.
Completing the Wizard ...
leave the assigned Data Source name in place for purposes of our
15. 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.
NOTE: For more information on Data
Source Views, see my article Introduction
to MSSQL Server Analysis Services: Introducing Data Source Views at
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 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
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, as we
have mentioned in Introducing Data Source Views, as
well as other articles, that we can define a primary data source (a
single Data Source like Adventure Works DW is an example), and
then add tables and views from additional data sources. As we have
noted in other articles of the Introduction to
MSSQL Server Analysis Services series, heterogeneous queries are supported as long as one
Data Source is a MSSQL Server Data Source.
Analysis Services scans Adventure Works DW schema, and then we arrive at the
Select Tables and Views page. Here 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. We will be selecting two fact tables, to allow us to house
both sales and "budget" data within our cube.
in the Available objects list to select it.
button marked ">" to move FactInternetSales to the Included objects
list on the right half of the page.
in the Available objects list to select it.
button marked ">" to move FactSalesQuota to the Included objects list on the right
half of the page.
The added tables appear
within the Included objects list of Select Tables and Views page as
shown in Illustration 20.
Illustration 20: Table
Selections for Inclusion in the Data Source View
newly added FactInternetSales table in the Included objects list
to select it.
10. Click the Add Related Tables button,
located underneath the Included objects list.
11. Click the newly added FactSalesQuota
table in the Included objects list to select it.
12. Click the Add Related Tables button,
located underneath the Included objects list.
Several related tables
appear within the Included objects list, as depicted in Illustration
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
indicated in the Completing the Wizard page, our selection includes the
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, which opens simultaneously
with the Data Source View. The contents of the Designer appear within
Business Intelligence Development Studio, as shown in Illustration 24.
Illustration 24: The New
Data Source View in the Data Source View Designer
One of several designers
within the Studio, the Data Source View Designer for our new Data
Source View contains numerous elements that aid us in our organization and
design efforts. We can modify the composition of the tables that comprise our
view, as well as performing other activities, here.
We will next create a
cube based upon the Data Source and Data Source View we have
assembled. As a part of this process, we will get some hands-on exposure to working
with Measure Groups.
Create a Cube and
Work with Measure Groups
Create a Basic Cube
We will create a basic
cube, to house our Measures, and the Measure Groups to which they
will belong. We will focus upon other areas within the overall design and
creation process in other articles of the series our primary objective here
is to gain some familiarity with Measure Groups not to concern
ourselves with the myriad other options and topics contained within general
the Cubes folder within the Solution Explorer.
Cube ... from the context menu that appears, as depicted in Illustration 25.
Illustration 25: Select
New Cube to Begin Cube Creation
The Welcome to the
Cube Wizard dialog appears, as shown in Illustration 26.
Illustration 26: The
Welcome to the Cube Wizard Dialog Appears ...
Ensure that Build
the cube using a data source checkbox is selected on the Select Build
Method dialog that appears next, along with the Auto build checkbox,
and with Create attributes and hierarchies selected in the drop-down
selector underneath the Auto build setting.
default settings are depicted in Illustration 27.
Ensuring the Default Settings on the Select Build Method Dialog
These settings will allow the Wizard to leverage the technology built into Analysis Services, and to detect
the relationships between attributes, dimensions, and facts within the data
source we have defined. While we will not accept all the judgments that the
wizard makes, we can still take advantage of the actions it performs, and
modify or eliminate those that are inappropriate or of no use to us.
the Data Source View we have created, Adventure Works DW, is
selected within the Available data source views list on the left side of
the Select Data Source View dialog, which appears next. (The tables
contained within the Data Source View are displayed on the right side of
The Select Data Source
View dialog appears, as shown in Illustration 28.
Illustration 28: Select
AdventureWorks DW as the Data Source View
Wizard begins scanning the relational schema, identifying fact and
dimensional tables, as indicated on the Detecting Fact and Dimension Tables
page that appears next. We quickly receive a message indicating that the Wizard
has completed its analysis of the relationships between tables in the Data
Source View, and that it proposes its suggestions on the following page, as
depicted in Illustration 29.
Illustration 29: The
Detecting Fact and Dimension Tables Page Indicates Completion ...
Fact and Dimension Tables dialog next appears. Let's select a Time
dimension table, and narrow the table selection somewhat, for the purposes
of our rudimentary cube.
Select the DimTime
table in the drop-down Time dimension table selector.
Uncheck the following checkboxes:
Dimension Column: DimCurrency
NOTE: We can also make table selections graphically via the Diagram
tab of the Identify Fact and Dimension Tables dialog.
The Identify Fact and Dimension Tables dialog appears, as shown in Illustration 30.
Illustration 30: The
Identify Fact and Dimension Tables Dialog, with Our Modifications
Time Periods dialog appears.
unspecified columns blank, select the Time Table Columns values shown in
Table 1 for each of the listed Time Property Names.
Table 1: Selections within Respective Time Table Columns in
Select Time Periods Dialog
The Select Time Periods dialog appears, with our selections, as depicted in Illustration 31.
Illustration 31: The
Select Time Periods Dialog, with Our Selections
Measures dialog appears next.
Uncheck the following checkboxes:
The Select Measures dialog appears, as partially shown in Illustration 32.
Illustration 32: The
Select Measures Dialog (Partial View), with Adjusted Selections
Hierarchies page appears next, as hierarchy detection begins. We receive
rapid indication that dimension tables have been scanned and hierarchies
detected, as depicted in Illustration 33.
Illustration 33: The
Detecting Hierarchies Page, with Indication of Relationships Detected
move to the Review New Dimensions page, which appears as shown in Illustration
Illustration 34: The
Review New Dimensions Page
the Wizard page appears next.
default Cube name to the following:
ANSYS048 Measure Groups
the Wizard page appears as depicted
in Illustration 35.
Illustration 35: The
Completing the Wizard Page
Wizard concludes, and the Cube Designer opens, as shown in Illustration
Illustration 36: The
Cube Designer Opens ...
the multi-tabbed Cube Designer component of the Business Intelligence
Development Studio, we can perform myriad enhancements and extensions to,
and operations surrounding, the selected cube. We examine these new features
in other articles of my Introduction to MSSQL
Server Analysis Services series, where we define business needs
and then meet them with the appropriate functionality(ies). For the purposes of
this article, let's get some exposure to Measure Groups, and verify
their effectiveness in our basic cube.
Work with Measure
Because our cube
incorporates three fact tables, FactInternetSales, FactSalesQuota, and FactInternetSalesReason, which we included within the underlying Data
Source View, the Cube Wizard has generated a default Measure
Group for each originating fact table. (The Measure Groups are
named after the tables they represent, by default.) We can verify this fact by
examining the tree within the Measures pane that appears in the upper
left corner of the Cube Structure tab, which itself appeared by default
within the Designer when the Cube Wizard concluded its work.
In the Measures
pane, expand the three Measure Groups, Fact Internet Sales,
Fact Sales Quota and Fact
Internet Sales Reason.
expanded Measure Groups, with member measures exposed, appear as depicted
in Illustration 37.
Illustration 37: The Measures
within their Respective Groups ...
We will next examine
these groups within the Cube Designer, before making alterations to suit
the nature of the measures. The reason we need modifications is that the granularities
of the Measure Groups are different. As an illustration, Sales Amount,
a measure from the Fact Internet Sales table, is related to the actual
date (the "TimeKey" field in the Time dimension) of the
sale, while the Sales Amount Quota measure, from the Fact Sales Quota
table, is a "budget" value, relating to the year level of the Time
dimension. To separate conceptual "apples from oranges" (or, to use
a more apt metaphor, "single apples from baskets of apples"), we need
to ensure that our Measure Groups are dimensionally aligned to reflect
their true nature.
Click the Dimension
Usage tab atop the Cube Designer, as shown in Illustration 38.
Illustration 38: Click
the Dimension Usage Tab ...
The Dimension Usage page appears. It
is here that, using the Dimension Usage settings, we can assign the
relationships between the dimensions and the Measure Groups that exist
within our cube. When we combined our selection of database dimensions and
measures within the cube, a couple of things took place. Because we selected
measures, via the Cube Wizard, from three independent fact tables, the
three Measure Groups we noted earlier were created. Analysis
Services attempted to determine the appropriate alignment between the Measure
Groups and the dimensions by examining the relationships in the Data
Source View, as well as elsewhere. It then made several Dimension Usage
settings, based upon the relationships it detected. These settings appear as
depicted in Illustration 39.
Illustration 39: Initial
Dimension Usage Settings Made by Analysis Services ...
Click the ellipses ("...")
button to the immediate right of the box currently containing "TimeKey,"
at the intersection of the Fact Sales Quota (Measure Group)
column and the Dim Time (Dimension) row, as shown in Illustration 40.
Illustration 40: Accessing
a Dimension Usage Setting ...
The Define Relationship
selector box labeled Granularity attribute, select CalendarYear.
selector box that appears at the intersection of the row labeled CalendarYear
(under the column marked Dimension Columns) and the column marked Measure
Group Columns, within the Relationship table of the Define
in the selector, as depicted in Illustration 41.
Illustration 41: Select
CalendarYear in the Relationships Table ...
The Define Relationship page appears,
with our new settings, as depicted in Illustration 42.
Illustration 42: Define
Relationship Page with Our Settings
Click OK to
accept settings and close the Define
Within the Dimension
Usage settings, click the ellipses ("...") button to the immediate
right of the box (currently unoccupied) at the intersection of the Fact
Internet Sales (Measure Group) column and the Dim Time (Dimension)
row, as shown in Illustration 43.
Defining Relationship for Fact Internet Sales Dim Time
The Define Relationship
selector box labeled Select relationship type, select Regular.
selector box labeled Granularity attribute, select TimeKey.
selector box that appears at the intersection of the row labeled TimeKey (under
the column marked Dimension Columns) and the column marked Measure
Group Columns, within the Relationship table of the Define
in the selector, as depicted in Illustration 44.
Illustration 44: The
Define Relationship Page with Our Input
have selected ShipDateKey for purposes of our practice exercise, as if it
were organizational accounting policy to record sales as of Ship Date.
This is only to make the exercise more convenient.)
Click OK to
accept settings and close the Define
Usage settings appear, with all modifications to this point, as shown in Illustration 45.
Illustration 45: Dimension
Usage Settings, with Modifications ...
have gained some exposure to aligning measures with dimensions. Each Measure
Group, as we have seen, can be aligned differently with the dimensions of
the cube. This allows us the flexibility in assigning granularity
appropriately when multiple fact tables are involved.
created our cube, and made our adjustments to its Dimension Usage
settings, we are now ready to browse the cube and verify the effectiveness of
our settings. Before we can do this, we must deploy our Analysis Services
solution. We will do so in the section that follows.
Deploy the Analysis Services
the solution will dispatch the code we have created using the Cube Wizard
and Designer interfaces. Once the XML reaches the targeted Analysis
Server (the destination server must be targeted, before beginning
deployment, within the project's Deployment Configuration Properties
page), the Analysis Services database is created and processed. Once
this has been accomplished, we can fully leverage the browser that the Cube
Designer makes available to us.
the ANSYS048 Measure Groups solution, atop the tree in Solution
Solution from the context menu that appears, as depicted in Illustration
Illustration 46: Deploying
the Solution ...
Processing begins, as we
can see in the Deployment Progress pane. Once deployment has
completed successfully, we see a message to this effect in the Status
area, toward the bottom of the pane, as depicted in Illustration 47.
Indication of Successful Deployment Appears ...
left with a processed Analysis Services database and cube. We are ready
for the final part of our article, where we will verify the effectiveness of our
handiwork via the Cube Browser.
Browse the Cube
get to the Cube Browser, at this stage, and see our Measure Groups
at work within the cube, by taking the following steps.
Click the Browser
tab within the Cube Designer, as shown in Illustration 48.
Illustration 48: Click
the Browser Tab ...
tree appearing inside the Metadata pane, on the left side of the Browser
tab, expand Dim Time by clicking the "+" sign to its
Dim Time.CalendarYear - CalendarQuarter - EnglishMonthName
FullDateAlternateKey to the area marked Drop Row Fields Here within
the Pivot Table area, as depicted in Illustration 49.
Creating a PivotTable Browse ...
Within the Metadata
pane, once again, expand Measures.
Expand the Fact
Internet Sales folder.
Amount to the area marked Drop Totals or Detail Fields Here within
the Pivot Table area, as shown in Illustration 50.
Illustration 50: Adding
the First Measure, from the Fact Internet Sales Measure Group ...
Expand the Fact
Sales Quota folder.
10. Drag Sales Amount Quota to
the area marked Drop Totals or Detail Fields Here within the Pivot
Table area, dropping it to the immediate right of the Sales Amount measure.
The Browser pivot
table area appears as depicted in Illustration 51.
Illustration 51: The
Browser Pivot Table with Additions ...
assemble any number of browse scenarios at this point. This includes
juxtaposing various column and row combinations, as well as crossjoining on
either column or row axes, adding filters of various types, and more, within
the Pivot Table (assuming that we have enacted the appropriate Dimension
Usage settings to support the combinations we select). For the purposes of
our exercise, we will simply verify the granularity we have imposed over the Time
dimension hierarchy for each of the measures we have engaged above.
11. Within the column headed "CalendarYear,"
expand the year 2003 by clicking the "+" sign to its immediate
12. Expand quarter 4 (underneath
the CalendarQuarter column heading) within the expanded year.
13. Expand the month of December (underneath
the EnglishMonthName column heading) within the expanded quarter.
dimension hierarchy expands to reveal the totals, by level of
the Time dimension, as shown in Illustration 52.
Illustration 52: The Measures
Totals Appearing at Time Dimension Hierarchy Levels
that the Sales Amount Quota measure remains fixed at the annual
value, within all Time dimension levels, while the Sales Amount
measure "drills down" to the individual date level of the Time dimension
hierarchy. This circumstance allows us to verify the expected operation of the Dimension
Usage settings, which dictate that the granularity of the Sales Amount
Quota (a member of the Fact Sales Quota Measure Group) is the CalendarYear
level. Moreover, we can see that the granularity of the actual Sales Amount
(a member of the Fact Sales Quota Measure Group) is established at the
individual date level.
visualize an array of reporting capabilities at this point, once we add
formatting and other nuances. For now, we will conclude our session, having
focused upon the establishment of granularity for different Measure Groups
to extend our cube to leverage multiple fact tables for desired reporting and
14. Examine further the Measure
Groups within the Cube Browser as desired, perhaps with
corresponding alterations in the Dimension Usage settings to obtain
specific effects. (Remember to re-process the cube after any changes, before
15. Select File -> Save All from the main menu, to save our
work through this point, as depicted in Illustration 53.
Illustration 53: Saving All Work
from Our Session
16. Select File -> Exit, when ready, to leave the Business
Intelligence Development Studio.
In this article, we
examined Measure Groups, which debut in Analysis Services 2005.
After introducing the concept of Measure Groups, we overviewed their
creation, and discussed ways in which they can offer flexibility in cube and
solution / application design and development. In preparation for our
examination of the steps involved in extending our cubes to leverage multiple
fact tables, we prepared Analysis Services, and our environment, by
creating an Analysis Services Project to house our development steps,
and to serve as a platform for the design of a quick cube model, within which
to perform subsequent procedures in our session. We next performed the steps
that are common to the design and creation of any cube within Analysis
Services 2005, including the creation of a Data Source, containing
the information Analysis Services needs to connect to a database, and a Data
Source View containing schema information.
We next created a basic cube, referencing our Data
Source and Data Source View, which contained data from our sample
relational tables. We then focused upon the addition of examples of Measure
Groups as part of cube design, from the perspective of the Cube Wizard.
We then got some hands-on exposure to assigning, via the Dimensional Usage
tab of the Designer, granularity at measure / dimension
intersects for members of the new Measure Groups. Finally, after deploying
our Analysis Services solution, we browsed the cube,
focusing on the new Measure Groups and associated details.
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.