Work with Measure
Groups
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.
1.
In the Measures
pane, expand the three Measure Groups, Fact Internet Sales,
Fact Sales Quota and Fact
Internet Sales Reason.
The
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.
2.
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 ...
3.
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
page appears.
4.
In the
selector box labeled Granularity attribute, select CalendarYear.
5.
Click the
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
Relationships page.
6.
Select CalendarYear
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
7.
Click OK to
accept settings and close the Define
Relationship page.
8.
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.
Illustration 43:
Defining Relationship for Fact Internet Sales – Dim Time
The Define Relationship
page appears.
9.
In the
selector box labeled Select relationship type, select Regular.
10.
In the
selector box labeled Granularity attribute, select TimeKey.
11.
Click the
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
Relationship page.
12.
Select ShipDateKey
in the selector, as depicted in Illustration 44.
Illustration 44: The
Define Relationship Page with Our Input
(We
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.)
13.
Click OK to
accept settings and close the Define
Relationship page.
The Dimension
Usage settings appear, with all modifications to this point, as shown in Illustration 45.
Illustration 45: Dimension
Usage Settings, with Modifications ...
We
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.
Having
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.
» 