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.
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
- 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