Upgraded Foodmart 2000 sample database for Analysis Services 2005

Soon after installing Analysis Services 2005 I upgraded the Foodmart 2000 sample database from Analysis Services 2000. Foodmart is kind of the equivalent of the pubs database in the SQL Server world. Sample queries and questions on newsgroups are often posted using Foodmart.

Someone else in the newsgroups asked if anyone had an upgraded copy of this database as they had Mosha's “Fast track to MDX” book and all the samples are against Foodmart 2000.

I did a backup of it both with and without data and it is available here.

Update 14 Dec 2009: Now linking to these files on my Sky Drive

Unfortunately just having access to this database is not going to solve all this persons problems. This is due to the fact that in Analysis Services 2000, multiple hierarchies in a dimension were really not a lot more than 2 separate dimensions with a dot in the name. Where as in Analysis Services 2005 everything is setup to work with multiple hierarchies.

In Analysis Services 2000, most references follow the pattern of <dimension>.<level>, which means that a set of month members would look like [Time].[Month].Members.

In Analysis Services 2005, most references follow the pattern of <dimension>.<hierarchy>.<level>, so you end up with sets like [Time].[Month].[Month].Members. Where the first [Month] refers to the Month hierarchy and the second [Month] refers to the Month level. Often in Analysis Services 2005 queries or calculations from Analysis Services 2000 will not work properly, if at all without the extra hierarchy specification.

The other interesting facet of this is that it means that it is often possible to deduce what version of Analysis Services someone is using just be looking at the format of the queries they issue.

Print | posted on Sunday, April 2, 2006 4:38 PM