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.


Feedback

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

Hi Darren,
thanks for the file. Will give it a go, bearing in mind your comments on the differences with SSAS 2005.
Cheers,
Donovan. 4/3/2006 5:27 PM | Donovan Hide

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

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 5/22/2007 8:31 AM | RICHARD GARTH

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

It will play an important role for Data Warehousing people 12/8/2007 9:08 PM | Md. Mizanur Rahman

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

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
1/8/2008 3:42 AM | Marty Wilson

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

Marty,

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

Darren 1/8/2008 8:59 AM | Darren Gosbell

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

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 4/9/2008 4:24 AM | R.Smith

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

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. 4/9/2008 6:38 AM | Darren Gosbell

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

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

2/5/2009 8:09 PM | Christian

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

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 2/5/2009 10:51 PM | Darren Gosbell

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

Darren,

Thanks a lot for the quick response!

Christian 2/6/2009 12:04 AM | Christian

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

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 5/27/2009 12:07 AM | Sheetal Sharma

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

Hi,

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

Marco 8/27/2009 7:06 PM | Marco

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

I have not done anything specific for SSAS 2008, but the 2005 version should work. 8/27/2009 8:48 PM | Darren Gosbell

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

thank you very much^^ 6/24/2010 3:34 PM | ameaiya

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

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

8/26/2010 2:59 AM | Bhanu Singh

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

I need the version for Foodmart for sql server analysis servies 2008 R2.please prvide me the link if any. 5/6/2011 6:51 PM | Triparna

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

@Triparna - the 2005 backup files work fine with 2008 R2, there is no need for a separate version. 5/6/2011 9:44 PM | Darren Gosbell

Post a comment





 

 

News

About Me
I am a consultant, based in Melbourne Australia. I primarily work in the Business Intelligence area with SQL Server, although I also dabble in Content Management Server and .Net coding

Contact Me

MVP


Legal
Any and all code, software, examples, suggestions and anything else on this web site is available for you to use at your own risk. No warranty is expressed or implied.
Views and Opinions
The views and opinions expressed on this web site are not necessarily the views or opinions of my employer.

Subscribe in Bloglines Subscribe in NewsGator Online

Locations of visitors to this page

Twitter












Tag Cloud


Article Categories

Archives

Post Categories

.Net Blogs

Aussie Bloggers

BI Blogs

CMS Blogs

Syndication: