SQL Server Business Intelligence Core Skills (5 days)


There has never been a better time to have skills in BI (Business Intelligence) and the Microsoft BI platform is well placed in the market. This course is designed to take you through the most important components of the Microsoft BI platform, with just the right amount of depth, within five days.

The course is most suitable for developers that need to learn to work with the Microsoft BI platform or for existing SQL Server developers and DBAs that want to cross-train into BI work. The course can also help business analysts and project managers that need a solid understanding of the technologies involved. While not essential to complete the labs (as full detailed instructions are provided), some knowledge of T-SQL would be helpful.

The course starts by looking at the final result with reporting. The course covers how to build and secure reports using SQL Server Reporting Services and how end-users can make use of Report Builder to create their own reports. On the second day, the course looks at how the Power BI tools (such as Power Pivot, Power View, Power Query, Power Map, Power Q&A, and Power BI Sites) can provide self-service BI options in conjunction with Excel and/or Sharepoint. On the third day, the course looks at creating an underlying data model that is suitable for supporting the reports, using SQL Server Analysis Services. Both the multi-dimensional and tabular data models are discussed. The fourth day shows how the SQL Server Integration tools can be used for ETL (Extract-Transform-Load) processing. The final day covers core concepts in dimensional modelling and how SSIS can be used to populate these models.

While our courses are based on SQL Server 2017, we realise that many customers are not yet running that version. Rather than provide you a course that only covers a single version of SQL Server, our courses are designed to be relevant (wherever possible) to a range of SQL Server versions including SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014 and SQL Server 2016. Where there are differences between versions, the differences are clearly highlighted.

 CITY DATES COURSE PRICE EARLY BIRD PRICE
On-demand Please contact us

 



DAY 1 RS201 INTRODUCING SERVER-BASED REPORTING SERVICES
Server-based Reporting Fundamentals
Creating Reports
Common Report Items
Deploying Reports
LAB: Creating and Deploying Reports
DAY 1 RS202 WORKING WITH COMMON REPORT ELEMENTS AND CREATING DYNAMIC REPORTS
Additional Common Report Elements
Creating Dynamic Reports Using Parameters
Creating Dynamic Reports Through Links and Document Maps
LAB: Creating Dynamic Reports
DAY 1 RS203 MANAGING REPORT EXECUTION AND SECURITY
Extending Reports with Custom Logic
Reporting from Analysis Services
Optimising Report Execution
Securing Report Server Items and Data
Delivering Reports with Subscriptions
LAB: Managing Report Execution and Security
DAY 1 RS204 END-USER REPORTING WITH REPORT BUILDER
Introduction to Report Builder
Creating Map-Based Reports
Data-Feeds as Data Sources
LAB: Report Builder Self-service Reporting
DAY 2 PB201 OVERVIEW OF POWER BI
Why Implement a BI Solution?
Introduction to Power BI
Earlier Versions of Power BI
Current Power BI Toolset
LAB: Installing and Configuring Power BI Tools
DAY 2 PB202 DISCOVERING AND PREPARING DATA WITH POWER QUERY IN POWER BI DESKTOP
Using Content Packs and Apps
Acquiring Data using Power Query
Transforming Data using Power Query
Managing Queries and Data Sources
LAB: Discovering and Preparing Data with Power Query in Power BI Desktop
DAY 2 PB203 CREATING AND ENHANCING DATA MODELS USING POWER PIVOT IN POWER BI DESKTOP
Creating a Power Pivot Data Model
Adding Measures and Calculations to Power Pivot Data Models using DAX
Enhancing a Power Pivot Data Model
LAB: Creating and Enhancing Data Models using Power Pivot in Power BI Desktop
DAY 2 PB204 CREATING AND QUERYING DASHBOARDS WITH THE POWER BI SITE AND POWER Q&A
Working with the PowerBI.com Site
Creating Datasets, Reports, and Dashboards
Interacting with Data Using Power Q&A
Accessing On-Premises Data via Gateways

LAB: Enhancing Power Pivot Data Models

DAY 3 AS201 INTRODUCTION TO SQL SERVER ANALYSIS SERVICES
Introduction to Analysis Services
OLAP and Multi-dimensional Analysis vs Tabular Data Models
Building Analysis Services Cubes
LAB: Creating a Cube
DAY 3 AS202 ENHANCING A MULTI-DIMENSIONAL CUBE DESIGN
Enhancing Cube Dimensions and Forming Hierarchies
Adding MDX Calculations, Key Performance Indicators (KPIs) and Actions
Implementing Perspectives, Translations and Cube Write-back
LAB: Enhancing an OLAP Cube
DAY 3 AS203 CREATING AND ENHANCING A TABULAR DATA MODEL
Creating and Importing Tabular Data Models
Adding Calculated Columns and Measures with Data Analysis Expressions (DAX)
Implementing Hierarchies and Perspectives
Implementing Roles for Security
LAB: Creating and Enhancing a Tabular Data Model
DAY 3 AS204 PROCESSING, AGGREGATIONS AND DATA MINING
Processing Multi-dimensional Cubes and Deployed Tabular Data Models
Improving Performance Through Aggregations
Introduction to SQL Server Analysis Services Data Mining
LAB: Processing, Aggregations and Data Mining
DAY 4 IS201 OVERVIEW OF SQL SERVER INTEGRATION SERVICES
Integration Services Features Overview
Working with SQL Server BI Tooling
Creating SSIS Projects and Packages
LAB: Creating SSIS Projects and Packages
DAY 4 IS202 WORKING WITH SSIS CONTROL FLOW
Introduction to Control Flow Tasks and Precedence Constraints
Working with Containers
Passing Information Between Tasks with Variables and Expressions
LAB: Working with SSIS Control Flow
DAY 4 IS203 WORKING WITH SSIS DATA FLOW
Introduction to Data Flow Data Sources, Destinations and Assistants
Transforming Data
Debugging Using Data Viewers and Breakpoints
LAB: Working with SSIS Data Flow
DAY 4 IS204 SSIS CONFIGURATION, DEPLOYMENT, SCHEDULING AND LOGGING
Project and Package Configurations
Deploying Projects or Packages
Scheduling Package Execution
Logging Package Activity
LAB: SSIS Configurations, Deployment, Scheduling and Logging
DAY 5 IS301 SSIS CASE STUDY: DIMENSIONAL MODELLING CORE CONCEPTS
Why Implement Dimensional Models?
Fact Table Core Concepts
Dimension Table Core Concepts
Profiling Incoming Data
LAB: Dimensional Modelling Core Concepts
DAY 5 IS302 LOADING DIMENSION TABLES
Issues with Loading Dimension Tables
Implementing Slowly-Changing Dimensions
Implementing Inferred Members
Useful Additional Transformations
LAB: Loading Dimension Tables
DAY 5 IS303 LOADING FACT TABLES
Issues with Loading Fact Tables
Working with Granularity and Calculations
Useful Additional Transformations
LAB: Loading Fact Tables
DAY 5 IS304 DESIGNING FOR RELIABILITY AND PERFORMANCE
Designing for Reliability
Improving SSIS Package Performance
Incremental Data Loads
LAB: Designing for Reliability and Performance