Jun 27, 2012 Adventureworks sample database launched with SQL Server 2012 and you can download it from the codeplex. Given below are the links: Adventureworks2012 data file Adventureworks2012 case sensitive date file After downloading the appropriate data files lets proceed with the installation: Installation via T-SQL: Step 1: You should copy the data file to. I need to install AdventureWorksLT2012 database but I don't need the.exe installer instead I need the script file so I can copy & paste it to SSMS 2012 and run it from it so it creates it. Is there a way to download this script from a website?
-->Use the instructions and links provided in this topic to install all of the data and project files used in the Analysis Services Tutorials.
Step 1: Install SQL Server Software
The lessons in this tutorial assume that you have the following software installed. All of the following software is installed using SQL Server installation media. For simplicity of deployment, you can install all of the features on a single computer. To install these features, run SQL Server Setup and select them from the Feature Selection page. For more information, see Install SQL Server 2014 from the Installation Wizard (Setup).
Database Engine
Analysis Services
Analysis Services is available in these editions only: Evaluation, Enterprise, Business Intelligence, Standard.
Note that SQL Server Express editions do not include Analysis Services. Download the Evaluation edition if you want to try out the software free of charge.
By default, Analysis Services is installed as a multidimensional instance, which you can override by choosing Tabular Server Mode in the server configuration page of the Installation Wizard. If you want to run both server modes, rerun SQL Server Setup on the same computer to install a second instance of Analysis Services in the other mode.
SQL Server Management Studio
Optionally, consider installing Excel to browse your multidimensional data as you proceed through the tutorial. Installing Excel enables the Analyze in Excel feature that starts Excel using a PivotTable field list that is connected to the cube you are building. Using Excel to browse data is recommended because you can quickly build a pivot report that lets you interact with the data.
Alternatively, you can browse data using the built-in MDX query designer that is built into SQL Server Data Tools (SSDT). The query designer returns the same data, except the data is presented as a flat rowset.
Step 2: Download SQL Server Data Tools - Business Intelligence for Visual Studio 2012
In this release, SQL Server Data Tools is downloaded and installed separately from other SQL Server features. The designers and project templates used to create BI models and reports are now available as a free web download.
Download the Business Intelligence version of SQL Server Data Tools. The file is saved to the Downloads folder. Run setup to install the tool.
Reboot the computer to complete the installation.
Step 3: Install Databases
An Analysis Services multidimensional model uses transactional data that you import from a relational database management system. For the purposes of this tutorial, you will use the following relational database as your data source.
AdventureWorksDW2012 - This is a relational data warehouse that runs on a Database Engine instance. It provides the original data that will be used by the Analysis Services databases and projects that you build and deploy throughout the tutorial.
You can use this sample database with SQL Server 2014 as well as SQL Server 2012.
To install this database, do the following:
Download the AdventureWorkDW2012 database from the product samples page on codeplex.
The database file name is AdvntureWorksDW2012_Data.mdf. The file should be in the Downloads folder on your computer.
Copy the AdventureWorksDW2012_Data.mdf file to the data directory of the local SQL Server Database Engine instance. By default, it is located at C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLData.
Start SQL Server Management Studio and connect to the Database Engine instance.
Right-click Databases, click Attach.
Click Add.
Select the AdventureWorksDW2012_Data.mdf database file and click OK. If the file is not listed, check the C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLData folder to be sure the file is there.
In database details, remove the Log file entry. The setup program assumes you have a log file, but there is no log file in the sample. A new log file will be created automatically when you attach the database. Select the log file and click Remove, and then click OK to attach just the primary database file.
Step 4: Grant Database Permissions
The sample projects use data source impersonation settings that specify the security context under which data is imported or processed. By default, the impersonation settings specify the Analysis Services service account for accessing the data. To use this default setting, you must ensure that the service account under which Analysis Services runs has data reader permissions on the AdventureWorksDW2012 database.
Note
For learning purposes, it is recommended that you use the default service account impersonation option and grant data reader permissions to the service account in SQL Server. Although other impersonation options are available, not all of them are suitable for processing operations. Specifically, the option for using the credentials of the current user is not supported for processing.
Download Adventureworks 2012 Data File
Determine the service account. You can use SQL Server Configuration Manager or the Services console application to view account information. If you installed Analysis Services as the default instance, using the default account, the service is running as NT ServiceMSSQLServerOLAPService.
In Management Studio, connect to the database engine instance.
Expand the Security folder, right-click Logins and select New Login.
On the General page, in Login name, type NT ServiceMSSQLServerOLAPService (or whatever account the service is running as).
Click User Mapping.
Select the checkbox next to the AdventureWorksDW2012 database. Role membership should automatically include db_datareader and public. Click OK to accept the defaults.
Step 5: Install Projects
The tutorial includes sample projects so that you can compare your results against a finished project, or start a lesson that is further on in the sequence.
The project file for Lesson 4 is particularly important because it provides the basis for not only that lesson, but all the subsequent lessons that follow. In contrast with the previous project files, where the steps in the tutorial result in an exact copy of the completed project files, the Lesson 4 sample project includes new model information that is not found in the model you built in lessons 1 through 3. Lesson 4 assumes that you are starting with a sample project file that is available in the following download.
Download the Analysis Services Tutorial SQL Server 2012 on the product samples page on codeplex.
The 2012 tutorials are valid for the SQL Server 2014 release.
The 'Analysis Services Tutorial SQL Server 2012.zip' file will be saved to the Downloads folder on your computer.
Move the .zip file to a folder just below the root drive (for example, C:Tutorial). This step mitigates the 'Path too long' error that sometimes occurs if you attempt to unzip the files in the Downloads folder.
Unzip the sample projects: right-click on the file and select Extract All. After you extract the files, you should have the following projects installed on your computer:
Lesson 1 Complete
Lesson 2 Complete
Lesson 3 Complete
Lesson 4 Complete
Lesson 4 Start
Lesson 5 Complete
Lesson 6 Complete
Lesson 7 Complete
Lesson 8 Complete
Lesson 9 Complete
Lesson 10 Complete
Remove the read-only permissions on these files. Right-click the parent folder, 'Analysis Services Tutorial SQL Server 2012', select Properties, and clear the checkbox for Read-only. Click OK. Apply the changes to this folder, subfolders, and files.
Start SQL Server Data Tools (SSDT).
Open the solution (.sln) file that corresponds to the lesson you are using. For example, in the folder named 'Lesson 1 Complete', you would open the Analysis Services Tutorial.sln file.
Deploy the solution to verify that database permissions and server location information is set up correctly.
If Analysis Services and the Database Engine are installed as the default instance (MSSQLServer) and all software is running on the same computer, you can click Deploy Solution on the Build menu to build and deploy the sample project to the local Analysis Services instance. During deployment, data will be processed (or imported) from the AdventureWorksDW2012 database on the local Database Engine instance. A new Analysis Services database will be created on the Analysis Services instance that contains the data retrieved from the Database Engine.
If you encounter errors, review the previous steps on setting up database permissions. Additionally, you might also need to change server names. The default server name is localhost. If your servers are installed on remote computers or as named instances, you must override the default to use a server name that is valid for your installation. Furthermore, if the servers are on remote computers, you might need to configure Windows Firewall to allow access to the servers.
The server name for connecting to the database engine is specified in the Data Source object of the multidimensional solution (Adventure Works Tutorial), visible in Solution Explorer.
The server name for connecting to Analysis Services is specified in the Deployment tab of the Property Pages of the project, also visible in Solution Explorer.
Start SQL Server Management Studio. In SQL Server Management Studio, connect to Analysis Services. Verify that a database named Analysis Services Tutorial is running on the server.
Next Step
You are now ready to use the tutorial. For more information about how to get started, see Multidimensional Modeling (Adventure Works Tutorial).
See Also
Install SQL Server 2014 from the Installation Wizard (Setup)
Configure the Windows Firewall to Allow Analysis Services Access
Configure the Windows Firewall to Allow SQL Server Access
The AdventureWorks database is a fictitious company database that has existed since SQL Server 2005 as a means to show new functionality in each new version released. Because the Books Online TSQL samples leverage this sample database, AdventureWorks has become a vital aspect of learning new features/constructs within SQL Server. What started as a big improvement over the Northwind and Pubs sample databases has evolved into numerous specialized off-shoots highlighting different aspects of the product. There are now multiple versions of AdventureWorks databases for each version of the product, including many updates corresponding to service pack releases. The end result is that it is extremely confusing to find the 'correct' version of AdventureWorks that you are searching for. I'll lovingly refer to this as AdventureWorks sprawl and try to lower the barrier to entry for getting started working with the AdventureWorks sample database.
Pre-SQL Server 2005:
AdventureWorks did not exist before SQL Server 2005. In this time, the sample databases Northwind and Pubs were included on the media along with the product and could be installed during the setup process. You can download the Northwind and Pubs sample databases for SQL Server 2000 here.
SQL Server 2005:
In the SQL Server 2005 timeframe, the AdventureWorks sample DB was introduced and also included on the media along with the product. You can install the samples during setup or download them from the following location:
The installers for SQL Server 2005 supply you with the .mdf/.ldf files, but you need to manually attach them to your instance for use. You will see a couple different AdventureWorks databases, each with a slightly different focus. For the majority of people downloading sample databases to learn T-SQL, refer to Books Online examples, etc., the AdventureWorksDB is the first one to start with.
SQL Server 2008:
Beginning with SQL Server 2008, the sample databases are no longer included with the media and must be downloaded and attached as a separate step. The SQL Server 2008 sample databases are packaged slightly different than any other version, where they include a single .exe file that will allow you to install all different versions of the SQL Server 2008 sample databases. They also include a .zip file containing a single .mdf and .ldf for attaching the basic AdventureWorksLT database. I'd suggest unzipping this file and attaching the .mdf/.ldf to get started and using the .exe if you want to dig further into more complex examples.
Step-by-step instructions for using the .exe to install all sample DBs is here.
Sql Server 2012 Sample Databases Download
SQL Server 2008 R2:
Beginning with the 2008 R2 sample databases, a new version was introduced that supported filestream. If you are familiar with SQL Server and trying to learn filestream specifically, you'll want to download the version containing filestream sample schema/code. For the huge bulk of users trying to leverage the sample database to learn the basics and extend into new areas, you will want to download the version without filestream support from the link below:
In this link, you will see numerous versions of the database. To get started quickly, I'd suggest downloading AdventureWorks2008R2-Full Database Backup.zip, unzipping the .bak file to your backup location, and restoring the database through SSMS. The other versions all have different intentions for learning, but again I'd suggest starting with the basic version first before advancing to the other versions.
SQL Server 2012:
Through the evolution of the AdventureWorks DBs over the years, the site hosting the sample databases now includes 13 different links/flavors/versions of the sample database for SQL Server 2012 and can be found here:
As with my suggestion for 2008 R2, I would suggest that you download AdventureWorks2012-Full Database Backup.zip, unzip the .bak file to your backup location, and restore the database through SSMS to get started quickly.
SQL Server 2014:
Following the same pattern as with 2008 R2 and 2012, you can download Adventure Works 2014 Full Database Backup.zip, unzip the .bak file to your backup location, and restore the database through SSMS to get started quickly.
In summary, the AdventureWorks sample databases are outstanding learning tools for understanding examples from Books Online, trying out new constructs, and exploring entire new areas of SQL Server, but wading through the numerous versions and editions can be quite confusing. If you have questions/comments, let me know.
Hope this helps,
Sam Lester (MSFT)