Developing tabular models on Microsoft SQL Server

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