HemSök efter kurserDeveloping tabular models on Microsoft SQL Server

Developing tabular models on Microsoft SQL Server


2 dagar

27650 kr

This three-day instructor-led course provides students who are looking to produce SQL Server Analysis Services tabular models.

Target Audience

The primary audience for this course is individuals who will administer and maintain a SQL Server Analysis Services tabular model.

After completing this course, students will be able to:

  • Design a SSAS tabular model using a data warehouse
  • Import and direct query tables
  • Cleanse and configure columns and rows
  • Manage dimension tables
  • Manage measures and KPIs
  • Manage presentation level options – translations, perspectives, hierarchies
  • Implement partitions
  • Use DAX
  • Implement security

In addition to their professional experience, students who attend this training should already have the following technical knowledge:


  • Basic knowledge of the Microsoft Windows operating system and its core functionality.
  • Working knowledge of Transact-SQL. (equivalent knowledge to QATSQL and QATSQLPLUS)

Nice to have:

  • Working knowledge of relational databases.
  • Some experience with database design.

Module 1 : Business Intelligence overview

This module introduces the main terms used within business intelligence and analysis services tabular models.


  • Defining business intelligence
  • Common information problems
  • Analytical systems
  • Delivery of business intelligence
  • Requirements analysis
  • LAB A: Review and record SQL Server setup

Module 2: Accessing and loading data

This module is aimed at accessing and loading data in the SSAS tabular model. The data can be imported or direct queried.


  • Accessing and loading data
  • Loading data
  • Storage modes
  • Connection modes
  • LAB A: Creating SSAS tabular project
  • LAB B: Load tables

Module 3: Data preparation

This module explores Power Query use within the datasets. The module covers working on the datasets to cleanse columns, reduce rows, and merging datasets.


  • SSAS data types
  • Cleansing columns
  • Reduce columns and rows
  • Calculations in Power Query
  • Merging and appending datasets
  • LAB A: Work on columns
  • LAB B: Work on rows
  • LAB C: Merging tables

Module 4: Modelling tables and columns

This module covers the design of the presentation level to be used by tools like Power BI and Excel PowerPivot.


  • Table properties
  • Data table
  • Column properties
  • Relationships
  • Partitions
  • Hierarchies
  • LAB A: Dates table and column properties
  • LAB B: Relationships
  • LAB C: Partitions
  • LAB D: Hierarchies

Module 5: Calculations

This module covers the calculations using DAX. The items covered are columns, measures, KPIs, table and calculation groups.


  • Implementing DAX in calculations
  • Implement columns, measures and KPIs
  • Implement calculation groups
  • Implement calculated tables
  • LAB A: Calculated columns
  • LAB B: Measures
  • LAB C: Calculated tables
  • LAB D: Calculation groups
  • LAB E: KPIs

Module 6: Finalising models

This module covers the ability to create and maintain additional terms such as translations and perspectives.


  • Working on translations
  • Working on perspectives
  • LAB A: Translations
  • LAB B: Perspectives

Module 7: Securing models

This module two tools to implement security – row level security and SSAS model security.


  • Analysis services security with model security
  • Role-based security with SQL direct query sources
  • LAB A: SSAS security
  • LAB B: Role-based security
  • LAB C: Role using a table

Module 8: Deployment and Processing

This module covers the deployment and updating of models into both SQL Server tabular models on-premises and Azure. The processing of models is also covered.


  • Deployment options
  • Processing options
  • LAB A: Deployment to SSAS
  • LAB B: Processing via SSMS