Mastering Enterprise BI: Introduction to Translations - Page 5
September 11, 2006
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.
The Welcome to the Cube Wizard dialog appears, as shown in Illustration 24.
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.
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.
7. Click Next.
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.
8. Click Next.
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.
11. Click Next.
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.
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.
13. Click Next.
The Select Measures dialog appears next.
14. Uncheck the following checkboxes:
The Select Measures dialog appears, as partially shown in Illustration 30.
15. Click Next.
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.
16. Click Next.
We move to the Review New Dimensions page, which appears as shown in Illustration 32.
17. Click Next.
The Completing the Wizard page appears next.
18. Change the default Cube name to the following:
The Completing the Wizard page appears as depicted in Illustration 33.
19. Click Finish.
The Wizard concludes, and the Cube Designer opens, as shown in Illustration 34.
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.
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.
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.
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.
The Define Relationship page appears, with our new settings, as depicted in Illustration 39.
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.
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.
(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.
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.