Duration: 1 day

Overview

Web Age's Advanced Business Intelligence using SSIS, SSAS, and Power BI course teaches attendees how to  create and manage complex analytical solutions through data modeling and data visualization.

Objectives

  • Master ETL techniques using SSIS
  • Construct multidimensional and tabular data models with SSAS
  • Optimize Power BI performance with different models
  • Harness advanced DAX concepts for deeper insights
  • 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. Attendees must also be comfortable writing SQL queries, ideally within SQL Server and/or Azure SQL.

Outline for Advanced Business Intelligence using SSIS, SSAS, and Power BI Training

  • Integration Services
    • SSIS Overview
      • Import/Export Wizard
      • Exporting Data with the Wizard
      • Common Import Concerns
      • Quality Checking Imported/Exported Data
    • Overview 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
  • Analysis Services
    • 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
  • Power BI
    • 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