SSAS-Info.com

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

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 "Releases" 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:

• Go to website http://www.codeplex.com/MSFTDBProdSamples
• Select Releases 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=16040
• From the available list of files (you need *BI*.msi files) choose sample database installation file that most closely matches your requirement:

SQL2008.AdventureWorks_DW_BI_v2008.x86.msi
SQL2008.AdventureWorks_DW_BI_v2005.x86.msi
SQL2008.AdventureWorks_DW_BI_v2008.x64.msi
SQL2008.AdventureWorks_DW_BI_v2005.x64.msi
SQL2008.AdventureWorks_DW_BI_v2008.ia64.msi
SQL2008.AdventureWorks_DW_BI_v2005.ia64.msi
SQL2008.AdventureWorks_DW_BI_v2008.zip
SQL2008.AdventureWorks_DW_BI_v2005.zip

• My recommendation would be to ignore *v2005* files, and use just *v2008* files as these files contains demos with 2008 specific features. For 32bit environment I would use file SQL2008.AdventureWorks_DW_BI_v2008.x86.msi and for 64 bit environment I would use SQL2008.AdventureWorks_DW_BI_v2008.x64.msi or SQL2008.AdventureWorks_DW_BI_v2008.ia64.msi depending on CPU architechcure. *.msi file is installation package that will do installation for you. *.zip file contains OLAP solution files (enterprise and standard) and SQL Server database backup. There is also example of sql script how to restore SQL Server Adventure Works database. Following steps assume that you will use *.msi file to install Adventure Works DW Database.
• Start *.msi file that you just downloaded. You will be asked to accept licence agreement.
• In the next screen you will have to select installation options and location. Make sure that following options are checked as "Entire feature will be installed on local hard drive": "Sample Files", "Program Menu Shortcuts" and "Restore Adventure Works DBs" . Note - last option is not selected by default. Also, make sure that file installation location is acceptable to you. Default location is "c:\Program Files\Microsoft SQL Server\100\". Click next.
• In this screen you will be asked to select local database instance for Adventure Works. Click Next and then click "Install".
• After few minutes installation will complete. Now on your SQL Server you will have new database AdventureWorksDW2008. Note - at this point you still dont have SSAS Sample database!
• Installation created folder "C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project" where it placed enterprise and standard edition of SQL Server Analysis Services solution. If you have enterprise or developer edition of SQL Server, then use solution from "Enterprise" subfolder, otherwise use solution from "Standard" folder.
• 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.

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