EtusivuHae koulutuksiaDeveloping tabular models on Microsoft SQL Server

Developing tabular models on Microsoft SQL Server




2 päivää


2104 €

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: Essential:

  • 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. Lessons:

  • 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. Lessons:

  • 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. Lessons:

  • 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. Lessons:

  • 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. Lessons:

  • 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. Lessons:

  • 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. Lessons:

  • 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. Lessons:

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