Summary
The following table lists all Microsoft SQL Server 2012 samples, by technology.
Adventure Works OLTP database and data warehouse (relational database)
Adventure Works Sample Databases Download AdventureWorksDWDenali Data File from Samples The data warehouse will be modified in the final product release. For more information, see "Pending Changes for AdventureWorksDW in the SQL Server 2012 RTM Release" further down on this page
Analysis Services Multidimensional models
Analysis Services Tabular models Created in SQL Server Data Tools
Reporting Services Power View reports Model created in PowerPivot for Excel
Images for Power View and PowerPivot sample report and workbook
Power View Sample Images Download from Sample Images.
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. Back to top
Download and install the SQL Server 2012 version of the Adventure Works OLTP database and data warehouse: AdventureWorksDW Data File from Samples.
Case-insensitive and case-sensitive AdventureWorks databases
The AdventureWorks2012 database has two versions: AdventureWorks2012_Data.mdf for case-insensitive and AdventureWorks2012_CS_Data.mdf for case-sensitive. 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.
How to install AdventureWorks2012 OLTP database
The AdventureWorks2012 OLTP database can be installed using the CREATE DATABASE statement or by attaching the database.
To install AdventureWorks2012 OLTP database
Download the AdventureWorks2012 Data File.
From the License Agreement, click I Agree.
From File Download, click Save and browse to a location on your local server.
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).
How to install AdventureWorks2012 OLTP database from script
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
Install AdventureWorksDWDenali data warehouse
Click the AdventureWorksDWDenali Data File link on Adventure Works for SQL Server 2012 RC0.
From SQL Server Management Studio, execute the following code:
{{ CREATE DATABASE AdventureWorksDWDenali ON (FILENAME = '<drive>:\<file path>\AdventureWorksDWDenali_Data.mdf') FOR ATTACH_REBUILD_LOG ;}}
{{ CREATE DATABASE AdventureWorksDWDenali
Pending Changes for AdventureWorksDW in the SQL Server 2012 RTM Release This notice informs you of upcoming 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.
Databases affected:
Back to top
This sample includes 11 sample projects that accompany the Analysis Services multidimensional modeling tutorial. Download SSAS Multidimensional Model Projects SQL Server 2012 RC0 from Samples. 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. Note: These tutorials are a work in progress. Screenshots are out of date. Instructions for browsing data are also incorrect. We recommend that you use the Analyze in Excel feature to browse data in the tutorials.
Step 1: Install the AdventureWorksDWDenali Database and Grant Permissions
The sample projects use transactional data that you import from a relational database management system or some other external data source. For the purposes of these sample projects, you will install the AdventureWorksDenaliDW relational database. Download the sample database from Codeplex
Unzip the sample databases.
Copy the AdventureWorksDWDenali_Data.mdf file to the data directory of the local SQL Server Database Engine instance. By default, it is located at C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data.
Start SQL Server Management Studio and connect to the Database Engine instance.
Right-click Databases, click Attach.
Click Add.
Select the AdventureWorksDWDenali_Data.mdf database file and click OK.
In database details, remove the Log file entry. The sample download does not include a log file. 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.
Determine the service account under which Analysis Services is running. You can use SQL Server Configuration Manager or the Services console application to view account information. If you installed Analysis Services using the default account, the service is running as NT Service\MSSQLServerOLAPService.
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 Service\MSSQLServerOLAPService (or whatever account the service is running as).
Click User Mapping.
Select the checkbox next to the AdventureWorksDWDenali database. Role membership should automatically include db_datareader and public. Click OK to accept the defaults.
Step 2: Install and Open Analysis Services Tutorial Projects
Download the sample project files from Codeplex.
Unzip the sample projects. After you extract the files, you should have the following projects installed in the SSAS Multidimensional Tutorial Projects folder on your computer:
Start SQL Server Data Tools.
Open the solution (.sln) file that corresponds to the lesson you are using.
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 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 AdventureWorksDWDenali 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.
In SQL Server Management Studio, connect to Analysis Services. Verify that a database named Analysis Services Tutorial is running on the server.
Requirements
Using the sample projects requires that you install the following features using SQL Server 2012 RC0 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.
Demonstrates
The projects in this sample demonstrate how to build an Analysis Services multidimensional model in SQL Server Data Tools. Back to top
This sample contains projects to build the multidimensional model of Adventure Works DW SQL Server 2012 RC0, for the enterprise and standard edition of SQL Server.
Install the samples
Unzip the download file to your downloads folder.
Unblock the zip file to use the solution file inside (this applies to Windows XP, Windows Vista, Windows 7, Windows Server 2008 and Windows Server 2008 R2):
Right click on the file and select properties
Locate the UNBLOCK button, in the lower right side of the properties window, and click it.
If you don't unblock the zip file you will have to unblock all individual files inside the zip file.
(Optional) Locate the 'Adventure Works DW Multidimensional SQL Server Denali' folder and move, or copy, that folder to your Visual Studio 2010 projects folder
Install the relational data source AdventureWorksDWDenali, if not available to you. See the readme of the database to do so.
The solution files are located under Adventure Works DW Multidimensional Denali folder.
Test and validate the samples
Verify your administrator access to a multidimensional instance of Analysis Services:
Open SQL Server Management Studio and connect to an Analysis Services instance. localhost, the default value, should be enough; if you are connecting to a remote server, ask your DBA about server name and instance.
Expand the Databases folder and make mental note of the existing databases.
Right click the Databases folder and select New Database.
In the Database name text box add any name you want; make sure it doesn't exist and is short (you are going to create and delete the database immediately).
Click OK to create an empty database.
If you succeeded creating the database, delete the database by right clicking on the database name and following the delete steps.
If you couldn't successfully create a database, because of permission rights, then you need to have access to an instance where you have administrator credentials before continuing with this installation.
Depending on the edition of SQL Server that you have installed, open the Standard or Enterprise folder. If you are using the Developer evaluation edition, it has the same behavior as Enterprise edition.
Double click the solution file: Adventure Works DW Multidimensional Denali.sln.
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.
Once the solution is open, in the Solution Explorer pane locate the Data Source folder. Right click the 'Adventure Works.ds' data source and from the context menu select 'Open' and in the connection string area click 'Edit'.
In the connection manager window:
Verify the server name points to the server and instance where AdventureWorksDWDenali database is located.
Verify you have the correct log on to the server credentials.
Verify connect to a database points to AdventureWorksDWDenali
Click on Test Connection and the connection should succeed
Right click the database object, it is the topmost object in the Solution Explorer tree, and select Properties.
In the navigation tree, on the left pane, select Deployment and verify the Server property names the Analysis Services instance where you have administrator privileges (step 1); adjust if needed and click OK, otherwise you can click Cancel.
Right click the database object, again, and select Show Deployment Progress.
Right click the database object, again, and select Deploy.
Watch the deployment progress until it comes to a successful end.
Requirements to install and deploy Adventure Works DW Multidimensional Denali sample database and project:
Analysis Services Multidimensional capabilities.
This sample contains project to build the tabular model of Adventure Works DW Tabular SQL 2012 RC0. Download SSAS Tabular Model Projects SQL 2012 RC0 from Samples.
(Optional) Locate the 'Adventure Works DW Tabular SQL 2012 RC0' folder and move, or copy, that folder to your Visual Studio 2010 projects folder
The solution files are located under Adventure Works DW Tabular SQL 2012 RC0 folder.
Verify your read access to the relational data source AdventureWorksDWDenali
Open SQL Server Management Studio and connect to a Database Engine instance. localhost, the default value, should be enough; if you are connecting to a remote server, ask your DBA for server name and instance.
Expand the Databases folder.
Locate the AdventureWorksDWDenali database.
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.
Expand the AdventureWorksDWDenali database object, expand the tables folder
Select any table, right click on it and click on Select Top 1000 rows.
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.
Verify you have administrator privileges to access a tabular instance of Analysis Services where your workspace database and the sample model would be deployed:
Right click the server instance, on the left pane, and select Properties.
If no error message appears when you clicked on properties and you got the properties tab in the right pane, then you have administrator privileges in the server.
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.
Open the folder that contains the solution files.
Double click the solution file: Adventure Works DW Tabular 2012 RC0.sln.
Once the solution is open, in the Solution Explorer, right click over the Adventure Works DW Tabular SQL 2012 RC0.bim file and select Properties. Verify the Workspace Server name matches the tabular instance from step 2; adjust as needed.
In the Solution Explorer pane, double click over the Adventure Works DW Tabular SQL 2012 RC0.bim file.
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.
From the menu bar, click on Model and select Existing connections.
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.
Again, from the menu bar, click on Model, hover over Refresh and select Refresh All from the context menu. Wait a moment until the model, with data, appears in the window.
Some tables may appear with a warning sign on the name label; this is because some calculated columns need to be re-calculated to get their correct values.
From the menu bar, click on Model, hover over Calculation Options and select Manual Calculation.
Select one table with the warning sign, by clicking on the label. From the menu bar, click on Model and select Calculate Now.
Repeat the recalculate step for each table with a warning sign. Also, remember to review all tabs from the scroll menu, located to the right of the right most visible table tab.
From the menu bar, click on File and select Save All.
In the Solution Explorer pane, right click on Adventure Works DW Tabular SQL 2012 RC0 project and select Properties. Select Deployment from the left pane. Verify the Server name matches the tabular instance from step 2; adjust as needed.
Click OK to close the properties window.
In the Solution Explorer pane, right click on Adventure Works DW Tabular SQL 2012 RC0 project and select Deploy. Wait a moment until the model is deployed.
To test the deployed instance of the sample model:
Open SQL Server Management Studio and connect to the Analysis Services instance from step 2.
Expand the Databases folder and locate Adventure Works DW Tabular SQL 2012 RC0. There might be another database with the same name and your username and a GUID, which is your workspace database. You can identify workspace server and workspace database used by a model by checking the Workspace Database and Workspace Server properties of the .bim file in SQL Server Data Tools.
Right click on Adventure Works DW Tabular SQL 2012 RC0 database, hover over New Query and select MDX.
Verify that Adventure Works DW Tabular SQL 2012 RC0 is the selected database in the Available Databases drop down list box (use <CTRL><ALT><J> shortcut to locate the list).
In the query window copy/paste the following MDX expression:
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]
Click the Execute ! button.
The following results should appear after a moment:
All
$109,809,274.20
$1,272,057.89
$94,620,526.21
$2,117,613.45
$11,799,076.66
2001
$11,331,808.96
$20,235.36
$10,661,722.28
$34,376.34
$615,474.98
2002
$30,674,773.18
$92,735.35
$26,486,358.20
$485,587.15
$3,610,092.47
2003
$41,993,729.72
$590,242.59
$34,910,877.69
$1,010,112.16
$5,482,497.29
2004
$25,808,962.34
$568,844.58
$22,561,568.03
$587,537.80
$2,091,011.92
Requirements to install and deploy Adventure Works DW Tabular Denali sample database and project:
Analysis Services Tabular capabilities.
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 Release Candidate 0 (RC0). 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:
Prerequisites See the Prerequisites section. Contents of the Download File The PowerViewPowerPivotHelloWorldPicnicSamples download zip file package contains these files: For the Power View tutorial
Publish the HelloWorldPicnic XLSX files
If your SharePoint site does not have a PowerPivot Gallery, you can upload the files to the Shared Documents library on your SharePoint site. In that case, you will need to create a shared data source so you can create Power View reports based on the XLSX files. Publish HelloWorldPicnicImages
Start Power View
Follow the tutorial.
Alternatively, you can upload the sample report, as described in the next procedures. Upload the sample Power View report and bind it to the PowerPivot model Upload the report to the PowerPivot Gallery If you uploaded the XLSX file to the PowerPivot Gallery, upload the report there, too.
http://localhost/PowerPivot Gallery/HelloWorldPicnicPowerViewTutorialRC0.xlsx
OR: Upload the report to Shared Documents 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.
Setting up the PowerPivot Samples Start PowerPivot
You can open either XLSX workbook in Excel and then open the PowerPivot window.
Start PowerPivot from the PowerPivot Gallery
This opens the workbook in SharePoint.
OR: Start PowerPivot from Shared Documents
Back to top About HelloWorldPicnic Sample Images for Power View and PowerPivot 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. For information about publishing the images to your SharePoint site, see Publish HelloWorldPicnicImages in this document. Back to top
The AMO2Tabular SQL 2012 RC0 sample is a C# sample, using AMO, to create a tabular model. Through the sample the developer gets acquainted with the relationship between AMO objects and the logical set of objects presented in tabular models. Download SSAS AMO2Tabular SQL 2012 rc0 from Samples.
Install the sample
(Optional) Locate the 'AMO2Tabular SQL 2012 RC0' folder and move, o copy, that folder to your Visual Studio 2010 projects folder
(optional) Install the relational data source AdventureWorksDWDenali, if no other relational data source is available to you. See the readme of the database to do so.
The solution files are located under AMO2Tabular Denali SQL 2012 RC0 folder.
Test and validate the sample code
Verify your read access to the relational data source AdventureWorksDWDenali. If you are planning to use a different relational data source, use that name whenever you are asked to use AdventureWorksDWDenali.
Verify you have administrator privileges to access a tabular instance of Analysis Services where sample model would be created:
Double click the solution file: AMO2Tabular.sln.
If it doesn't open Visual Studio 2010 then the feature wasn't correctly installed; you need to go over the setup process and install Visual Studio 2010 again.
Once the solution is open, in the Solution Explorer, right click over the References folder and select Microsoft.AnalysisServices. If a warning sign appears with the label, you need to recreate the reference; otherwise you should be good to continue.
If you need to recreate the reference to the latest build of Microsoft.AnalysisServices.dll of RC0 or later version; the file should be located at C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.AnalysisServices.dll.
Click on Build, from the menu bar, and select Rebuild Solution. Wait until the solution is compiled.
Click on Debug, from the menu bar, and select Start Debugging. Wait until the form appears on the screen.
Your sample application is functional
The Querying a Tabular Model sample is a C# sample, using ADOMD, to query a tabular model using DAX expressions and MDX expressions. The sample shows that either way (DAX or MDX) can be used to do the query in the same fashion; the functionality is the same regardless of your syntax choice. Download Querying a Tabular Model from Samples. Install the sample: