Once upon a time, there was such a thing as a talking car. I never owned one, but I did get to drive one for a week in Quebec while a colleague and I were working with a client up there back in the late 80s. Normally, we were supposed to rent a compact car when out on business, but we had to pick up a bunch of computer equipment at air cargo and there was no way our luggage and the equipment was fitting into a compact. As it turned out, the only car that accommodated us was a New Yorker (and even then it was pretty tight). We quickly discovered that the New Yorker was one of those talking cars - with a French male voice. We named him Pierre and proceeded to try out things to see what he would say and add to our French vocabulary while we were at it. I don't think we had an owner's manual to simply peruse the list of errors we could commit (and should presumably avoid) for which Pierre would gently scold us. As time has shown, demand for Pierre and his counterparts simply didn't hold up in the market. Maybe people can accept warning lights, but not a warning voice?

In SQL Server 2008, the cube and dimension designers in Analysis Services now come with best practice design warnings, but fortunately Dev Studio doesn't read them aloud to you. A visual indicator - which I'll call the blue squiggly - will appear on screen to highlight the offending object. The first warning you're likely to see when you create dimension is associated with the dimension object at the top of the attribute tree. This warning says (in the July CTP), "Create hierarchies in non-parent child dimensions." As soon as you create a user hierarchy, the blue squiggly goes away, right? Nope... now you probably have a new warning on the dimension object if the attributes you selected are all visible - "Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies." And the hierarchy object now probably has a blue squiggly to let you know that there are no attribute relationships defined between one or more levels in the hierarchy. (Remember this is a brand new dimension).

Don't worry about more warnings appearing as you do your design work. Just go about your normal business, and hopefully all will clear up before you're ready to deploy the project. Many of the 48 warnings (in the July CTP) are well-known best practices to experienced Analysis Services developers. So what's the point of including best practices if they are so well-known? Well, not everyone implementing SQL Server for the first time has access to experienced developers, so their experience will be much more positive with Analysis Services if they are warned about the pitfalls before they fall in.

Rather than haphazardly try out something to see whether or not it conforms to best practices, as I did with Pierre, you can jump straight to Books Online to see complete list of the warnings (including links to more information about each). Search for the topic, "Design Warning Rules." The warnings are organized into categories (in the July CTP BOL) as follows: Dimensions, Aggregations, Partitions, Attributes and Attribute Relationships, Measures and Measure Groups, User-defined Hierarchies, ROLAP and MOLAP storage, Data Providers, and Error Handling. Some warnings come with better explanations about best practices than others. I hope this will improve over time, because for the unitiated these warnings without explanation are little more than "because I said so" instead of the educational opportunity it could be.

Like Pierre's reminders that we were doing something contrary to the established best practices of driving, the Analysis Services design warnings are there to alert you to potential hazards, but won't stop you from ignoring them. For example, I'm not certain that I agree that one should always "Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies." This is a matter best decided in conjunction with users, in my opinion, after explaining the pros and cons of this approach. Some implementations may not have this luxury, in which case I would defer to the best practice recommendation.

Some best practices earn a chuckle from me, such as "Define a time dimension." I have yet to meet a cube without one. I had a student insist once that they had seen one, but when pressed could not describe the purpose of the cube. I'm still waiting for a cube without a time dimension. I'm not saying it's not possible, but I can't imagine why you would want one as time-series analysis is one of the most compelling reasons to build a cube in the first place.

Some best practices contradict default values for dimensions (in the July CTP), which also amuses me, such as "Change the UnknownMember property of dimensions from Hidden to None" or "Define attribute relationships as 'Rigid' where appropriate". It seems to me the Analysis Services dev team could easily make the change for default values to accomodate these best practices, as they did with "Do not ignore duplicate key errors. Change the KeyDuplicate property of the error configuration so that it is not set to IgnoreError". To clarify, in SQL Server 2005, the default KeyDuplicate property value is IgnoreError, but this is changed to ReportAndStop in SQL Server 2008.

As mentioned earlier, before you deploy your project, you should clean up - to the extent you wish - the current warnings in your project. Warnings won't stop your deployment, but you should make a conscious decision whether to ignore the surfaced warnings. A comprehensive list of all warnings in your project can be found in the Error List window (which you can open with Ctrl+E). Double-click on an error to access the designer and fix the problem. Alternatively, you can right-click the error and click dismiss to clear it off the list if you don't intend to fix it. You can even add a comment to document your reason for ignoring this error. This method of clearing the error is instance-based and will not clear the same error if it's found in a different dimension or cube. To globally dismiss a particular type of error, whether proactively before you start development or after the fact, you can access the new Warnings tab in the Database editor (which you can open on the Database menu by clicking Edit Database). Incidentally, the Warnings tab also contains a list of the warnings dismissed individually and the related comment.

All in all, I think this is a nice feature in SQL Server 2008 Analysis Services, particuarly for the many folks out there who are just getting started with this technology. Just as long as the warnings stay visual. As much as I like technology in general, I still don't think I'm ready for Dev Studio to start talking to me like Pierre and I suspect many other people feel the same way. --Stacia

In my previous post, I covered the new dimension wizard and mentioned there were options for creating time dimensions that I would cover later. Now I'll explain those options further.

Time Dimension Options in SQL Server 2005 Analysis Services

Let’s start with a quick review of what happens in SQL Server 2005 (referred to as Yukon hereafter). On the Select the Dimension Type page of the dimension wizard, you can choose Standard, Time Dimension, and Server Time Dimension.

If you select Time Dimension, you identify the time table in your DSV and then you map your time columns to the Analysis Services time properties. For example, you map a CalendarYear column in your time table to the Year property. This association of a table column to a property helps your MDX queries how to handle time-related functions like YTD or PeriodToDate.  I admit I find this mapping process tedious, but necessary. When you use a time dimension table, you have to manage the processing of the Analysis Services dimension to add new time members if you incrementally add members to the table (instead of populating it well into the future as some people prefer to do).  The benefit of this approach is the ability to include time attributes that mean something to your industry, such as a flag for a holiday or weekend versus weekday. You also can confirm inclusion of hierarchies based on the columns you map to time properties. I never liked the inability to change the hierarchy names here, but that’s just a nit. You can, of course, add your own hierarchy or modify the hierarchy name later in the dimension editor.

If your time-related analysis is pretty simple and you don’t want to manage a time dimension in your data source, you can create a Server Time Dimension instead. This is a pretty handy feature that lets you define a date range for the dimension, the type of attributes you want to include (year, quarter, month, etc.), the calendars to support (calendar, fiscal, etc.). You can confirm the default hierarchies just like you can with a table-based time dimension. The generated dimension includes several additional attributes, such as Day of Month and Day of Year, and there are other optional attributes you can add using the editor, such as Day of Week or Trimester of Year. When you want to add the Server Time dimension to a cube, you have to add it on the Cube Structure page of the cube designer because the cube wizard doesn’t have a way for you to add it there.  You still need to ensure the end date of the range of your Server Time dimension is equal to or greater than the maximum date in your fact table.

There is a third option available. You could generate a time table by selecting the option to build the dimension without a data source. Select the Date template and you’ll get a similar interface as that for the Server Time Dimension. When you complete the dimension wizard, you have the option to generate the schema  on the spot or you can run the Schema Generation Wizard at a later time. The Schema Generation Wizard lets you create a new data source view for your time table or select an existing DSV. You can even choose to have the wizard populate the time table or you can leave it empty. You’re also given the opportunity to specify naming conventions. By the way, your credentials are used to create the database objects so you’ll need to be sure you have the correct permissions on the data source. This is a nice way to get started with a time table but you’ll need to keep it up-to-date with an ETL tool and you can’t customize it to have more or fewer columns.

Time Dimension Options in SQL Server 2008 Analysis Services

As I mentioned in my previous blog entry, SQL Server 2008 Analysis Services (which I’ll call Katmai from now on) gives you four choices for creating a dimension:

·        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 second and third options relate specifically to a time dimension. If you select “Generate a time table on the server,” you get the same result as the Server Time Dimension in Yukon and an almost identical interface in the wizard. The exception is that the hierarchy confirmation page is missing in Katmai – which is fine for me as I’d rather fine-tune the hierarchy in the editor anyway.

The new option, “Generate a time table in the data source,” is the same as Yukon’s option to build the dimension from the Date template. You run the Schema Generation Wizard to design the table and optionally to populate it the first time.

So what do you do if you have a time dimension in your data source? The only option is to choose “Use an existing table.” On the Select Dimension Attributes page of the dimension wizard, which you use to select the columns from your table to include in your dimension, you have the ability to change the attribute type. This is the equivalent of mapping columns to time properties in Yukon, although I must say the interface is not ideal for this task. In short, more clicks are required to set up your time dimension from a table, but you have the benefit of getting a table and all the attributes exactly the way you want them.

Conclusion

So, functionally, nothing has really changed much for time dimensions in Katmai apart from renaming of options and some slight interface adjustments. If you base your time dimension on a table, the interface changes make the process to create the time dimension in the Analysis Services database a bit more tedious, in my opinion. Fortunately, these aren’t tasks that you have to repeat every day and, if you want to reproduce the time dimension in another cube, you can always script it out rather than build it through the wizard.  --Stacia

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