How to install Adventure Works SQL DW and Analysis Services 2005/2008 sample database and project
User Rating: / 159
PoorBest 
Written by Vidas Matelis   
Wednesday, 05 September 2007 18:48

Q: How to install Adventure Works SQL DW database and Analysis Services 2005/2008 Sample database

A0: For Analysis Services 2008 - Adventure Works samples SR4 (SSAS 2005 and older SSAS 2008 sample release installation steps are below)

Latest samples for SQL Server 2008 (Relational database and Analysis Services database) can be downloaded from the codeplex website. Step by step guide below:

  • Go to website http://www.codeplex.com/MSFTDBProdSamples
  • Select "Downloads" tab.On the right side you will see list of releases. Select release SR4. Direct link to that release is: http://msftdbprodsamples.codeplex.com/releases/view/37109. Download file AdventureWorks2008_SR4.exe
  • Execute file AdventureWorks2008_SR4.exe. After accepting licence agreement you will get a "Database Selection" screen where you will be able to select databases that you would like to install on your machine.
  • Select instance where you would like to install databases and select databases that you would like to install on that instance.
  • Select location where you would like to install scripts. By default location is c:\Program Files\Microsoft SQL Server\100\Tools\Samples
  • Select databases that will be installed on your instance. At the very minimum you should select database "Adventure Works Data Warehouse 2008".

242-adventure-works-2008-sr4

  • Click "Install" button and wait till setup will finish.
  • Start SQL Server Management Studio 2008 (SSMS 2008), connect to your SQL Server instance and confirm that you have database "AdventureWorksDW2008" installed on your instance.
  • Start SQL Server Business Intelligence Development Studio (BIDS).
  • From the menu select "File"->"Open"->"Project/Solution"->navigate to the script folder (default c:\Program Files\Microsoft SQL Server\100\Tools\Samples) and then select sub-folders \AdventureWorks 2008 Analysis Services Project\enterprise and open file "Adventure Works.sln". This will open Adventure Works project.
  • If SQL Server Adventure Works DW database is installed not on the local machine, then after you open solution in BIDS (Business Intelligence Development Studio), double click on data source "Adventure Works.ds", click "Edit" on connection string and change Server name from localhost to your SQL Server name where Adventure Works DW database is installed.
  • In BIDS Choose menu option "Project" and then "Adventure Works DW Properties". In Deployment screen change Server name from localhost to name of server where Analysis Services database should be deployed.
  • Deploy project. This might take a few minutes.
  • Start SSMS 2008, connect to your SSAS (!) instance and confirm that there is "Adventure Works DW 2008" Analysis Services database installed on your SSAS instance.
  • Now you have Analysis Services sample database Adventure Works DW.

 

A1: For Analysis Services 2005 (for SSAS 2008 steps look at the steps bellow)

Latest samples for SQL Server 2005 (Relational database and Analysis Services database) can be downloaded from codeplex website. Step by step guide below:

  • Go to website http://www.codeplex.com/MSFTDBProdSamples
  • Select "Downloads" tab.
  • Most likely latest release will be related to SQL Server 2008 (katmai). To download SQL Server 2005 related release choose it from top right corner. At the time of writing this FAQ latest available sample release related to 2005 was "SQL Server 2005 SP2a". Direct link to that release is: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004
  • From the available list of files (you need *BI*.msi files) choose sample database installation file that most closely matches your requirement:
    • AdventureWorksBI.msi
    • AdventureWorksBICI.msi
    • AventureWorksBI_x64.msi
    • AdventureWorksBICI_x64.msi
    • AdventureWorksBI_IA64.msi
    • AventureWorksBICI_IA64.msi
  • Note: The CI versions of some of the databases use a case-insensitive collation. The others are case-sensitive. Choose *x64.msi files for installing on 64bit server.For my tests on 32bit machine I usually use AdventureWorksBICI.msi file.
  • During installation you will be asked for destination folder location. Default value is "c:\Program Files\Microsoft SQL Server\".
  • During installation following files will be copied into C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder: AdventureWorksDW_Data.mds and AdventureWorksDW_Log.ldf.
  • Most likely you will need to attach database based on files listed above. This can be easily done using SQL Server Management Studio attach database wizard, or by executing following SQL command:

USE [master]
GO
CREATE DATABASE [AdventureWorksDW] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Log.LDF' )
 FOR ATTACH
GO
if exists (select name from master.sys.databases sd where name = N'AdventureWorksDW' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [AdventureWorksDW].dbo.sp_changedbowner @loginame=N'OFFICE1\Vidas', @map=false
GO

  • After steps above you will have AdventureWorksDW database on your SQL Server.
  • Now you have to setup sample Analysis Services database. The same installation file that you downloaded above also installed Adventure Works Analysis Services Project in the folder "C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project". There you will find 2 sub-folders: "Enterprise" and "Standard". If you have SQL Server Enterprise or Developer edition, then use project from Enterprise sub-folder as it has features available just in SQL Server Enterprise Edition.
  • From Enterprise or Standard folder open solution project by clicking on "Adventure Works.sln" solution file. Tip for Windows Vista users: if you are doing installation from/on Windows Vista OS, I would recommend that you copy sample project out of "c:\Program Files" folder. In Windows Vista this folder has extra security and you might not be able to save changes in project files as files will be read-only.
  • If SQL Server Adventure Works DW database is installed not on the local machine, then after you open solution in BIDS (Business Intelligence Development Studio), double click on data source "Adventure Works.ds", click "Edit" on connection string and change Server name from localhost to your SQL Server name where Adventure Works DW database is installed.
  • In BIDS Choose menu option "Project" and then "Adventure Works DW Properties". In Deployment screen change Server name from localhost to name of server where Analysis Services database should be deployed.
  • Deploy project. This might take few minutes.
  • Now you have Analysis Services sample database Adventure Works DW.

A2: For Analysis Services 2008 (Sample Refresh 1). Steps if on the server you have full text search enabled:

SQL2008.AdventureWorks_All_Databases.x86.msi
SQL2008.AdventureWorks_All_Databases.x64.msi
SQL2008.AdventureWorks_All_Databases.ia64.msi

  • Start msi file. Accept licence agreement. In the "Custom Setup" screen leave default selections. In the next "Database Setup" screen choose database instance where you would like to restore SQL Server database. Note - it is hard to see that you have option to select database from the drop down list, so please be careful. In the "Ready to install" screen choose "Install".

  • If during installation you get error message: "PrepInstance() failed for MSSQLSERVER. The following features are missing: Full Text Search. Fix the problems and re-run setup.", you should use different steps described below (A3).

  • Now you have to setup sample Analysis Services database. The same installation file that you downloaded above also installed Adventure Works Analysis Services Project in the folder "C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project". There you will find 2 sub-folders: "Enterprise" and "Standard". If you have SQL Server Enterprise or Developer edition, then use project from Enterprise sub-folder as it has features available just in SQL Server Enterprise Edition.

  • From Enterprise or Standard folder open solution project by clicking on "Adventure Works.sln" solution file. Tip for Windows Vista users: if you are doing installation from/on Windows Vista OS, I would recommend that you copy sample project out of "c:\Program Files" folder. In Windows Vista this folder has extra security and you might not be able to save changes in project files as files will be read-only.

  • If SQL Server Adventure Works DW database is installed not on the local machine, then after you open solution in BIDS (Business Intelligence Development Studio), double click on data source "Adventure Works.ds", click "Edit" on connection string and change Server name from localhost to your SQL Server name where Adventure Works DW database is installed.
  • In BIDS Choose menu option "Project" and then "Adventure Works DW Properties". In Deployment screen change Server name from localhost to name of server where Analysis Services database should be deployed.
  • Deploy project. This might take few minutes.
  • Now you have Analysis Services sample database "Adventure Works DW 2008".

A3: For Analysis Services 2008 (Sample Refresh 1). Steps if on the server you DO NOT have full text search enabled:

  • Note: Use this manual steps just if you do not want to enable full text search (!).
  • Follow these steps just when you cannot enable full text search. To enable full text search - check the list of services if "SQL Full-text Filter Daemon Launcher" service is available. If yes, start it
  • Go to website http://www.codeplex.com/MSFTDBProdSamples
  • Select "Downloads" tab. At the time of publishing this article latest release was for SQL Server 2008 RTM and direct link to this release is: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=18407
  • If you do not have full text search, you should download file SQL2008.AdventureWorks_All_Databases.zip. Unzip content of the file to your folder, for example C:\Utilities\Install\SQL2008.AdventureWorks_DW_BI_v2008. After unzipping you will see 2 folders: "Samples" and "Tools". Copy "Tools" folder into your default SQL Server location: "C:\Program Files\Microsoft SQL Server\100\".
  • Start SQL Server Management Studio and open file C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Data Warehouse\instawdwdb.sql. Find lines:

--:setvar SqlSamplesDatabasePath   "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\"
--:setvar SqlSamplesSourceDataPath "C:\Program Files\Microsoft SQL Server\100\Tools\Samples\"

  • Remove comments (first two "-" characters from each line) from the above lines and adjust values if your file path is different. Save file. Default these lines will be:

:setvar SqlSamplesDatabasePath   "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\"
:setvar SqlSamplesSourceDataPath "C:\Program Files\Microsoft SQL Server\100\Tools\Samples\"

  • Load that script into SQL Server Management Studio (SSMS).
  • In SSMS choose menu "Query" and then choose "SQLCMD mode"
  • Execute this script. After that in your SQL Server you will have database "AdventureWorksDW2008" relational database.
  • Now you have to setup sample Analysis Services database. The same installation file that you downloaded above also installed Adventure Works Analysis Services Project in the folder "C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project". There you will find 2 sub-folders: "Enterprise" and "Standard". If you have SQL Server Enterprise or Developer edition, then use project from Enterprise sub-folder as it has features available just in SQL Server Enterprise Edition.
  • From Enterprise or Standard folder open solution project by clicking on "Adventure Works.sln" solution file. 
  • If SQL Server Adventure Works DW database is installed not on the local machine, then after you open solution in BIDS (Business Intelligence Development Studio), double click on data source "Adventure Works.ds", click "Edit" on connection string and change Server name from localhost to your SQL Server name where Adventure Works DW database is installed. Selected database there will be "AdventureWorksDW2008"
  • In BIDS Choose menu option "Project" and then "Properties". In Deployment screen change Server name from localhost to name of server where Analysis Services database should be deployed.
  • Deploy project. This might take few minutes.
  • Now you have Analysis Services sample database "Adventure Works DW 2008".

A4: For Analysis Services 2008 (Sample Refresh 1). Steps if on the server you DO NOT have full text search enabled - option to setup 2005 samples:

  • If for any reasons you cannot enable full text search, then you can also install SQL Server 2005 Adventure Works database on SQL Server 2008 instance. Majority of beginners will not notice any difference between 2005 and 2008 samples. To install SQL Server 2005 samples on the SQL Server 2008 instance follow above steps "A1: For Analysis Services 2005". When you will attach SQL server 2005 database, it will be automatically migrated to the SQL server 2008 version. Also, when you will open Adventure Works SSAS solution, you will be offered to migrate that solution to the Visual Studio 2008 version. For SSAS solution you will have to change connection provider to "SQL Server Native Client 10.0".

If you are getting error 2738 while installing SQL Server 2008 Adventure Works sample database on Windows Vista OS, read this.

 
Comments (39)
39 Saturday, 26 July 2014 01:31
Aarti Parmar
I wnt 2 install adventureworks DW for SQL server 2005 on my win 7 (64- bit) lapi. Bt I CNT found d exact link 2 d/l d perfect setup. Can u plz help me out !
38 Tuesday, 25 June 2013 07:35
Alireza
Very nice step by step instructions. So helpful.
37 Friday, 03 February 2012 00:49
Amol mulik
Thanks yar
36 Thursday, 02 February 2012 16:30
adrshen
Thanks a lot , pretty straight forward and well documented...Nice work
35 Thursday, 20 October 2011 09:44
Eduardo
I can´t download Adventure Works DW Refresh. The link below is down:

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=18407

When can i found this package ???

Thanks
34 Thursday, 06 October 2011 18:11
Mark Ma
Very patient and careful advices
33 Sunday, 28 August 2011 13:59
mahesh pardeshi
Thanks yar u help me
32 Friday, 22 July 2011 09:26
Prashant B
great job .... keep it up :)
31 Tuesday, 07 June 2011 13:21
Jon R
Hugely helpful and clear, well thought out.
Microsoft's technical writers could learn a thing or two from tutorials with this degree of accuracy and clarity. Good work :D
Jon.
30 Thursday, 05 May 2011 18:13
Sujeet
Thanks for the instruction...i was able to install this sample database on my machine.....thanks so much......
29 Tuesday, 15 February 2011 11:40
Dinesh
Thanks a lot!!
28 Thursday, 10 February 2011 02:14
Roger Ingal
Very helpful. Thank you.
27 Tuesday, 07 December 2010 03:05
sally
Marvellous. Went round in circles trying to find where these files were...and found them here...everything installed perfectly! Thank-you :-)
26 Friday, 10 September 2010 04:44
Douglas Mui
Thanks for the install code. Work perfectly!! Managed to attach the database and it's okay.

Mam Kande
25 Monday, 09 August 2010 19:02
Diego
Very helpful, thank you!
24 Saturday, 07 August 2010 10:29
PRATIK
i have sql 2008 and i had downloaded adventureworks database file.....i've installed it ....but when i execute the installdb in ssms,i get the following error;

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)


Started - 2004-01-01 06:08:32.263

*** Dropping Database

*** Enabling FileStream
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'filestream_access_level' does not exist, or it may be an advanced option.
** An error was encountered during execution of batch. Exiting.


plz help...thanks in advance
23 Thursday, 15 July 2010 09:57
Meghana
It is really very helpful......Thanks a lot :)
22 Saturday, 27 March 2010 17:56
shekhar
thnx for the adventure works
was looking it for ling time
21 Friday, 26 March 2010 13:51
Prabhakar Thatikonda
Really very good
20 Wednesday, 24 February 2010 22:33
Paulo Nayan
Thank you for all instrutions. It was great.
19 Tuesday, 23 February 2010 19:46
bhakthil
Very helpful........
18 Tuesday, 06 October 2009 11:01
Edgar
Great instructions! Thanks. One addition though...

On my Windows 7 x64 laptop had to change the impersonation information on the data source to my own user id/password to be able to deploy the project.
17 Saturday, 03 October 2009 08:13
Bharath
Great article. Thanks a lot
16 Tuesday, 29 September 2009 14:01
Olu
Thank you sooooo much. I wish I'd seen your site first. I've wasted precious time on other sites with too much data and not useful info.
15 Sunday, 27 September 2009 04:39
James King
Thanks for your time and effort mate, saved me at least a couple of hours work.
14 Wednesday, 16 September 2009 19:18
Daniel Upton
Perfecto!
13 Tuesday, 25 August 2009 02:07
rk1
Great article! Thanks much.
12 Tuesday, 16 June 2009 02:49
Vidas M.
Sorry, source files were changed and that means this page needs update. I'll do that in the next few days. In the mean time - if you can install full text search, that would be the easies solution. This can be done in "Control Pannel", "Programs and Features..." section select SQL Server 2008. If Full text search installation is not an option, then first workaround that comes to my mind - setup SQL Server 2005 samples. They will work on 2005 version without problems. Steps would be - download 2005 samples, install them to some temp folder c:\temp. Then move mdf and ldf file from c:\temp\MSSQL.1\MSSQL\Data to C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA. Probably you will need to give yourself full permissions on these files and then attach SQL Server database. Then open SSAS project from the folder C:\temp\90\Tools\Samples\AdventureWorks Analysis Services Project\enterprise and deploy...
11 Monday, 15 June 2009 21:30
kathy
I had the same problem as the previous commentor. I was following the instructions for A3 (do not have full text search enabled). I did not find RestoreAdventureWorksDW2008.sql in the mentioned folder.
10 Monday, 15 June 2009 13:24
anon
I'm trying to install on SSMS2008 without full text search enabled. And there is no file named RestoreAdventureWorksDW2008.sql.
in the ../tools/samples folder

any ideas?
9 Thursday, 04 June 2009 10:11
Srikanth
Thanks very much boss
8 Saturday, 21 March 2009 18:28
sinoj
very comprehensive and easy to understand!!!
7 Saturday, 28 February 2009 22:42
Андрей
да уж спасибо так спасибо
6 Monday, 09 February 2009 13:13
Kate
This saved me time, many thanks
5 Monday, 02 February 2009 07:06
nita
very helpful thanks
4 Friday, 19 December 2008 04:45
Anoop
This is very Useful for the beginners Who wish to learn Analysis Services.
3 Tuesday, 04 November 2008 11:08
TomVdP
Thank you, very helpful.
2 Tuesday, 09 September 2008 16:23
Dmitriy
I've always been baffled as to the correct location of sample SSAS projects. They seemed to go into the proverbial bit bucket.I could never find them.
This article finally shed some light on the elusive location of aforomentioned source files.
1 Wednesday, 23 July 2008 21:51
Lucy
Very helpful

 

Pyramid Analytics