HemSök efter kurserDeveloping tabular models on Microsoft SQL Server

Developing tabular models on Microsoft SQL Server


2 dagar

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