Report Portal

How to install Adventure Works SQL DW and Analysis Services 2005/2008 sample database and project

Written by Vidas Matelis. Posted in Management FAQ

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.

Tags: introduction, faq, management

 

2007-2015 VidasSoft Systems Inc.