Perform Basic
Modifications to 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.
20.
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 35.
Illustration 35: The
Measures within their Respective Groups ...
While we can alter the Measure Groups to suit the nature of the measures, and would likely
normally do so to manage their respective granularities, and so forth, we will
leave these and other cube objects largely as they have been created by
default, to allow us to focus upon the central subject matter of this session.
We will make just a few alterations to flesh out the "views" that are
relevant to the Perspectives which we will define within the Procedure
section that follows.
NOTE: For more information on the creation and management of Measure Groups, see Mastering
Enterprise BI: Working with Measure Groups, a member of my Introduction to MSSQL Server Analysis Services series at Database Journal.
Perform Basic Modifications to Measure Groups
We
will make our minimal modifications to the Measures Groups within the Cube Designer,
before making alterations to suit the nature of the measures. The basis of our
modifications, typically enough, is that the granularities of the Measure
Groups are different. As an illustration, Sales Amount, together
with the other measures from the Fact Internet Sales table, is related
to the actual date (the "TimeKey" field in the Time
dimension) of the transaction, 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 "single
apples from baskets of apples," we need to ensure that our Measure
Groups are dimensionally aligned to reflect their true nature.
1.
Click the Dimension
Usage tab atop the Cube Designer, as shown in Illustration 36.
Illustration 36: Click
the Dimension Usage Tab ...
The Dimension Usage page appears.
2.
Click the box currently containing "TimeKey,"
at the intersection of the Fact Sales Quota (Measure Group)
column and the Dim Time (Dimension) row.
3.
Click the
ellipses ("...") button that appears to the immediate right of the selected
box, as depicted in Illustration 37.
Illustration 37:
Accessing a Dimension Usage Setting ...
The Define Relationship page appears.
4.
In the
selector box labeled Select relationship type, ensure that Regular remains
selected.
5.
In the
selector box labeled Granularity attribute, select CalendarYear.
6.
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.
7.
Select CalendarYear
in the selector, as shown in Illustration 38.
Illustration 38: Select
CalendarYear in the Relationships Table ...
The Define Relationship page appears, with our new
settings, as depicted in Illustration 39.
Illustration 39: The Define
Relationship Page with Our Settings
8.
Click OK to
accept settings and close the Define Relationship page.
9.
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 40.
Illustration 40:
Defining Relationship for Fact Internet Sales – Dim Time
The Define Relationship page appears.
10.
In the
selector box labeled Select relationship type, select Regular.
The
page expands to display previously hidden fields, which support definition of
the Regular dimension type.
11.
In the
selector box labeled Granularity attribute, select TimeKey.
12.
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.
13.
Select ShipDateKey
in the selector, as depicted in Illustration 41.
Illustration 41: 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.)
14.
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 42.
Illustration 42:
Dimension Usage Settings, with Modifications ...
We can obviously perform myriad enhancements and extensions to, and operations
surrounding, our cube from within the multi-tabbed Cube Designer component of the
Business Intelligence Development Studio. We examine the many options 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). At this point, we have
sufficiently aligned measures with dimensions to complete our preparation of an
environment within which we can get some hands-on exposure to the definition of
Perspectives within our cube.
» 
Comment and Contribute
