While there are several new features slated for Analysis Services that haven’t been released in a CTP yet, the July CTP does include a new dimension wizard. This wizard is intended to simplify your work by streamlining the steps involved to set up a new dimension. Today, as I walk you through the new wizard in SQL Server 2008 (which I’ll henceforth call Katmai throughout this article), I’ll explain how it’s lived up to the promise of an improved design experience and remind you how it’s different from the dimension wizard in SQL Server 2005 (which I’ll refer to as Yukon).
Of course, before you can add a dimension, you need to add a data source and data source view (DSV). Nothing has changed here. If an attribute doesn’t exist in the format you want in the physical data source, you will still need to add a named calculation (or a derived column in a named query) in the DSV before you add the attribute to the dimension. For example, if you have FirstName and LastName columns in a customer dimension table, but want to display “LastName, FirstName”, you’ll need to concatenate the columns in the DSV.
Step 1: What’s the source for your dimension?
Once the DSV is just right, you can kick off the dimension wizard. In Yukon, the first main page of the wizard is “Select Build Method” which gives you two choices for creating a dimension. I use the bottom-up approach most often – that is, I build the dimension using a data source which in turn is associated with a DSV which includes one or more tables for the dimension. Alternatively, there is the top-down approach, or more officially “Build the dimension without using a data source” which lets you describe the design and generate a table schema in your data source. If you leave the Auto build check box selected, then the wizard recommends the key column for the dimension and looks for hierarchies (although my experience with auto-detected hierarchies has been inconsistent). You then click Next to select the DSV and click Next again to specify whether you’re creating a standard dimension, a time dimension based on a table in your DSV, or a server-based time dimension. To recap, not counting the welcome page of the wizard, you go through three pages of the wizard to define the type and source of the dimension you want to build.
In Katmai, the three pages have been consolidated into one page – Select Creation Method – which gives you the following choices:
- Use an existing table
- Generate a time table in the data source
- Generate a time table on the server
- Generate a non-time table in the data source (using a template)
The first and fourth options are equivalent to the options you have in Yukon. I’ll discuss the time table options in a future blog entry. For now, I’ll continue through the wizard using an existing table.
Step 2: Which table is the main dimension table and which are the key and name columns?
In Yukon, on the Select the Main Dimension Table page of the wizard, you first select the dimension table (or the most granular table in a snowflake schema). Then you select one or more key columns in the table to uniquely identify each dimension member. Optionally, you select a column to represent the member name.
In Katmai, the only change here is that one page – Specify Source Information – allows you to select the DSV and the dimension table selection. You also specify the Key and Name columns on this page. The interface is slightly different if you want to use a composite key – using a drop-down list instead of check boxes. I think this will wind up requiring more mouse movement than the previous interface, so I’m not wild about this last change, but practically speaking I rarely use composite keys in a dimension so it’s probably a negligible change.
Step 3: Which columns are dimension attributes?
On the Select Dimension Attributes page (the next page in both Yukon and Katmai), a list of all remaining columns displays. In Yukon, you won’t see the key or name columns in this list, but in Katmai the key column is included in the list. In Yukon, all attributes are selected by default (if you kept Auto Build enabled) whereas in Katmai only the key column is selected by default.
In Yukon, you see the same column name in the list’s columns labeled Attribute Key Column and Attribute Name Column. I liked this feature to update name columns for snowflaked schemas. Unfortunately, this feature goes away in Katmai. You’ll have to update the name column in the dimension editor directly. Not the end of the world, I suppose, but it’s a feature I use enough to really notice it’s missing.
Katmai adds another feature to this page which I’ll concede compensates for the inability to specify the Attribute Name Column. Specifically, there is a Enable Browsing check box for each attribute. This is a nice quick way to quickly and efficiently set the AttributeHierarchyEnabled property to False which means the attribute can’t be placed on an axis in a query (i.e. you can’t put it in rows or columns or in the filter). Disabled attributes are useful for things like phone numbers or addresses – you don’t really analyze this information but your client application can make it available to the end user as a tooltip, as an example. On this page, you can also specify the attribute type, although I don’t know too many people who actually use this often for non-time dimensions.
Step 4: What is the dimension name?
The final page in Katmai allows you to name the dimension and you’re done after going through a grand total of four pages! Before I get to this point in Yukon, I have to specify a dimension type (which is usually Regular), define a parent-child hierarchy (which should self-detect anyway and which I avoid whenever possible), two pages for hierarchies (detecting and reviewing) and then I reach the final page to give the dimension a name. For a standard dimension with auto build enabled in Yukon, you have to go through ten pages. That’s quite a difference and therefore Katmai considerably streamlines the basic development of a dimension with the new dimension wizard.
There are a few more Analysis Services features in the July CTP that I’ll review in future blog entries. Check back soon! –Stacia