Business Intelligence Training Overview
Accelebrate's Advanced Business Intelligence using SSIS, SSAS, and Power BI course teaches learners practical skills for ETL (extracting, transforming, and loading data), data modeling, and data visualization.
Location and Pricing
Accelebrate offers instructor-led enterprise training for groups of 3 or more online or at your site. Most Accelebrate classes can be flexibly scheduled for your group, including delivery in half-day segments across a week or set of weeks. To receive a customized proposal and price quote for private corporate training on-site or online, please contact us.
In addition, some courses are available as live, instructor-led training from one of our partners.
Objectives
- Create ETL Pipelines using SSIS
- Implement multidimensional and tabular data models using SSAS
- Optimize Power BI performance using different models
- Apply Advanced DAX (Data Analysis Expressions) concepts to produce more complex insights from data
- Build and operate Power BI deployment pipelines
- Compare Datasets, Dataflows, and Datamarts in Power BI
Prerequisites
All attendees should have taken the Microsoft Power BI Data Analyst (PL-300) course or have the equivalent knowledge for the Power BI section. Attendees must also be comfortable writing SQL queries, ideally within SQL Server and/or Azure SQL.
Outline
Expand All | Collapse All
SSIS Overview
- Import/Export Wizard
- Exporting Data with the Wizard
- Common Import Concerns
- Quality Checking Imported/Exported Data
Over of Basic Control Flow
- Working with Tasks
- Understanding Precedence Constraints
- Annotating Packages
- Grouping Tasks
- Package and Task Properties
- Connection Managers
- Analysis Services Processing
- Data Profiling Task
- Execute Package Task
- Execute Process Task
- File System Task
- FTP Task
- Script Task Introduction
- Send Mail Task
Data Flow Sources and Destinations
- Data Flow Task
- Data Flow SSIS Toolbox
- Working with Data Sources
- SSIS Data Sources
- Working with Data Destinations
- SSIS Data Destinations
Data Flow Transformations
- Understanding Transformations
- Working with common Transformations
- Configuring Transformations
Understanding Multidimensional Databases
- Understanding SQL Server Analysis Services (SSAS)
- Multidimensional Analysis
- Data Sources and Data Source Views
- KPIs
- Cubes
Introduction to MDX
- MDX Overview
- Calculations in Cube
- Query a Cube using MDX
Understanding the Tabular Data Model
- Comparing TDM to Cubes
- Creating measures
- Creating relationships
- Using DAX
- Creating KPIs
Create and Manage Power BI Dataflows
- Define use cases for dataflows
- Create reusable assets
- Implement best practices
- Comparing Dataflows to Datasets and Datamarts
Advanced DAX in Power BI
- Using IF and SWITCH
- Implementing iterations with ALL, ALLEXCEPT, FILTER, SUMX, AVERAGEX, and similar functions.
- Using Statistical functions, e.g., standard deviation, median, mode, and interquartile range
Creating Advanced Measures using Calculation Groups
- Introduction
- Understand calculation groups
- Explore calculation groups features and usage
- Create calculation groups in a model
Create Paginated Reports
- Introduction to paginated reports
- Get data
- Create a paginated report
- Work with charts on the report
- Publish the report
Optimize Power BI Performance
- Use Performance analyzer
- Troubleshoot DAX performance by using DAX Studio
- Optimize a data model by using Best Practice Analyzer
Choose a Power BI Model Framework
- Describe Power BI model fundamentals
- Determine when to develop an import model
- Determine when to develop a DirectQuery model
- Determine when to develop a composite model
- Choose a model framework
Create and Manage a Power BI Deployment Pipeline
- Understand the deployment process
- Create a deployment pipeline
- Assign a workspace
- Deploy content
- Work with deployment pipelines
Implementing Data Gateway in Power BI
- Understanding Data Gateway
- Configuring Data Gateway in Power BI
- Configuring a shared folder and SQL Server in Data Gateway
Training Materials
All attendees receive a course workbook that includes detailed instructional content and hands-on labs.
Software Requirements
- Microsoft 365, especially Excel
- Power BI Desktop
- Modern web browser such as Chrome or Microsoft Edge (Accelebrate will provide a browser-based connection to a remote desktop environment that includes SQL Server with SSIS and SSRS)
- Optionally, Microsoft Remote Desktop client
