Tabular Modeling (Adventure Works Tutorial)

This tutorial provides lessons on how to create a SQL Server 2012 Analysis Services tabular model by using SQL Server Data Tools (SSDT).

What You Will Learn

During the course of this tutorial, you will learn the following:

• How to create a new tabular model project in SQL Server Data Tools.

• How to import data from a SQL Server relational database into a tabular model project.

• How to create and manage relationships between tables in the model.

• How to create and manage calculations, measures, and Key Performance Indicators that help users analyze model data.

• How to create and manage perspectives and hierarchies that help users more easily browse model data by providing business and application specific viewpoints.

• How to create partitions that divide table data into smaller logical parts that can be processed independent from other partitions.

• How to secure model objects and data by creating roles with user members.

• How to deploy a tabular model to a sandbox or production instance of Analysis Services running in Tabular mode.

Tutorial Scenario

This tutorial is based on Adventure Works Cycles, a fictitious company. Adventure Works Cycles is a large, multinational manufacturing company that produces and distributes metal and composite bicycles to commercial markets in North America, Europe, and Asia. The headquarters for Adventure Works Cycles is in Bothell, Washington, where the company employs 500 workers. Additionally, Adventure Works Cycles employs several regional sales teams throughout its market base.

To better support the data analysis needs of sales and marketing teams and of senior management, you are tasked with creating a tabular model for users to analyze internet sales data in the AdventureWorksDW2012 sample database.

In order to complete the tutorial, and the Adventure Works Internet Sales tabular model, you must complete a number of lessons. Within each lesson are a number of tasks; completing each task in order is necessary for completing the lesson. While in a particular lesson there may be several tasks that accomplish a similar outcome; however, how you complete each task is slightly different. This is to show that there is often more than one way to complete a particular task, and to challenge you by using skills you learned in previous tasks.

The purpose of the lessons is to guide you through authoring a basic tabular model running in In-Memory mode by using many of the features included in SQL Server Data Tools. Because each lesson builds upon the previous lesson, you should complete the lessons in order. Once you have completed all of the lessons, you will have authored and deployed the Adventure Works Internet Sales sample tabular model on an Analysis Services server.

After you complete the tutorial, you can add to your model, or create additional models using the same AdventureWorksDW2012 sample database. The database includes an extensive collection of tables and data that can apply to a wide range of sample models.

Prerequisites

In order to complete this tutorial, you must have the following prerequisites installed:

• SQL Server 2012 Analysis Services instance running in Tabular mode.

• SQL Server Data Tools (SSDT) - installed as part of SQL Server 2012.

• AdventureWorksDW2012 sample database. This sample database includes the data necessary to complete this tutorial. To download the sample database, see https://go.microsoft.com/fwlink/?LinkID=221770.

• Microsoft Excel 2003 or later (for use with the Analyze in Excel feature in lesson 11)

Lessons

This tutorial includes the following lessons:

Supplemental Lessons

This tutorial also includes Supplemental Lessons. Topics in this section are not required to complete the tutorial, but can be helpful in better understanding advanced tabular model authoring features.

This tutorial includes the following supplemental lessons:

Next Step

To begin the tutorial, continue to the first lesson: Lesson 1: Create a New Tabular Model Project.