SQL Server Samples Readme

  • Article

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.

 

 
Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Summary

The following table lists all Microsoft SQL Server 2012 samples, by technology.

Description Readme Link
Adventure Works OLTP database and data warehouse

(relational database)

 Adventure Works Sample Databases

Download AdventureWorks2012 Data File from Samples.

Download AdventureWorksLT2012 Data File from Samples.

Download AdventureWorksDW2012 Data File from Samples.
Analysis Services Multidimensional Databases and Tutorials AdventureWorks Multidimensional Databases

Download AsdventureWorks Multidimensional Models SQL Server 2012 from Samples.

Analysis Services Tutorial on Multidimensional Modeling

Download Analysis Services Tutorial SQL Server 2012 from Samples
Analysis Services

Tabular models

Created in SQL Server Data Tools

 Adventure Works DW Tabular SQL Server 2012

Download AdventureWorks Tabular Model SQL Server 2012 from Samples.
Analysis Services

Tabular models

Created in C# using AMO

 Tabular AMO 2012

Download from Tabular AMO 2012.
Reporting Services

Power View reports

Model created in PowerPivot for Excel

 Power View and PowerPivot Samples for SQL Server 2012

Download from Power View and PowerPivot Samples.
Images for Power View and PowerPivot sample report and

workbook

 Power View Sample Images

Download from Sample Images.
Analysis Services Tabular Models

Adventure Works Internet Sales solution

 Adventure Works Internet Sales Tabular Model SQL Server 2012 Sample

Download from Samples.,mn n m

 

↑ Back to top

Scenario

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.

  1. For reports based on tabular models created in SQL Server Data Tools or in PowerPivot for Excel, end users launch ProPower View or PowerPivot from a SharePoint site.
  2. For reports based directly on OLAP cubes and RDBMS databases, end users run Reporting Services reports.

In this release, sample reports are included only for the first case.

 

↑ Back to top

Prerequisites

Server Prerequisites

Your system must meet the following requirements to use the samples.

Client Prerequisites

To create models, your system needs one or both of the following applications.

  • SQL Server Data Tools To create SQL Server 2012 Analysis Services tabular models. For more information, see SQL Server Data Tools.
  • PowerPivot for Microsoft Excel 2010 To create models in PowerPivot. For more information, see PowerPivot for Excel.

For information about Power View requirements, see System requirements for Power View.

 

↑ Back to top

Readme for Adventure Works 2012 Sample Databases

Download and install the SQL Server 2012 version of the Adventure Works OLTP database and AdventureWorksDW data warehouse file.

Case-insensitive and case-sensitive AdventureWorks databases

The AdventureWorks2012 database has two versions:

  1. AdventureWorks2012_Data.mdf for case-insensitive and
  2. 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

  1. Download the AdventureWorks2012 Data File.

  2. From the License Agreement, click I Agree.

  3. From File Download, click Save and browse to a location on your local server.

  4. From SQL Server Management Studio, execute the following code:

    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).

    Note:  
    You must remove the log file from the list of files to attach. This will cause the operation to rebuild the log.  

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

  1. Download AdventureWorks 2012 OLTP Script.zip or AdventureWorks 2012 CS OLTP Script.zip.

  2. Copy the DDL script files to {SQL Server Path}\Tools\Samples\AdventureWorks 2012 OLTP 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.

  3. Open {SQL Server Path}\Tools\Samples\AdventureWorks 2012 OLTP\instawdb.sql in SQL Server Management Studio.

  4. Update the script path variables.

    1. If necessary, change the :setvar SqlSamplesDatabasePath variable to your SQL Server 2012 path. The default 64-bit path is C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA. Use C:\Program Files (x86)\.. for 32-bit SQL Server 2012.
    2. Change the :setvar SqlSamplesSourceDataPath variable to the path containing the .csv files.

  5. On the Query menu, click SQLCMD Mode.

  6. On the Standard toolbar, click the Execute button to run the script.

Install AdventureWorksLT2012 OLTP database

The AdventureWorksLT2012 OLTP database can be installed using the CREATE DATABASE statement or by attaching the database.

To install AdventureWorksLT2012 OLTP database

  1. Download the AdventureWorksLT2012 Data File.

  2. From the License Agreement, click I Agree.

  3. From File Download, click Save and browse to a location on your local server.

  4. From SQL Server Management Studio, execute the following code:

    Case-insensitive Database

    CREATE DATABASE AdventureWorksLT2012 ON (FILENAME = '{drive}:\{file path}\AdventureWorksLT2012_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).

    Note:  
    You must remove the log file from the list of files to attach. This will cause the operation to rebuild the log.  

Install AdventureWorksLT2012 OLTP database from script

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

  1. Download AdventureWorks LT 2012 OLTP Script.zip.

  2. Copy the DDL script files to {SQL Server Path}\Tools\Samples\AdventureWorks LT 2012 OLTP 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.

  3. Open {SQL Server Path}\Tools\Samples\AdventureWorks LT 2012 OLTP\instawltdb.sql in SQL Server Management Studio.

  4. Update the script path variables.

    1. If necessary, change the :setvar SqlSamplesDatabasePath variable to your SQL Server 2012 path. The default 64-bit path is C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA. Use C:\Program Files (x86)\.. for 32-bit SQL Server 2012.
    2. Change the :setvar SqlSamplesSourceDataPath variable to the path containing the .csv files.

  5. On the Query menu, click SQLCMD Mode.

  6. On the Standard toolbar, click the Execute button to run the script.

Install AdventureWorksDW2012 data warehouse

  1. Click the AdventureWorksDW2012 Data File link on Adventure Works for SQL Server 2012 RC0.

  2. From the License Agreement, click I Agree.

  3. From File Download, click Save and browse to a location on your local server.

  4. From SQL Server Management Studio, execute the following code:

    {{ CREATE DATABASE AdventureWorksDW2012 ON (FILENAME = '<drive>:\file path>\AdventureWorksDW2012_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).

    Note:  
    You must remove the log file from the list of files to attach. This will cause the operation to rebuild the log.  

     

Changes in AdventureWorksDW in the SQL Server 2012 Release

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. 

  • YEAR data shifted by 4. Before the year shift, dates ranged from January 1, 2001 through December 31, 2006, with 2004 being a leap year. In the current version of the Adventure Works DW database, the date range is January 1, 2005 through December 31, 2010, with the leap year occurring in 2008.
  • WEEKNUMBER is now consistent with the output produced by the T-SQL DATEPART command, following ISO standard 8601. Although this was a relatively small change in terms of number of rows affected, you should test any scripts, code, or examples that include the WEEKNUMBER column.
  • Additional IMAGES are now available in the data warehouse. Previously, images were primarily in the OLTP database. This release adds more images to the data warehouse.

 Databases affected:

  • AdventureWorksDW2012 database –
    • This is the relational data warehouse used in various tutorials and as the underlying data source for Analysis Services multidimensional and tabular model projects.
  • Adventure Works DW Tabular 2012 database –
    • This tabular model is built from the Adventure Works Tabular Denali project, using the Adventure Works DW database as its underlying data source.
  • AdventureWorksDW2012Multidimensional database –
    • This multidimensional model is built from the AdventureWorksDW2012Multidimensional project, available in Enterprise and Standard versions to support different levels of functionality. Both projects use the AdventureWorksDW2012 database as the underlying data source.
  • Analysis Services Tutorial Projects –
    • This is a collection of multidimensional model projects that supplement the Analysis Services tutorial. All of the tutorial projects use the Adventure WorksDW2012 database as the underlying data source.
  • Data Mining Model Projects –
    • This is a collection of data mining model projects that supplement the Data Mining tutorials. All of the data mining projects use the AdventureWorksDW2012 database as the underlying data source.

 

↑ Back to top

Readme for Adventure Works 2008 R2 Sample Database

Download and install the SQL Server 2008R2 version of the Adventure Works OLTP database and AdventureWorksDW data warehouse file.

Install AdventureWorks2008R2 OLTP database

The AdventureWorks2008R2 OLTP database can be installed using the CREATE DATABASE statement or by attaching the database.

To install AdventureWorks2008R2 OLTP database

  1. Download the AdventureWorks2008R2 Data File.

  2. From the License Agreement, click I Agree.

  3. From File Download, click Save and browse to a location on your local server.

  4. From SQL Server Management Studio, execute the following code:

    Case-insensitive Database

    CREATE DATABASE AdventureWorks2008R2 ON (FILENAME = '{drive}:\{file path}\AdventureWorks2008R2_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).

    Note:  
    You must remove the log file from the list of files to attach. This will cause the operation to rebuild the log.  

Install AdventureWorks2008R2 OLTP database from script

  1. Download AdventureWorks 2008 R2 OLTP Script.zip .

  2. Copy the DDL script files to any path such {SQL Server Path}\Tools\Samples\AdventureWorks 2008 OLTP Script.

    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.

  3. Open {SQL Server Path}\Tools\Samples\AdventureWorks 2008 OLTP\instawdb.sql in SQL Server Management Studio.

  4. Update the script path variables.

    1. If necessary, change the :setvar SqlSamplesDatabasePath variable to your SQL Server 2008 R2 path. The default 64-bit path is C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA. Use C:\Program Files (x86)\.. for 32-bit SQL Server 2012.
    2. Change the :setvar SqlSamplesSourceDataPath variable to the path containing the .csv files.
    3. Uncomment each :setvar variable.

  5. On the Query menu, click SQLCMD Mode.

  6. On the Standard toolbar, click the Execute button to run the script.

↑ Back to top

Readme for Analysis Services Tutorial on Multidimensional Modeling

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.

Grant Permissions for Processing or Importing Data 

  1. 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.
  2. In Management Studio, connect to the database engine instance.
  3. Expand the Security folder, right-click Logins and select New Login.
  4. On the General page, in Login name, type NT Service\MSSQLServerOLAPService (or whatever account the service is running as).
  5. Click User Mapping.
  6. Select the checkbox next to the AdventureWorksDW2012 database. Role membership should automatically include db_datareader and public. Click OK to accept the defaults.

Install and Open Analysis Services Tutorial Projects

  1. Download the "Analysis Services Tutorial SQL Server 2012" from Codeplex.

  2. 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:

    • 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

  3. Start SQL Server Data Tools.

  4. Open the solution (.sln) file that corresponds to the lesson you are using.

  5. 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 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.

  6. 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 installation media:

  • Database Engine
  • Analysis Services installed in Multidimensional server mode (default)
  • SQL Server Data Tools
  • SQL Server Management Studio
  • Read access to an instance of AdventureWorksDW2012 database

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 deploy an Analysis Services multidimensional model in SQL Server Data Tools.

↑ Back to top

Readme for AdventureWorksDW2012 Multidimensional Databases

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.

Install the samples

  1. Download the "AdventureWorks Multidimensional Models SQL Server 2012" from Codeplex.
  2. Unzip the download file to your downloads folder.
  3. 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):
    1. Right click on the file and select properties
    2. Locate the UNBLOCK button, in the lower right side of the properties window, and click it.
    3. If you don't unblock the zip file you will have to unblock all individual files inside the zip file.

Test and validate the samples

  1. Verify you have administrator access to the Analysis Services instance:

    1. 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.

    2. Expand the Databases folder and note the existing databases.

    3. Right click the Databases folder and select New Database.

    4. In the Database name text box add any name you want; make sure you choose a name that is short and not already used (you are going to create and delete the database immediately).

      Click OK to create an empty database.

    5. If you succeeded creating the database, you have administrator permission. You can now delete the database by right clicking on the database name and following the delete steps.

  2. Depending on the edition of SQL Server that you have installed, open the Standard or Enterprise folder. If you are using the Business Intelligence, Developer, or Evaluation editions, choose the Enterprise project.

  3. Double click the solution file: AdventureWorksDW2012Multidimensional-EE.sln to open the solution in SQL Server Data Tools.

  4. In Solution Explorer, 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'.

  5. In the connection manager window:

    1. Verify the server name points to the server and instance where AdventureWorksDW2012 database is located.
    2. Verify you have the correct log on to the server credentials.
    3. Verify connect to a database points to AdventureWorksDW2012
    4. Click on Test Connection and the connection should succeed

  6. Right click the database object, it is the topmost object in the Solution Explorer tree, and select Properties.

  7. 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.

  8. Right click the database object, again, and select Show Deployment Progress.

  9. Right click the database object, again, and select Deploy.

    Watch the deployment progress until it comes to a successful end. 

Requirements

Requirements to install and deploy AdventureWorksDW2012Multidimensional sample database and project:

  • Windows 2008 R2
  • SQL Server 2012 Analysis Services, deployed in multidimensional mode.
  • SQL Server Data Tools, a feature to install when installing SQL Server, used to create Analysis Services projects.
  • Read access to an instance of AdventureWorksDW2012 database. 

Demonstrates

Analysis Services Multidimensional capabilities.

↑ Back to top

Readme for Adventure Works DW Tabular SQL 2012

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.

 

 ! 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.  

Install the samples

  1. Unzip the download file to your downloads folder.
  2. 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):
    1. Right click on the file and select properties
    2. Locate the UNBLOCK button, in the lower right side of the properties window, and click it.
    3. If you don't unblock the zip file you will have to unblock all individual files inside the zip file.
  3. (Optional) Locate the 'Adventure Works DW Tabular SQL 2012 folder and move, or copy, that folder to your Visual Studio 2010 projects folder
  4. Install the relational data source AdventureWorksDWDenali, if not available to you. See the readme of the database to do so.
  5. The solution files are located under Adventure Works DW Tabular SQL 2012 folder. 

Test and validate the samples

  1. Verify your read access to the relational data source AdventureWorksDW2012

    1. 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.

    2. Expand the Databases folder.

    3. Locate the AdventureWorksDW2012 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.

    4. Expand the AdventureWorksDW2012 database object, expand the tables folder

    5. 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.

  2. Verify you have administrator privileges to access a tabular instance of Analysis Services where your workspace database and the sample model would be deployed:

    1. 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.

    2. Right click the server instance, on the left pane, and select Properties.

    3. 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.

  3. Open the folder that contains the solution files.

  4. Double click the solution file: Adventure Works DW Tabular 2012.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.

  5. Once the solution is open, in the Solution Explorer, right click over the Adventure Works DW Tabular SQL 2012.bim file and select Properties. Verify the Workspace Server name matches the tabular instance from step 2; adjust as needed.

  6. In the Solution Explorer pane, double click over the Adventure Works DW Tabular SQL 2012.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.

  7. 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.

  8. 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.

  9. 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.

    1. From the menu bar, click on Model, hover over Calculation Options and select Manual Calculation.
    2. Select one table with the warning sign, by clicking on the label. From the menu bar, click on Model and select Calculate Now.
    3. 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.

  10. From the menu bar, click on File and select Save All.

  11. In the Solution Explorer pane, right click on Adventure Works DW Tabular SQL 2012 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.

  12. 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.

  13. To test the deployed instance of the sample model:

    1. Open SQL Server Management Studio and connect to the Analysis Services instance from step 2.

    2. Expand the Databases folder and locate Adventure Works DW Tabular SQL 2012. 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.

    3. Right click on Adventure Works DW Tabular SQL 2012 database, hover over New Query and select MDX.

    4. Verify that Adventure Works DW Tabular SQL 2012 is the selected database in the Available Databases drop down list box (use <CTRL><ALT><J> shortcut to locate the list).

    5. 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]

    6. Click the Execute ! button.

    7. The following results should appear after a moment:

      All Accessories Bikes Clothing Components
      All $109,809,274.20 $1,272,057.89 $94,620,526.21 $2,117,613.45 $11,799,076.66
      2005 $11,331,808.96 $20,235.36 $10,661,722.28 $34,376.34 $615,474.98
      2006 $30,674,773.18 $92,735.35 $26,486,358.20 $485,587.15 $3,610,092.47
      2007 $41,993,729.72 $590,242.59 $34,910,877.69 $1,010,112.16 $5,482,497.29
      2008 $25,808,962.34 $568,844.58 $22,561,568.03 $587,537.80 $2,091,011.92

Requirements

Requirements to install and deploy Adventure Works DW Tabular Denali sample database and project:

  • Windows 2008 R2
  • SQL Server Analysis Services SQL 2012, an instance deployed in tabular mode.
  • SQL Server SQL 2012 Data Tools, a feature to install when installing SQL Server.
  • Read access to an instance of AdventureWorksDWDenali database. 

Demonstrates

Analysis Services Tabular capabilities.

↑ Back to top

Readme For Tabular AMO

Project Description

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.

 

Readme First Document

1. Introduction

‘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.

2. Requirements

Software
  • SQL Server 2012 (Standard, Enterprise or Developer edition)
  • Visual C# 2010 Express or Visual Studio 2010 (Professional, Premium or Ultimate edition)
  • Visual Studio 2010 SP1
Environment

3. Install

  1. If you are reading this note you already downloaded the required files and had unzipped them; in case you haven’t unzipped the files, do it now to your preferred location for developer projects.
  2. Open the ‘Tabular AMO.sln’ solution file with Visual Studio or by double clicking on it.
  3. Build the solution (will build the library -AMO2Tabular- and the sample -AdventureWorks Tabular AMO-) either by selecting build the solution from the Build menu or by pressing [CTRL+SHIFT+B].
  4. Run the solution. If Microsoft SQL Server is installed, both instances (rdbms and tabular) are running and the user executing the sample has read access to ‘AdventureWorksDW2012’ and has server administrator privileges to the tabular instance, the sample should run with no problems.
  5. Please note that the security section, in AdventureWorks Tabular AMO, is grayed. See the ‘Setup and Execute’ guide for more details.

 

↑ Back to top

Readme for Power View and PowerPivot HelloWorldPicnic Samples for SQL Server 2012

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:

  • Explore and interact with a completed report created in Power View and model created in PowerPivot for Excel.
  • Create a Power View report based on a model created in PowerPivot for Excel.
  • Create a model in PowerPivot for Excel and then optimize it for Power View (and then create a Power View report based on your model).

Prerequisites

See the Prerequisites section.

Contents of the Download File

The PowerViewPowerPivotHelloWorldPicnicSamples download zip file package contains these files:

For the Power View tutorial

  • HelloWorldPicnicPowerViewRTM.xlsx PowerPivot sample model from which you can build simple Power View reports, including HelloWorldPicnicReport.rdlx
  • HelloWorldPicnicReport.rdlx Completed sample Power View (RDLX) file that you can create yourself from HelloWorldPicnicPowerViewRTM.xlsx, by following the Tutorial: Create a Sample Report in Power View.

For the PowerPivot tutorial

You will also need this zip file for both Power View and PowerPivot:

Download the HelloWorldPicnic XLSX and RDLX files

  1. Download the PowerViewPowerPivotHelloWorldPicnicSamples zip file.
  2. Unzip the files to a local folder. There are three files.

Publish the HelloWorldPicnic XLSX files

Upload the PowerPivot (XLSX) files to your SharePoint Server 2010 site with the SQL Server 2012 Reporting Services add-in installed.

  • Create a folder called HelloWorldPicnicSQL2012 and save the files to that folder in:

    • A PowerPivot Gallery.
    • A Shared Documents library. If you upload the files to the Shared Documents library, you need to create a shared data source so you can create Power View reports based on the XLSX files. The tutorials explain how to create the shared data source file.

Set up the Power View Samples

Start Power View

  • From the PowerPivot Gallery, Gallery View, for HelloWorldPicnicPowerViewRTM.xlsx, click the Create Power View Report icon.

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

If you uploaded the XLSX file to the PowerPivot Gallery, upload the report there, too.

  1. Create a HelloWorldPicnicSQL2012 folder in the PowerPivot Gallery and upload the PowerPivot workbook HelloWorldPicnicPowerViewRTM.xlsx there.
  2. In the PowerPivot Gallery, click the Library tab and switch Current View from Gallery to All Documents.
  3. From the drop-down menu for the report, click Manage Data Sources. The data source page displays EntityDataSource.
  4. Click EntityDataSource to open the data source management page.
  5. In Data Source Link, paste in the following text:

http://localhost/PowerPivot Gallery/HelloWorldPicnicSQL2012/HelloWorldPicnicPowerViewRTM.xlsx

  1. If you need to, substitute the URL for the sample model on your SharePoint site.
  2. Click OK, and then click Close.

OR: Upload the report to Shared Documents

If you uploaded the XLSX file to Shared Documents, upload the report there, too.

  1. Create a HelloWorldPicnicSQL2012 folder in the Shared Documents library on your SharePoint site and upload the PowerPivot workbook HelloWorldPicnicPowerViewRTM.xlsx there.
  2. Create an RSDS file called HelloWorldPicnicRSDS.rsds in the same folder.

For help creating the RSDS file, see “Before you start the tutorial” in Tutorial: Create a Sample Report in Power View.

  1. From the drop-down menu for the report, click Manage Data Sources. The data source page displays EntityDataSource.
  2. Click EntityDataSource to open the data source management page.
  3. In Data Source Link, browse to HelloWorldPicnicRSDS.rsds.
  4. Click OK, and then click Close.

Set up the PowerPivot Samples

Start PowerPivot

You can open either XLSX workbook in Excel and then open the PowerPivot window.

  • HelloWorldPicnicPowerViewRTM.xlsx is a completed PowerPivot sample model from which you can build simple Power View reports, including HelloWorldPicnicReport.rdlx
  • HelloWorldPicnicModelPowerPivotTutorialRTM.xlsx is a plain Excel (XLSX) file with nothing but tables, which you can modify to create the model HelloWorldPicnicPowerViewRTM.xlsx by following the Tutorial: Optimize a Sample PowerPivot Model for Power View Reports.
  1. From the PowerPivot Gallery, click the image of the file HelloWorldPicnicModelPowerPivotTutorialRTM.xlsx.

This opens the workbook in SharePoint.

  1. Click Open in Excel.
  2. Click the PowerPivot tab, and then click PowerPivot Window.
  3. Follow the tutorial steps in Tutorial: Optimize a Sample PowerPivot Model for Power View Reports.

OR: Start PowerPivot from Shared Documents

  1. From Shared Documents, for HelloWorldPicnicModelPowerPivotTutorialRTM.xlsx, click the dropdown arrow and click Edit in Microsoft Excel.
  2. Click the PowerPivot tab, and then click PowerPivot Window.
  3. Follow the tutorial steps in Tutorial: Optimize a Sample PowerPivot Model for Power View Reports.

↑ 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.

Publish HelloWorldPicnic Images

  1. Download HelloWorldPicnicImages from Sample Images . For more information about the images, see the ReadMe on the download site.
  2. Unzip the files to a local folder. There are 56 .png files.
  3. In the SharePoint site document library Shared Documents, create a folder named HelloWorldPicnicSQL2012, if you haven't already, and then create a subfolder named Images.
  4. Upload all the .png image files to the Images folder.

↑ Back to top

 

Readme for Adventure Works Internet Sales Tabular Model SQL Server 2012 sample (developer sample)

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.

Install the sample

  1. Unzip the download file to your downloads folder.
  2. 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):
  3. Right click on the file and select properties
  4. Locate the UNBLOCK button, in the lower right side of the properties window, and click it.
  5. If you don't unblock the zip file you will have to unblock all individual files inside the zip file.
  6. (Optional) Locate the ‘Adventure Works Internet Sales Tabular Model SQL Server 2012’ folder (in Downloads) and move to your Visual Studio 2010 projects folder

Change data source connection information

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.

  1. Open the solution in SQL Server Data Tools.
  2. On the Model menu, click Existing Connections.
  3. In the Existing Connections dialog box, verify there is a connection named SQLServer localhost AdventureWorksDW2012, and then click Edit.
  4. If you installed the AdventureWorksDW2012 sample database to a server other than localhost, in the Edit Connection dialog box, in Server name, enter that server name.
  5. Click Impersonation.
  6. In the Impersonation Information dialog box, in Specific Windows user name and password, enter a user name with Read permissions on the  AdventureWorksDW2012 sample database, and then click Ok.
  7. In the Edit Connection dialog box, click Test Connection. If the connection test fails, verify your AdventureWorksDW2012 sample database and connection information. Otherwise, click Save. You can now run a Process All to load data from the AdventureWorksDW2012 sample database to the model workspace database.

Requirements

Requirements to author and deploy Adventure Works Internet Sales Tabular Model sample project:

  • SQL Server 2012 Analysis Services instance running in Tabular Mode.
  • Read access to an instance of the sample AdventureWorksDW2012 database.
  • SQL Server Data Tools, a feature you can select when installing SQL Server.

Demonstrates

SQL Server 2012 Analysis Services tabular modeling capabilities.

↑ Back to top

See Also