Mastering Enterprise BI: Working with Measure Groups - Page 5
June 12, 2006
Create a Cube and Work with Measure Groups
Create a Basic Cube
We will create a basic cube, to house our Measures, and the Measure Groups to which they will belong. We will focus 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 Measure Groups not to concern ourselves with the myriad other options and 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 depicted in Illustration 25.
The Welcome to the Cube Wizard dialog appears, as shown in Illustration 26.
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 27.
These settings will allow the Wizard to leverage the technology built into Analysis Services, 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 shown in Illustration 28.
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 29.
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 checkboxes:
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 30.
11. Click Next.
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.
Table 1: Selections within Respective Time Table Columns in Select Time Periods Dialog
The Select Time Periods dialog appears, with our selections, as depicted in Illustration 31.
13. Click Next.
The Select Measures dialog appears next.
14. Uncheck the following checkboxes:
The Select Measures dialog appears, as partially shown in Illustration 32.
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 33.
16. Click Next.
We move to the Review New Dimensions page, which appears as shown in Illustration 34.
17. Click Next.
The Completing the Wizard page appears next.
18. Change the default Cube name to the following:
ANSYS048 Measure Groups
The Completing the Wizard page appears as depicted in Illustration 35.
19. Click Finish.
The Wizard concludes, and the Cube Designer opens, as shown in Illustration 36.
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 selected cube. We examine these new features 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). For the purposes of this article, let's get some exposure to Measure Groups, and verify their effectiveness in our basic cube.