Tabular Modeling (Adventure Works Tutorial)
This tutorial provides lessons on how to create a SQL Server 2014 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 AdventureWorksDW 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.
Note
This tutorial does not provide lessons or information about managing a deployed tabular model database by using SQL Server Management Studio, or using a reporting client application to connect to a deployed model to browse model data.
Prerequisites
In order to complete this tutorial, you must have the following prerequisites installed:
SQL Server 2014 Analysis Services instance running in Tabular mode.
SQL Server Data Tools (SSDT).
AdventureWorksDW sample database. This sample database includes the data necessary to complete this tutorial. To download the sample database, see https://github.com/microsoft/sql-server-samples/releases/tag/adventureworks.
Microsoft Excel 2003 or later (for use with the Analyze in Excel feature in lesson 11)
Lessons
This tutorial includes the following lessons:
| Lesson | Estimated time to complete |
|---|---|
| Lesson 1: Create a New Tabular Model Project | 10 minutes |
| Lesson 2: Add Data | 20 minutes |
| Lesson 3: Rename Columns | 20 minutes |
| Lesson 4: Mark as Date Table | 3 minutes |
| Lesson 5: Create Relationships | 10 minutes |
| Lesson 6: Create Calculated Columns | 15 minutes |
| Lesson 7: Create Measures | 30 minutes |
| Lesson 8: Create Key Performance Indicators | 15 minutes |
| Lesson 9: Create Perspectives | 5 minutes |
| Lesson 10: Create Hierarchies | 20 minutes |
| Lesson 11: Create Partitions | 15 minutes |
| Lesson 12: Create Roles | 15 minutes |
| Lesson 13: Analyze in Excel | 20 minutes |
| Lesson 14: Deploy | 5 minutes |
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:
| Lesson | Estimated time to complete |
|---|---|
| Implement Dynamic Security by Using Row Filters | 30 minutes |
| Configure Reporting Properties for Power View ReportsConfigure Reporting Properties for Power View Reports | 30 minutes |
Next Step
To begin the tutorial, continue to the first lesson: Lesson 1: Create a New Tabular Model Project.