Creating Dimensions in SSAS

Creating the best possible cubes in SQL Server Analysis Services (SSAS) 2005 and 2008 requires good dimension design, including creating the proper attributes and making meaningful hierarchies with those attributes. Well-designed dimensions ensure that the data in cubes calculates correctly so users can analyze that data and turn it into useful information.

Let’s explore how to create dimensions in SSAS. Then, in the Web-exclusive follow-up article “Creating Dimensions in SSAS Part 2,” InstantDoc ID 98699, we’ll look at more aspects of dimensions, including creating a cube and analyzing attribute relationships.

Create a Data Source 

The first step in working with an SSAS project is to create a data source. I use the AdventureWorksDW database, one of the sample databases available for SQL Server 2005 and SQL Server 2008. Next, you need to create a data source view (DSV), which Figure 1 shows. A DSV is a logical representation of a schema and includes tables or views from one or more databases, queries that act like views but only exist in the DSV, and more. For this example, I’ll add the following tables to the DSV: DimProduct, DimProductCategory, Dim- ProductSubCategory, DimTime, and FactInternetSales. This makes a very simple snowflake schema that will have two dimensions: Time and Product.

At this point, you have a choice: You can run either the cube wizard or the dimension wizard. The cube wizard creates one or more of the dimensions if they don’t already exist, then proceeds to create the cube. The dimension wizard walks you through the process of creating dimensions one at a time, and of course doesn’t create cubes. To better explain the process and show what’s being created, I use the dimension wizard to create the Product and Time dimensions.

Create the Product Dimension 

Right-click the Dimensions folder in the Solution Explorer and choose New Dimension to launch the dimension wizard. The first page enables users to build a dimension with or without a data source. Normally you build a dimension with a data source, and when you select this option, you’ll see a check box for automatically building attributes and hierarchies (although this can be changed to create just attributes.) Accept the defaults and click the Next button to advance the wizard to the page for selecting a DSV. This project has only one DSV, so you simply click Next.

The wizard then asks you to select the dimension type: Standard, Time, or Server Time. The Time dimension option adds an extra step, which I’ll cover in a moment. The Server Time dimension creates a dimension table based on a start date, end date, and a selection of levels. Standard dimensions, to the wizard, are anything that isn’t a time dimension. Most cubes will have a Time dimension and several standard dimensions, as is the case here. Select the Standard dimension option and click Next.

Choose the main dimension table. After you select the table, the columns are listed and the key column, if it can be determined, is checked. Here you can change the key column as necessary. By changing the column name, the actual value will continue to be the key, but the user will see a more familiar, descriptive value.

After you click Next, you’ll see a screen verifying related tables. This screen appears only when the dimension is made up of multiple tables, as is the case with a snowflake schema.

Click Next to advance to the Select Dimension Attributes page. This page lists all the columns in the table(s) making up the dimension. In SSAS, each column becomes an attribute and can be used for analysis independently from any other attribute. For products, this means that users can analyze by such attributes as size, color, and weight, without the need to create separate dimensions. The ability to analyze by any attribute is extremely powerful but can be confusing for end users faced with dozens of attributes. The cube developer can remove attributes at this stage and also hide attributes in the dimension after the dimension has been created.

The next screen in the dimension wizard asks for a dimension type; most dimensions will work fine as regular dimensions, so select Regular and move to the next screen. This screen asks if the dimension contains a parent-child attribute, which it doesn’t, so it’s safe to continue to the next screen.

The wizard now attempts to detect hierarchies, but fails to find any in this case. That’s too bad because there is a clear hierarchy here (ProductCategory to Product- SubCategory to Product), but you have to create it manually after the wizard is done. Click the Next button a couple of times until you see the Completing the Wizard screen. This screen shows the completed dimension with the attributes and, if any are found, the hierarchies. Here you can rename the dimension if desired; many users prefer to drop the word “Dim” from the front of the dimension, naming it simply “Product.” Click Finish to create the dimension.

Create the Time Dimension

Now let’s move on to the Time dimension and look at the differences between a time and a non-time, or standard, dimension. The initial process is the same: Rightclick the Dimensions folder in the Solution Explorer and choose New Dimension, accept the defaults to have the dimension built with a data source, and select the AdventureWorksDW DSV.

The next screen is the Select the Dimension Type page, and this time you click Time dimension and select dbo_DimTime in the drop-down list. The wizard shows an extra screen called Define Time Periods, where you assign various columns in the Time dimension table to time properties. I assigned only four of the columns to keep the example simple.

Now the wizard displays the hierarchies it’s identified. The Time dimension almost always has at least one hierarchy if you’ve assigned columns to the time properties. Figure 2 shows the hierarchy resulting from the four columns I assigned. It contains the unfortunate name “Calendar Year – Calendar Quarter – Month Number Of Year – Full Date Alternate Key.” I’ll change it to just “Calendar” later. You can also change the names of the levels within the hierarchy, remove the entire hierarchy, or remove certain levels within the hierarchy, as needed.