Multidimensional Modeling (Adventure Works Tutorial)
Applies to: SQL Server Analysis Services Azure Analysis Services Power BI Premium
Welcome to the SQL Server Analysis Services Tutorial. This tutorial describes how to use SQL Server Data Tools to develop and deploy an SQL Server Analysis Services project, using the fictitious company Adventure Works Cycles for all examples.
What you learn
In this tutorial, you will learn the following:
How to define data sources, data source views, dimensions, attributes, attribute relationships, hierarchies, and cubes in an SQL Server Analysis Services project within SQL Server Data Tools.
How to view cube and dimension data by deploying the SQL Server Analysis Services project to an instance of SQL Server Analysis Services, and how to then process the deployed objects to populate them with data from the underlying data source.
How to modify the measures, dimensions, hierarchies, attributes, and measure groups in the SQL Server Analysis Services project, and how to then deploy the incremental changes to the deployed cube on the development server.
How to define calculations, Key Performance Indicators (KPIs), actions, perspectives, translations, and security roles within a cube.
A scenario description accompanies this tutorial so that you can better understand the context for these lessons. For more information, see Analysis Services Tutorial Scenario.
You will need sample data, sample project files, and software to complete all of the lessons in this tutorial. For instructions on how to find and install the prerequisites for this tutorial, see Install Sample Data and Projects for the Analysis Services Multidimensional Modeling Tutorial.
Additionally, the following permissions must be in place to successfully complete this tutorial:
You must be a member of the Administrators local group on the SQL Server Analysis Services computer or be a member of the server administration role in the instance of SQL Server Analysis Services.
You must have Read permissions in the AdventureWorksDW sample database. This sample database is valid for the SQL Server 2019 and later release.
This tutorial includes the following lessons.
|Lesson||Estimated time to complete|
|Lesson 1: Defining a Data Source View within an Analysis Services Project||15 minutes|
|Lesson 2: Defining and Deploying a Cube||30 minutes|
|Lesson 3: Modifying Measures, Attributes and Hierarchies||45 minutes|
|Lesson 4: Defining Advanced Attribute and Dimension Properties||120 minutes|
|Lesson 5: Defining Relationships Between Dimensions and Measure Groups||45 minutes|
|Lesson 6: Defining Calculations||45 minutes|
|Lesson 7: Defining Key Performance Indicators (KPIs)||30 minutes|
|Lesson 8: Defining Actions||30 minutes|
|Lesson 9: Defining Perspectives and Translations||30 minutes|
|Lesson 10: Defining Administrative Roles||15 minutes|
The cube database that you will create in this tutorial is a simplified version of the SQL Server Analysis Services multidimensional model project that is part of the Adventure Works sample databases available for download on GitHub. The tutorial version of the Adventure Works multidimensional database is simplified to bring greater focus to the specific skills that you will want to learn right away. After you complete the tutorial, consider exploring the multidimensional model project on your own to further your understanding of SQL Server Analysis Services multidimensional modeling.
To begin the tutorial, continue to the first lesson: Lesson 1: Defining a Data Source View within an Analysis Services Project.