Create a Cube with Basic Objects
Create a Cube
We will create a basic
cube, with measures we will add as a part of cube creation, within which we
will examine Translations, the focus of our practice session. We will
concentrate 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 Translations – not to concern ourselves with the myriad
other options and topics contained within general cube design and development.
1.
Right-click
the Cubes folder within the Solution Explorer.
2.
Select New
Cube ... from the context menu that appears, as depicted in Illustration 23.
Illustration 23: Select
New Cube to Begin Cube Creation
The Welcome to the
Cube Wizard dialog appears, as shown in Illustration 24.
Illustration 24: The
Welcome to the Cube Wizard Dialog Appears ...
3.
Click Next.
4.
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.
These
default settings are depicted in Illustration 25.
Illustration 25: Ensure
the Default Settings on the Select Build Method Dialog
5.
Click Next.
6.
Ascertain that
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 dialog).
The Select Data Source
View dialog appears, as shown in Illustration 26.
Illustration 26: Select
AdventureWorks DW as the Data Source View
The Cube
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 27.
Illustration 27: The
Detecting Fact and Dimension Tables Page Indicates Completion ...
The Identify
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.
9.
Select the DimTime
table in the drop-down Time dimension table selector.
10.
Uncheck the following checkbox, on the Tables
tab of the dialog.
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 28.
Illustration 28: The
Identify Fact and Dimension Tables Dialog, with Our Modifications
The Select
Time Periods dialog appears.
12.
Leaving any
unspecified columns blank, select the Time Table Columns values shown in
Table 2 for each of the listed Time Property Names.
|
Time Property
Name
|
Time Table
Columns
|
|
Year
|
CalendarYear
|
|
Quarter
|
CalendarQuarter
|
|
Month
|
EnglishMonthName
|
|
Date
|
FullDateAlternateKey
|
Table 2: Selections within Respective Time Table Columns in
Select Time Periods Dialog
The Select Time Periods dialog appears, with our selections, as depicted in Illustration 29.
Illustration 29: The
Select Time Periods Dialog, with Our Selections
The Select
Measures dialog appears next.
14.
Uncheck the following checkboxes:
-
Revision
Number
-
Discount
Amount
-
Product
Standard Cost
-
Tax Amt
-
Freight
The Select Measures dialog appears, as partially shown in Illustration 30.
Illustration 30: The
Select Measures Dialog (Partial View), with Adjusted Selections
The Detecting
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 31.
Illustration 31: The
Detecting Hierarchies Page, with Indication of Relationships Detected
We
move to the Review New Dimensions page, which appears as shown in Illustration
32.
Illustration 32: The
Review New Dimensions Page
The Completing
the Wizard page appears next.
18.
Change the
default Cube name to the following:
ANSYS051 – Translations
The Completing
the Wizard page appears as depicted
in Illustration 33.
Illustration 33: The
Completing the Wizard Page
The
Wizard concludes, and the Cube Designer opens, as shown in Illustration
34.
Illustration 34: The
Cube Designer Opens ...
Perform Basic
Modifications to Measure Groups
Because
our cube now 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 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 finish fleshing out a representative
set of objects for general examination as we explore Translations 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.
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 Translations within
our cube.
» 
Comment and Contribute
