Creating Dimensions in SSAS

To better help your users with data analysis, you create proper dimensions, which we covered in Part 1 of this article at InstantDoc ID 98510. We also discussed how to process the dimensions, and we found that our two-dimensional project was showing a warning symbol next to the Time dimension name. This wasn’t caused by the overly long name, but it does indicate a fundamental problem with dimensions, which we’ll examine shortly by creating a cube. Then we’ll look at how to create attribute relationships, which tell the cube how dimension attributes relate to each other and help to speed the data analysis process.

Creating the Cube

To understand why the Time dimension is showing a warning on its one hierarchy, let’s first build a cube. Right-click the Cubes folder in the Solution Explorer and select New Cube to open the Cube Wizard. The first screen confirms that the cube will be built from a data source, while the next screen shows the list of data source views. Next, the wizard attempts to determine which tables are dimension tables and which are fact tables. Normally the wizard determines this correctly based on the joins in the underlying relational database, but sometimes you’ll have to perform minor tweaks to correctly specify the table types. In addition, the page asks for the name of the time dimension table, if one exists; I selected DimTime as the time dimension table.

The next screen shows up only if dimensions already exist — it asks developers to select any existing dimensions they want in the cube. If additional dimensions are required, the cube wizard will create them, but for this example I’ll select the two dimensions just created. The next screen shows a list of all the possible measures from the fact table. The wizard selects any field with a numeric data type, so you must remove the foreign key fields in most cases. For this example I will select only the Sales Amount field.

After completing the wizard, the cube actually shows four dimensions: Product, Ship Date, Order Date, and Due Date. This is because there are three joins from the fact table back to the time dimension table. This is called a role-playing dimension and is discussed in the article "Data Warehousing: Dimensional Role-Playing," December 2007, InstantDoc ID 97272.

Now the cube can be processed. All the data from the fact table is read and loaded into the cube, ready for analysis.

Examining the Problem

This simple cube contains only Product and Time dimensions along with a single measure from the FactInternetSales table: Sales Amount. Figure 1 shows a view of the Sales Amount measure and the Calendar Quarter attribute from the Order Date dimension. Note that the Calendar Quarter attribute isn’t being pulled from the Calendar hierarchy but is simply the standalone attribute. Normally, attributes added to a hierarchy are then hidden as standalone attributes so as not to confuse users.

The query returns proper results, but notice that there are only four quarters shown. Each quarter is the total for that quarter for all years; normally, users want to see quarters broken down by year. By placing the Calendar Year attribute on the grid before the Calendar Quarter, the dimension will work as expected. However, there are two problems: First, users must know which attributes to drag over and in which order. Second, aggregations and indexes aren’t being built to facilitate the normal analysis that will be done, which is drilling down the Year to Quarter to Month to Day path.

Recall that the hierarchy, now called Calendar, had a warning indicator next to it. This warning indicates that this is not a natural hierarchy, and the term “natural hierarchies” will be explained in a moment. Therefore, queries at any level of the hierarchy other than the lowest level are suboptimal.