The SQL Server 2012 samples provide a variety of databases and files that you can use to learn about new and existing features.
This article is the consolidated Readme for each sample in the SQL Server 2012 release. It provides prerequisite information, installation instructions, and links to the download location of each sample. This article will be updated periodically. Be sure to visit again.
The following table lists all Microsoft SQL Server 2012 samples, by technology.
(relational database)
Download AdventureWorks2012 Data File from Samples.
Download AdventureWorksLT2012 Data File from Samples.
Download AdventureWorksDW2012 Data File from Samples.
Download AsdventureWorks Multidimensional Models SQL Server 2012 from Samples.
Analysis Services Tutorial on Multidimensional Modeling
Download Analysis Services Tutorial SQL Server 2012 from Samples
Tabular models
Created in SQL Server Data Tools
Download AdventureWorks Tabular Model SQL Server 2012 from Samples.
Created in C# using AMO
Download from Tabular AMO 2012.
Power View reports
Model created in PowerPivot for Excel
Download from Power View and PowerPivot Samples.
workbook
Download from Sample Images.
Adventure Works Internet Sales solution
Download from Samples.,mn n m
↑ Back to top
Adventure Works Cycles publishes product and sales information that is accessed by employees throughout the company. The IT department manages the OLTP database and the OLAP data warehouse that stores the sales and manufacturing data. Data modelers prepare multidimensional and tabular models for self-service business intelligence for end users. Data analysts from different disciplines use client tools to investigate, analyze, and visualize data based on either the IT managed data sources or data models. To share their findings with colleagues and other teams, they publish files to a SharePoint site.
End users view and interact with data visualizations by using different reporting clients. Each reporting client varies in support for sources of data.
In this release, sample reports are included only for the first case.
Your system must meet the following requirements to use the samples.
To create models, your system needs one or both of the following applications.
For information about Power View requirements, see System requirements for Power View.
Download and install the SQL Server 2012 version of the Adventure Works OLTP database and AdventureWorksDW data warehouse file.
The AdventureWorks2012 database has two versions:
You can also download a DDL script and .csv files to install the database from script. Download AdventureWorks 2012 OLTP Script.zip to create the case-insensitive database or AdventureWorks 2012 CS OLTP Script.zip for the case-sensitive database.
The AdventureWorks2012 OLTP database can be installed using the CREATE DATABASE statement or by attaching the database.
To install AdventureWorks2012 OLTP database
Case-insensitive Database
CREATE DATABASE AdventureWorks2012 ON (FILENAME = '{drive}:\{file path}\AdventureWorks2012_Data.mdf') FOR ATTACH_REBUILD_LOG;
Case-sensitive Database
CREATE DATABASE AdventureWorks2012_CS ON (FILENAME = '{drive}:\{file path}\AdventureWorks2012_CS_Data.mdf') FOR ATTACH_REBUILD_LOG;
As an alternative to step 4, you can attach the database using the SQL Server Management Studio user interface. For more detailed information, see Attach a Database (SQL Server Management Studio).
The AdventureWorks database can be created by running the instawdb.sql DDL script contained in the AdventureWorks 2012 OLTP Script.zip or AdventureWorks 2012 CS OLTP Script.zip file. The instawdb.sql script depends on two path environment variables: SqlSamplesDatabasePath and SqlSamplesSourceDataPath. The SqlSamplesDatabasePath environment variable is set to the default Microsoft ® SQL Server 2012 path. The SqlSamplesSourceDataPath environment variable is set to the default script CSV data path. An invalid SqlSamplesSourceDataPath path generates an error that the bulk load steps failed because the .csv files could not be found. The DDL script must be ran in SQLCMD mode inside SQL Server Management Studio.
To install AdventureWorks2012 OLTP database from script
Note The default SQL Server 2012 64-bit path is C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\. Use C:\Program Files (x86)\... for 32-bit SQL Server 2012.
The AdventureWorksLT2012 OLTP database can be installed using the CREATE DATABASE statement or by attaching the database.
To install AdventureWorksLT2012 OLTP database
CREATE DATABASE AdventureWorksLT2012 ON (FILENAME = '{drive}:\{file path}\AdventureWorksLT2012_Data.mdf') FOR ATTACH_REBUILD_LOG;
AdventureWorksLT2012 is a less complex sample database as compared to the enterprise-grade AdventureWorks2012 sample database. AdventureWorksLT2012 is dependent on AdventureWorks2012. See Readme for Adventure Works Sample Databases for detailed instructions on how to install AdventureWorks2012.
The AdventureWorksLT2012 database can be created by running the instawltdb.sql DDL script contained in the AdventureWorks LT 2012 OLTP Script.zip file. The instawltdb.sql script depends on two path environment variables: SqlSamplesDatabasePath and SqlSamplesSourceDataPath. The SqlSamplesDatabasePath environment variable is set to the default Microsoft ® SQL Server 2012 path. The SqlSamplesSourceDataPath environment variable is set to the default script CSV data path. An invalid SqlSamplesSourceDataPath path generates an error that the bulk load steps failed because the .csv files could not be found. The DDL script must be ran in SQLCMD mode inside SQL Server Management Studio.
To install AdventureWorksLT2012 OLTP database from script
{{ CREATE DATABASE AdventureWorksDW2012 ON (FILENAME = '<drive>:\<file path>\AdventureWorksDW2012_Data.mdf') FOR ATTACH_REBUILD_LOG ;}}
{{ CREATE DATABASE AdventureWorksDW2012
This notice informs you of changes to the Adventure Works DW database in the SQL Server 2012 final product release. If you use the Adventure Works DW database for testing or instructional purposes, review the following change list so that you update any materials that are based on this data source.
Download and install the SQL Server 2008R2 version of the Adventure Works OLTP database and AdventureWorksDW data warehouse file.
The AdventureWorks2008R2 OLTP database can be installed using the CREATE DATABASE statement or by attaching the database.
To install AdventureWorks2008R2 OLTP database
CREATE DATABASE AdventureWorks2008R2 ON (FILENAME = '{drive}:\{file path}\AdventureWorks2008R2_Data.mdf') FOR ATTACH_REBUILD_LOG;
Note The default SQL Server 2008 R2 64-bit path is C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\. Use C:\Program Files (x86)\... for 32-bit SQL Server 2012.
This sample includes 11 sample projects that accompany the Analysis Services multidimensional modeling tutorial. The projects show a completed, working example of the solution you would have created if you were following the lessons in the tutorial. You can use a completed project to compare against your work, or to jump ahead in the tutorial. For example, if you want to go directly to Lesson 5, you can start with Lesson 4 – completed.
This sample also includes a startup project for Lesson 4. Lesson 4 includes extra dimensions, attributes, and relationships that you would not have created in Lesson 3. The extra objects are added to save you from having to repeat steps that you already learned. Lesson 4 assumes you are starting with the extra objects provided in a sample project that is part of this download.
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 on the Project 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.
If you encounter errors, review the previous steps on setting up database permissions. You might also need to change server names, for example, an instance name if you installed either Analysis Services or the Database Engine as a named instance. Additionally, if the servers are on a different server, you might need to configure Windows Firewall to allow access to the servers.
Using the sample projects requires that you install the following features using SQL Server 2012 installation media:
Optionally, consider installing Excel. 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 use the built-in MDX query designer. The query designer returns the same data, except the data is presented as a flat rowset.
The projects in this sample demonstrate how to deploy an Analysis Services multidimensional model in SQL Server Data Tools.
This sample contains projects used to deploy an Analysis Services multidimensional database based on AdventureWorks sample data. There are two versions of this project for each of the enterprise and standard editions of SQL Server. If you are using the business intelligence edition of SQL Server, use the enterprise project to deploy the sample database.
Click OK to create an empty database.
Watch the deployment progress until it comes to a successful end.
Requirements to install and deploy AdventureWorksDW2012Multidimensional sample database and project:
Analysis Services Multidimensional capabilities.
This sample contains project to build the tabular model of Adventure Works DW Tabular SQL 2012. Download SSAS Tabular Model Projects SQL 2012 from Samples.
Test and validate the samples
If you don't see the database, this might be because the database is not installed in the server or you don't have privileges to access the database; contact your DBA to help you to gain access to the database.
If the select statement successfully returns rows you should have all privileges needed to reload data in your workbook.
If no rows are returned or an error occurs contact your DBA to obtain help.
If you got an error, it probably says something like the following text: The connected user is not an Analysis Services administrator.. Ask your DBA to give you administrator permissions.
If it doesn't open BIDS then the feature wasn't correctly installed; you need to go over the setup process and add the BIDS feature to your installation.
A warning dialog, titled Business Intelligence Semantic Model, appears with the following message Opening this file in the BIM Designer causes a script to be executed on an Analysis Services server..., click Yes to accept; accepting sends the model to your workspace server, generates the model there and presents the model to you in BIDS. Wait a moment until the model appears in the window.
Click on Edit and verify the data source information points the AdventureWorksDWDenali database verified in step 1. Adjust as needed; click on Test Connection to verify all parameters are correct. Click on the Impersonation... button and verify Service Account is selected. This means the Analysis Services service account will be impersonated when reading data from the AdventureWorksDWDenali database (during data refresh in step 8 below). Make sure the service account has at least db_datareader permissions to the AdventureWorksDWDenali relational database otherwise you will get an error when you try to refresh the data. Click on Save and Close in the next window to return to the model.
Click OK to close the properties window.
Select Non Empty [Date].[Calendar Year].members on rows, Non Empty [Product Category].[Product Category Name].members on columns from [Model] where [Measures].[Total Sales]
Requirements to install and deploy Adventure Works DW Tabular Denali sample database and project:
Analysis Services Tabular capabilities.
Tabular AMO 2012 is about creating and managing tabular models using the AMO api. It is a developer’s sample, for those interested in managing Analysis Services.
The sample is made of two project parts. The first part is a library of functions to manage tabular models -AMO2Tabular V2-. The second part is a sample to build a tabular model -AdventureWorks Tabular AMO 2012- using the AMO2Tabular library; the created model is similar to the 'AdventureWorks Tabular Model 2012', mentioned earlier in this set of documents.
The intentions around the AMO2Tabular library were to provide the most complete guide on how to write AMO code to manage the different logical objects in a tabular model. Not all functions to manage tabular models are implemented in the library; but, I expect the necessary knowledge to complete the library is included in the AMO2Tabular source code. Also, you are expected to contribute to the library; once you find that certain functionality was not included and you have to implement that functionality, please consider donating your code to expand the library.
The scope of the library comprehensive, in the sense that it has functionality for all tabular objects. However, as mentioned earlier, it is not extensive; it does not necessarily cover all possible operations on every tabular object. If coding certain operation, on a particular object, is not an obvious solution, you can be certain that I have included that operation in the library; what remains to be seen is what you consider obvious vs. what I do (I just hope there is not too much difference here).
The 'AdventureWorks Tabular AMO 2012' sample was designed with two purposes in mind. The first purpose was for it to be a test bed for the library; where most of the functionality of the library could be tested. The second purpose was to have a sample on how to use the library to build and manage a tabular model; also, the model created by the sample should have enough complexities to be a good showcase of the capabilities of the library.
The downloadable file contains one solution -Tabular AMO- with both projects: AMO2Tabular and 'AdventureWorks Tabular AMO 2012'. See the Readme file for a quickstart; or, see the Setup and Execute file for a detailed explanation on building the tabular model.
‘Tabular AMO’ is a set of two samples with the purpose of demonstrating how to create a tabular model, as a developer, using AMO. The solution consists of a sample library of functions to manage tabular models and another sample that shows how to use the library to create a tabular model.
‘AdventureWorks Tabular AMO’ is the C# sample program that builds a Tabular model using the AMO2Tabular V2 library (AMO2Tabular). The main purpose of the sample is to illustrate how you would create a tabular model, as a developer, using a programming language and AMO2Tabular.
‘AMO2Tabular’ is a sample of a library designed to manage tabular models using AMO. AMO2Tabular functions range from creating a tabular database, to modify columns, to create hierarchies and to manage partitions; but not limited to only those functions. AMO2Tabular does not intend to be the complete library; rather to exemplify how most operations should be built, leaving other operations to the user to implement them.
The sample and the learning are in the source code; more than in compiling and executing the code. The execution of the ‘AdventureWorks Tabular AMO’ code is the proof that the library works.
The sample creates a tabular model; it starts from creating a tabular database, then goes through the process of creating tables and all related elements until finalizing with some security elements; also, at the end, the sample exercises a little with partition management.
The created model is similar to ‘AdventureWorks Tabular Model SQL Server 2012’ (available to download from Codeplex at http://msftdbprodsamples.codeplex.com/downloads/get/353143).
By creating a model similar to an existing one (and one that you can easily download), you can verify that the results obtained by using the library are the same as those you would obtain by designing the model using Microsoft SQL Server Data Tools.
An important note here is to say, that by design: models created using the AMO2Tabular V2 library cannot be used in Microsoft SQL Server Data Tools (formerly known as BIDS). Models created using AMO2Tabular, can be queried and used from Microsoft SQL Server Management Studio.
These samples use HelloWorld_Picnic, a tiny, simple data model, to demonstrate new features of Power View (formerly Project Crescent) and PowerPivot for Excel for SQL Server 2012. The fictional data tracks use of food and drink at picnics.
The Power View sample report features multiple pages, export to PowerPoint, filtering, sorting, chart interactivity, charts with a ‘play’ axis, using images to enhance design, and other new functionality. Open the sample report or make it yourself following the Power View tutorial.
The PowerPivot sample workbook shows new reporting properties and some best practices for enhancing Power View reports. Open the sample workbook or make it yourself following the PowerPivot tutorial.
Download Power View and PowerPivot for Excel HelloWorldPicnic Samples zip file package.
You also need to download a set of image files from the Microsoft Download Center. Download Sample Images.
You can do a few different things with these sample files:
See the Prerequisites section.
The PowerViewPowerPivotHelloWorldPicnicSamples download zip file package contains these files:
You will also need this zip file for both Power View and PowerPivot:
Upload the PowerPivot (XLSX) files to your SharePoint Server 2010 site with the SQL Server 2012 Reporting Services add-in installed.
Follow the tutorial.
Alternatively, you can upload the sample report, as described in the next procedures.
If you uploaded the XLSX file to the PowerPivot Gallery, upload the report there, too.
http://localhost/PowerPivot Gallery/HelloWorldPicnicSQL2012/HelloWorldPicnicPowerViewRTM.xlsx
If you uploaded the XLSX file to Shared Documents, upload the report there, too.
For help creating the RSDS file, see “Before you start the tutorial” in Tutorial: Create a Sample Report in Power View.
You can open either XLSX workbook in Excel and then open the PowerPivot window.
This opens the workbook in SharePoint.
HelloWorldPicnicImages includes 56 .png image files that are used in the HelloWorldPicnic sample model for PowerPivot for Excel and sample report for Power View. The images represent the 4 categories -- fruits, vegetables, breads, and beverages -- and 24 items in these categories. For each of the 24 items, there are 2 files: a picture and a drawing of that item. For the 4 categories, there are 2 files: a picture and a drawing of the category.
Download the images and the ReadMe from Sample Images. For information about copyright and usage terms for the images, see the ReadMe on the download site.
Publish HelloWorldPicnic Images
The Adventure Works Internet Sales Tabular Model SQL Server 2012 sample is a completed version of a Tabular Model project solution you can complete yourself by using the SQL Server 2012 Analysis Services Tabular Modeling Adventure Works Tutorial. It is strongly recommended you already be familiar with tabular modeling concepts before installing and working with this sample.
! Alert !
This sample is not meant to be used in DirectQuery mode. See Formula Compatibility in Direct Query Mode to obtain a deeper understanding of the differences about running a model in In-Memory mode versus DirectQuery mode.
In order to open the project solution, you must have SQL Server Data Tools installed (Microsoft Visual Studio 2010 with the SQL Server Data Tools add-in). This project also requires you have the AdventureWorksDW2012 sample database installed to a SQL Server database instance on your network.
In order to re-load and process data from the AdventureWorksDW2012 data source, you must change the credentials used to connect to the data source.
Requirements to author and deploy Adventure Works Internet Sales Tabular Model sample project:
SQL Server 2012 Analysis Services tabular modeling capabilities.