SQL Server End-to-End Business Intelligence Workshop


Learn best practices and skills required to successfully design, build, deploy, and operate a business intelligence solution using SQL Server Integration Services, Analysis Services and Reporting Services.


Duration: 40 hours

Level: 200

What does this course cover?

Business intelligence solutions provide the infrastructure that enables users at all levels of a business to make better decisions based on more accurate and up-to-date information. This workshop focuses on teaching IT professionals the best practices and skills required to successfully design, build, deploy, and operate a business intelligence solution using SQL Server Integration Services, Analysis Services and Reporting Services.


Who is this course designed for?

This course is designed for IT professionals who are interesting in learning how to implement Business Intelligence solutions on the Microsoft SQL Server platform. Basic knowledge of BI concepts is assumed and some experience with SQL Server is required.

Pre-requisites: What do you need to know?

Before attending this course, it is recommended that students have the following skills:

  • Basic knowledge of Business Intelligence
  • Knowledge of relational database systems
  • Experience with SQL Server database
  • Basic knowledge of windows security

”Very knowledgeable and good energy. Kept the class moving and engaged”

WHEN and WHERE is this course running?

All our courses can be offered as a private delivery and tailored for your team's specific needs

Course outline

Module 01: Introduction to Business Intelligence

This module introduces BI and components of a BI solution and then discusses the various products that are available from Microsoft for implementing a BI solution and how those technologies fit into the BI component stack. We will then introduce in more detail the technologies that are part of the SQL Server BI Platform and also highlight some of the major changes in each of those technologies in 2016. We will also look at the various tools that are available for developing and managing the technologies.

  • BI Practice
  • Components of a BI solutions
  • MS BI stack
  • Intro to the MS SQL BI Platform
  • Highlight some major changes between previous verisions.
  • Client Tools for development and management – also highlight differences from previous SQL Server Architectures
Module 02: Preparing Data for Analytics

This module provides an introduction to dimensional modeling and lays the foundation for more detailed topics covered over the next few modules. We will cover Dimensional Modeling concepts including Fact and Dimension tables and how to implement a dimensional model within your organization. We will also address the needs for implementing dimensional models and talk about Data Warehousing and Data Marts. We will also briefly discuss processes for loading data into these dimensional tables.

  • The Need
  • Dimensional Modeling
  • Dimensional Modeling Concepts

LAB: Documenting a dimensional model

Module 03: Implementing workflows using SQL Server Integration Services

This module provides an introduction to the components of SQL Server Integration Services and the package. It also highlights changes in 2012 for in the control flow environment and explains how to use the components and features of SSIS to build process workflows.

  • The Need
  • SSIS Components
  • Package Components
  • Changes in SQL Server 2008 – Reasons for upgrade
  • Data Sources and Connections
  • Tasks, Containers and Precedence Constraints
  • Highlight: Script Task and VSTA
  • Variables
  • SSIS Expressions

LAB: Implementing simple workflows including loops and complex constraintsMemory Architecture and Memory Pools

Module 04: Processing data using SQL Server Integration Services

This module highlights the various components of a data flow environment and then explains how to use these components and features to implement ETL processes. This module does not go into great details around the internals of SSIS Data Flow.

  • The Need
  • Data Flow Components: Source and destination adapters, transformations, data flow
  • Building data flows
  • Changes in SQL Server 2012 – Reasons for upgrade
  • Highlight: ADO.Net data provider

LAB: Building a simple data flow including a custom source for Date Process

Module 05: Loading a Dimensional Model using SQL Server Integration Services

This module covers the specific components of the data flow that are used to implement ETL processes to load  dimension and fact tables.

  • The Need
  • Slowly Changing Dimension Concept
  • Highlight: Slowly Changing Dimension Transform
  • Loading Date Dimension
  • Load Fact Tables
  • Highlight: Lookup transform and persistent cache

LAB: Load dimension and fact tables including dimDateDisk Subsystem Terminology and Architecture

Module 06: Deploying SQL Server Integration Services Packages

This module covers deployment and scheduling of SSIS packages.

  • The Need
  • Deployment locations
  • Deployment options
  • Scheduling a package
Module 07: Introduction to the Unified Dimensional Model (UDM)
  • The Need
  • Introduction to OLAP Fundamentals
  • The UDM and Benefits
  • Analysis Services Fundamentals
  • Basic Components of the UDM: Data Sources, Data Source Views, Cubes, Dimensions
  • Components of a SQL Server Analysis Services Database
  • Building a basic cube
  • Deployment and processingTransaction Log Architecture
Module 08: Customizing the UDM
  • The Need
  • Introduction to the SSAS designer
  • New design features in SSAS
  • Dimension customization: Dimension properties, attributes and hierarchies (Customization scenario/how-to approach)
  • Cube customization: Cubes, measure groups, measures (Customization scenario/howto approach)
  • Advanced SSAS database components: Dimension relationships, Calculations, KPI’s
  • Review of the best practices
  • Highlight: Designer improvements
  • Highlight: Best Practices Warnings
  • Highlight: Attribute Relationship Designer
Module 09: Deployment, Management and Optimization

This module discusses SSAS Database deployment and management including storage modes, partitioning, proactive caching, performance tuning and optimization – including the new aggregation designer. We will also provide a very high-level look at the enhancements in SSAS and reasons for upgrade.

  • Storage Modes
  • Scale-out deployment options
  • Data processing optimization techniques
  • Aggregation design
  • Backup Improvements
  • Scaling out with Shared DatabasesWindows Performance Monitor
Module 10: Managing SSAS using SSIS

This module explains the features of SQL Server Integration Services that can be used to
interact with data in SQL Server Analysis Services and also manage SQL Server Analysis Services

  • The Need
  • Accessing data from the UDM
  • Interacting with and managing SSAS objects
  • Data Processing options
Module 11: Introduction to Data Mining

This module introduces Data Mining and provides an understanding of the business uses of data mining and also provides an overview of the data mining process.

  • The Need
  • Data Mining Process
  • Data Mining Algorithms
  • Changes in SQL Server 2016
  • Data Mining add-ins in Excel Locks
Module 12: Introduction to SQL Server Reporting Services

This module will introduce the Reporting Services, the architecture and components of SSRS. Additionally, we will look at the components of a Report and understand the features and functionality including new features in SSRS 2016.

  • The Need
  • SSRS 2016
  • SSRS Architecture and Components
  • What’s new in SSRS 2016 Architecture
  • Report Components
  • What’s new in SSRS 2016 Report ComponentsNative Data Types
Module 13: Designing Reports in SQL Server Reporting Services

This module will familiarize people with the design tools for designing reports and explain features available to customize report look and functionality.

  • The Need
  • SSRS 2016 Designer
  • What’s New in SSRS 2016 for Report design
  • Report Queries and Parameters
  • Report layout and customization
  • Report Interactivity
  • Charts and Graphs
  • Extending Report Functionality
Module 14: Publishing, Accessing and Managing Reports

This module looks at mechanisms for publishing and accessing the published reports as well as looks at new tools in 2016 to manage reports.

  • The Need
  • Report deployment options
  • Report deployment architectural scenarios (Scalable deployment)
  • Mechanisms for accessing reports
  • Processing and rendering architecture
  • Memory usage management
  • What’s New in SSRS 2016 Rendering Architecture
Module 15: Advanced Reporting Against the UDM

This topic will cover some other key technologies that can be used by end-users to report  against this data. We will see how to expose the UDM to end users for self service reporting, use pivot tables in Excel to report against the UDM and also highlight some features in Excel that make data analysis easier for the end user. Finally we will briefly discuss other technologies for reporting against the UDM including ProClarity, Excel Services and SharePoint Server, Business Scorecard Manager and Performance Point Server.

  • Report Designer as an end-user tool
  • Creating and Deploying Report Models
  • Ad-hoc Reporting using Report Builder Client
  • Reporting with Microsoft Office Excel
  • Other Reporting and Delivery Mechanisms

WHEN and WHERE is this course running next?

This course may be scheduled in more than one region. Please check availability in your country.