Skip to content

tabular-modeling-adventure-works-tutorial.md

Latest commit

 

History

History
108 lines (74 loc) · 7.45 KB

tabular-modeling-adventure-works-tutorial.md

File metadata and controls

108 lines (74 loc) · 7.45 KB
title Tabular Modeling (Adventure Works Tutorial) | Microsoft Docs
ms.custom
ms.date 04/19/2017
ms.prod sql-server-2016
ms.reviewer
ms.suite
ms.technology
analysis-services
ms.tgt_pltfrm
ms.topic get-started-article
applies_to
SQL Server 2016
keywords
Analysis Services
Tabular Model
Tutorial
SSAS
ms.assetid 140d0b43-9455-4907-9827-16564a904268
caps.latest.revision 40
author Minewiskan
ms.author owend
manager erikre

Tabular Modeling (Adventure Works Tutorial)

[!INCLUDEssas-appliesto-sql2016-later-aas]

This tutorial provides lessons on how to create an Analysis Services tabular model at the 1200 compatibility level by using SQL Server Data Tools (SSDT), and deploy your model to an Analysis Services server on-premises or in Azure.

If you are using SQL Server 2017 or Azure Analysis Services, and you want to create your model at the 1400 compatibility level, use the Azure Analysis Services - Adventure Works tutorial. This updated version uses the new, modern Get Data feature to connect and import source data, and uses the M language to configure partitions.

What you'll learn

  • How to create a new tabular model project in SSDT.

  • 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 an Analysis Services server on-premises or in Azure.

Scenario

This tutorial is based on Adventure Works Cycles, a fictitious company. Adventure Works is a large, multinational manufacturing company that produces and distributes metal and composite bicycles to commercial markets in North America, Europe, and Asia. With headquarters in Bothell, Washington, the company employs 500 workers. Additionally, Adventure Works 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, but 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've 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 SSDT. Because each lesson builds upon the previous lesson, you should complete the lessons in order. Once you've completed all of the lessons, you will have authored and deployed the Adventure Works Internet Sales sample tabular model on an Analysis Services server.

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'll need the following prerequisites:

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: Mark as Date Table 3 minutes
Lesson 4: Create Relationships 10 minutes
Lesson 5: Create Calculated Columns 15 minutes
Lesson 6: Create Measures 30 minutes
Lesson 7: Create Key Performance Indicators 15 minutes
Lesson 8: Create Perspectives 5 minutes
Lesson 9: Create Hierarchies 20 minutes
Lesson 10: Create Partitions 15 minutes
Lesson 11: Create Roles 15 minutes
Lesson 12: Analyze in Excel 20 minutes
Lesson 13: 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.

Lesson Estimated time to complete
Implement Dynamic Security by Using Row Filters 30 minutes

Next step

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