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

Comments on this post

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
Hi Darren,
thanks for the file. Will give it a go, bearing in mind your comments on the differences with SSAS 2005.
Cheers,
Donovan.
Left by Donovan Hide on Apr 03, 2006 5:27 PM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
Hi Darren,
i recently bought a book on analysis services and i'm trying to deploy the project included with the book(the book is Microsoft SQL Server 2005 Analysis Services by Melomed, Gorbach, etc). I'm having trouble getting the sample project project deployed. I know i have the original mdf installed correctly....when i try to deploy the analysis services project that goes with that database i get a message box that says the server content appears to be out of date. then it throws an error that says "No mapping between account names and security ids was done". do you have any idea what the problem relates to? i'm new to this so maybe i skipped an important step, but i don't know where to begin.
Thanks.
Richard Garth
Left by RICHARD GARTH on May 22, 2007 8:31 AM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
It will play an important role for Data Warehousing people
Left by Md. Mizanur Rahman on Dec 08, 2007 9:08 PM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
Darren,

Thanks for upgrading these samples for us.
I see the link http://projectdistributor.net/Files/73/201/331_Binaries.zip no longer works. Can you post the files to another server or point me to another source?

Thanks,
Marty
Left by Marty Wilson on Jan 08, 2008 3:42 AM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
Marty,

Yeah, the ProjectDistributor site was down for a while, but it is back up now so you should be good to go.

Darren
Left by Darren Gosbell on Jan 08, 2008 8:59 AM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
I used your samples and got them working but when I process the Trained cube and the Warehouse and Sales cubes I get the following error.

Errors in the OLAP storage engine: The linked measure group with the ID of 'Sales', Name of 'Sales' cannot be processed because it contains MG dimension with the ID of 'Time', Name of 'Time' with different granularity attribute than its source object.

Any Ideas
Left by R.Smith on Apr 09, 2008 4:24 AM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
Yes, It appears that the RTM version of the migration wizard produced this mixed granularity, but then they added some extra validations to the processing engine in SP2. I have manually fixed the granularity issue and created a new version of the database here: http://cid-98546e1b65a78a74.skydrive.live.com/browse.aspx/Public/Foodmart%202000%20Upgrade%20-%20SP2 - see if that works for you.
Left by Darren Gosbell on Apr 09, 2008 6:38 AM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
Hi Daren,

I can't download the zip-file under:
http://projectdistributor.net/Files/73/201/331_Binaries.zip

Is it possible to get it from a different source?

Thank you!
Christian

Left by Christian on Feb 05, 2009 8:09 PM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
Yes, it appears that the site where I was hosting those files has gone off the air.

I have a the original version and the one with the corrected relationships to the time dimension on my skydrive here
http://cid-98546e1b65a78a74.skydrive.live.com/browse.aspx/Public/Foodmart%202000%20Upgrade%20-%20SP2
Left by Darren Gosbell on Feb 05, 2009 10:51 PM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
Darren,

Thanks a lot for the quick response!

Christian
Left by Christian on Feb 06, 2009 12:04 AM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
Hi All,

I struggled with this AS 2005 issue for a month in my virtual cube after migration.

I did not find the solution anywhere on the internet and finally discovered the reason of error and where to correct. Please see if this solution applies to your case as well.

I had following case:

1) I had designed a virtual cube in AS 2000 with dimensions from 2 of my actual cubes. Say VCube importing dim1, dim2 from Cube1 and others from Cube2.

2) At the time of migration, the virtual cube gets converted to a real cube in AS 2005 and all the measures groups and dimenstions become LINKED type.

3) The dimension which was throwing the "MG Dimension" error when deleted helped build the cube successfully. say dim1 throws the errors, delete this. (This was just to test if other dimensions throw the same error. WARNING: Before making any changes DO keep a backup of erroneous cube file <yourcube>.cube).

4) After successful processing, I compared XML structure of dim2 in my Cube1 vs. the VCube. You do this by opening the VCube.cube and Cube1.cube in any text/XML editor. I discovered that <DimensioID> and <AttributeID> were same.

5) I then restored my erroneous cube defintion from the backup i had kept. So do that...

6) Now search for the erroneous dimension in VCube.cube XML and Verify that the <DimensionID> and <AttributeID> of the erroneous dimension in VCube is exactly same as in Cube1. For me this was the CULPRIT. While migrating my cubes, this relation had gotten corrupted.

7) Correct it if any inconsistency is found and save the XML VCube.cube file. Please remember, the changes must be done to VCube.cube ONLY.

8) Deploy, Process and VOILA!

If you have any doubts regarding this solution, feel free to drop me an email at sheetal.sharma@metacube.com

Hope this helps and saves you some time!

Cheers,

Sheetal
Left by Sheetal Sharma on May 27, 2009 12:07 AM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
Hi,

Is there already a Foodmart 2000 for SSAS 2008 or SSAS 2008 R2 ?

Marco
Left by Marco on Aug 27, 2009 7:06 PM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
I have not done anything specific for SSAS 2008, but the 2005 version should work.
Left by Darren Gosbell on Aug 27, 2009 8:48 PM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
thank you very much^^
Left by ameaiya on Jun 24, 2010 3:34 PM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
Hi

I have 2008 developer edition on my machine.

The source data in mdb file provided in the Cd

Please advice/help in downloading 2005 version

Left by Bhanu Singh on Aug 26, 2010 2:59 AM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
I need the version for Foodmart for sql server analysis servies 2008 R2.please prvide me the link if any.
Left by Triparna on May 06, 2011 6:51 PM

# re: Upgraded Foodmart 2000 sample database for Analysis Services 2005

Requesting Gravatar...
@Triparna - the 2005 backup files work fine with 2008 R2, there is no need for a separate version.
Left by Darren Gosbell on May 06, 2011 9:44 PM

Your comment:

 (will show your gravatar)