Building Cubes with SQL Server 2005 Analysis Services
Next we need to define some time periods. In this screen you will match the name of a column in the source table to the name of the property in the Time dimension. We will make the following selections (Figure 11):
Then click ‘Next’ to review the available Measures for the cube (Figure 12).
Take a minute to examine the names of these measures. You will see names that are tagged as “Amount”, “Cost”, and “Key”. Look back to how we defined measures, and this screen should be clearer to you as to how the wizard arrived at these conclusions. We will accept the defaults and click ‘Next’. The wizard will now detect the hierarchies available and when complete you should see the following screen (Figure 13):
Now you can review the dimensions that were created and make adjustments if desired. Notice the wizard says “All relationships detected”. Refer back to how we defined a hierarchy and this screen should be clearer to you. Of course we are going to take the defaults and simply click ‘Next’, choose a name for our cube (Adventure Works Cube), and then select ‘Finish’. You should be taken to the design view of the cube inside of BIDS.
The Cube is Built, Now What?
Now we can start looking through some of the basic features inside the cube designer. The first screen you would be viewing after the cube is built is the cube designer. Here you will be able to explore the Measures and Dimensions on the left hand side of the GUI. You should recall that measures tend to be items that are, well, measurable and also note how the Fact table is being displayed here.
The Hierarchies and Attributes are displayed just below the Measures. Browsing through the Hierarchies you will see that most of the links simply bring you to a design view of a dimension. If you go to “Ship Date”, you will find an example of two hierarchies that have been defined by the Auto-Build process. Click on the “Edit Dim Time” link to go to the design view for the Time Dimension, and note that there are two hierarchies defined for this dimension. Close this design view and go back to the design view for the cube. Now, click on the Attributes tab and you will see the attributes associated with the dimensions. Again, this information is also displayed in the design view for the Dimensions. You may be wondering at this point about the “Ship Date” that is listed. After all, it is not listed on the right hand side under the Dimensions section of the Solution Explorer. So, what is “Ship Date”? Is it a dimension? Is it a fact? Where did it come from?
The short answer is that it was created by the auto-build process. But why? Well, the process saw a column named ShipDateKey in the FactInternetSales table inside the AdventureWorksDW database, which is what we used to build our data source and view. This column is defined as a foreign key column to the DimTime table. So, the process decided to build an extra dimension for our use. Click on the very next tab named “Dimension Usage” and you should see a list of all available dimensions, including one named “Dim Time (Ship Date)”. Scroll to the right and you will see the connection made to the FactInternetSales table.
With the exception of the “Browser” tab at the far right, the remaining tabs are for functionality that is outside the scope of this introductory document. The Browser tab will let us start to examine the data, so let’s get started.
Before we are allowed to browse the cube, we need to process and deploy the project to the SSAS instance. Right-click on the project name in the Solution Explorer and select ‘Properties’. Make certain you are deploying to an instance of SSAS: