Create a Basic Cube
We will create a basic
cube, to house our Named Calculations, and to see how we put them to
work in the dimensional structure. We will focus on other areas within the
overall design and creation process in other articles of the series – our primary
objective here is to see how we use the Named Calculations we have
created – not to concern ourselves with the myriad other topics contained
within general cube design.
1.
Right-click
the Cubes folder within the Solution Explorer.
2.
Select New
Cube from the context menu that appears, as shown in Illustration 4.
The Welcome to the
Cube Wizard dialog appears, as depicted in Illustration 5.
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 shown in Illustration 6.
These settings will allow the Wizard to leverage the IntelliCube technology built into Analysis Services 2005, 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.
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 depicted in Illustration 7.
Illustration 7: 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
shown in Illustration 8.
Illustration 8: 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 checkboxes:
Fact Column: DimReseller (leave checked within Dimension
column)
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 depicted in Illustration 9.
Illustration 9: 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 1 for each of the listed Time Property Names.
|
Time Property Name
|
Time Table
Columns
|
|
Year
|
CalendarYear
|
|
Quarter
|
CalendarQuarter
|
|
Month
|
EnglishMonthName
|
|
Date
|
FullDateAlternateKey
|
Table
1: Selections within Respective Time Table Columns in Select Time Periods
Dialog
The Select Time Periods dialog appears, with our selections, as
shown in Illustration
10.
Illustration 10: 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 Amount
-
Freight
The Select Measures dialog appears, as depicted in Illustration 11.
Illustration 11: The
Select Measures Dialog, 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 shown in Illustration 12.
Illustration 12: The
Detecting Hierarchies Page, with Indication of Relationships Detected
We
move to the Review New Dimensions page, which appears as depicted in Illustration
13.
Illustration 13: The Review
New Dimensions Page
The Completing
the Wizard page appears next.
18.
Change the
default Cube name to the following:
ANSYS045 - Named Calculations
The Completing
the Wizard page appears as shown in Illustration 14.
Illustration 14: The Completing
the Wizard Page
The
Wizard concludes, and the Cube Designer opens, as depicted in Illustration
15.
Illustration 15: The
Cube Designer Opens ...
Here,
within the multi-tabbed Cube Designer component of the Business
Intelligence Development Studio, we can perform myriad enhancements and
extensions to, and operations surrounding, the existing cube. We will be
examining these new features in prospective articles, where we define business
needs and then meet them with the appropriate functionality(ies). For the
purposes of this article, let’s put our new Named Calculations to work,
and verify their effectiveness in our basic cube.
» 
Comment and Contribute