Creating the Database Dimensions
As I explained in the introduction, dimensions provide the descriptive attributes of measures and hierarchies that are used to provide non-leaf level aggregations. You should understand the difference between a database dimension and a cube dimension: database dimensions provide a base dimension object for multiple cube dimensions to be built upon.
Database and cube dimensions provide an elegant solution to a concept known as role-playing dimensions. Role-playing dimensions are when you need to use a single dimension multiple times in a cube. Date is a great example—in the sample cube, you'll be building a single date dimension and referencing it once for each date for which you want to analyze Internet sales.
Date will be the first dimension you create. Right-click the Dimensions folder in Solution Explorer and select the New Dimension option to launch the Dimension Wizard. Select the Use an existing table option and click Next in the Select Creation Method step. In the Specify Source Information step, select DimTime in the Main table drop-down and click the Next button. Now you need to create the Time dimension's attributes in the Select Dimension Attributes step. Select every column, as shown in Figure 6.
 |
Figure 6: Selecting the Time dimension attributes. Click to expand. |
Click Next. In the Completing the Wizard step, type Dim Date in the Name textbox, and click the Finish button to complete the Dimension Wizard. You should now see a new Dim Date dimension located under the Dimensions folder in Solution Explorer.
Next you'll use the Dimension Wizard to create the Product and Customer dimensions. Use the same steps to create a basic dimension that you used before. When you're running through the Dimension Wizard, make sure you select all potential attributes in the Select Dimension Attributes step for both dimensions. The default values for all other settings will suffice for the sample cube.
Bringing It All Together: Building the Internet Sales Cube
With your database dimensions built, you're now ready to build the cube. In Solution Explorer, right-click the Cubes folder and select the New Cube option to launch the Cube Wizard. Select the Use existing tables option in the Select Creation Method screen. Select the FactInternetSales table for the Measure Group in the Select Measure Group Tables step. Remove the check next to the Promotion Key, Currency Key, Sales Territory Key, and Revision Number measures in the Select Measures step and click Next.
On the Select Existing Dimensions screen, make sure all existing database dimensions are checked to reuse them in the cube as cube dimensions. Because I want to keep this cube as simple as possible, uncheck the FactInternetSales dimension in the Select New Dimensions step. (By leaving the FactInternetSales dimension checked here, you'd be creating what's called a Fact dimension or degenerate dimension. Fact dimensions are dimensions that are created by using an underlying fact table as opposed to a traditional dimension table.)
Click the Next button to advance the wizard to the Completing the Wizard step and type "My First Cube" in the cube name textbox. Click Finish to complete the cube wizard process.
Deploying and Processing the Cube
You're now ready to deploy and process your first cube. Right-click the new cube in Solution Explorer and select the Process option. You'll now see a dialog box informing you that the server content appears to be out of date. Click Yes to deploy your new cube to the target SSAs server. When you deploy a cube, you're technically sending XML for Analysis (XMLA) to the target SSAS server, which creates the cube on the server. As mentioned earlier, processing a cube populates its binary files on disk with data from the underlying data source, including the additional dimensional metadata you've added (dimension, measure, and cube settings).
Once the deployment process is complete, a new Process Cube dialog box is displayed. Click the Run button to process the cube, and a Process Progress dialog box will be displayed. Once cube processing has completed, click the Close button (twice to close out both dialog boxes) to complete the cube's deployment and processing.
You've now built, deployed, and processed your first cube. You can browse your new cube by right-clicking the cube in Solution Explorer and clicking Browse. Drag and drop the measures in the center of the pivot table and the dimension attributes on the rows and columns to explore your new cube. Observe how fast the cube returns your various aggregation queries and think back to my earlier discussion about the weaknesses of relational database aggregation queries. You should now comprehend the raw power, and thus the business value, of an OLAP cube.