Microsoft Business Intelligence End to End with SQL Server 2016

5 days


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.


Each participant will leave with a recognised certificate

Course Outline

Module 1: Business Intelligence Overview

  • This module will introduce the course concepts and the Microsoft Business Intelligence stack (covering line-of-business systems, Integration Services, the Data Warehouse, Analysis Services, Reporting Services, Power View, SharePoint, PerformancePoint and PowerPivot for Excel).
  • Sections:
  • Define terms and products
  • Business intelligence (BI)
  • Extract Transform and Load (ETL)
  • SQL Server Integration Services (SSIS)
  • SQL Server Analysis Services (SSAS)
  • SQL Server Reporting Services (SSRS)
  • SharePoint
  • BI products
  • Server products and the BI developer's toolset
  • BI Projects
  • High-level plan
  • Scope and phased delivery
  • Evaluating BI requirements
  • Labs:
  • Identify software requirements for solutions
  • Three business requirements that the delegates need to identify which Microsoft products are needed to fulfill requirement; Group exercise

Module 2: Designing Business Intelligence Solution

  • Introduce dimensions and fact tables
  • Discuss dimensions, attributes and hierarchies
  • Discuss star and snowflake schemas, and identify advantages and disadvantages
  • Discuss conformed dimensions
  • Discuss facts, fact tables and granularity
  • Discuss whether to use a staging database
  • Discuss timely delivery of data from source to destination (daily, hourly, real-time)
  • Discuss challenges of changing data
  • Discuss slowly changing dimensions (SCD) and late-arriving dimensions
  • Discuss use of surrogate keys
  • Discuss data warehousing techniques
  • Discuss designing a time dimension
  • References:
  • Ralph Kimball 'Data Warehouse Design'
  • Demonstrations:
  • Design dimensions and facts, introducing different techniques
  • Design a time dimension
  • Design a simple data warehouse: several dimension tables and fact table
  • Identify keys, attributes, hierarchies, requirements for SCDs and facts, stating assumptions made
  • Identify the flow of data from line of business systems to the data warehouse tables

Module 3: Creating the Data Warehouse

  • Table design -choosing appropriate columns and data types
  • Referential integrity with primary and foreign keys
  • Calculations and views
  • Partitioned tables
  • Indexes: clustered and non-clustered
  • Columnstore indexes
  • Maintaining indexes
  • Create partitioned tables (using multiple file groups for the fact table)
  • Create referential integrity
  • Create and maintain indexes
  • Create data warehouse database
  • Create the dimension tables
  • Populate the time dimension
  • Create the fact tables with partitioning
  • Create referential integrity with foreign keys
  • Create indexes on the fact and dimension tables

Module 4: Loading the Data Warehouse

  • SSIS basics: Projects; Packages; Control Flow; Data Flow; Transformations; Data Sources; Data Destinations
  • Project design: Implementing a package hierarchy
  • Creating dynamic packages: Variables, Expressions; Parameters
  • Control flow tasks
  • Data flow tasks
  • Troubleshooting and Error handling
  • Fast-load and table partitioning, using switch and merge
  • Late arriving data
  • Advanced options: Event Handlers; Logging; Checkpoint; Transactions
  • Handling changing data: Change Data Capture (CDC); Slowly changing dimensions
  • Deploying: Project vs. Package deployment; SSIS Catalog, Environments
  • SSIS package basics; Control flow; Data flow; Transformations; Logging; Variables; Expressions; Parameters
  • Error handling, logging, transactions and checkpoints
  • Change data capture (CDC); Slowly changing dimensions (SCDs)
  • Deploying packages to the SSIS catalog
  • Design and create a SSIS package structure
  • Add control and data flow items
  • Create data flow for each dimension table
  • Create data flow for each fact table
  • Manage changing data using slowly changing dimension transformations (SCDs) and Change data capture (CDC)

Module 5: Creating the Analysis Cube using the Multi-Dimensional model

  • Analysis Services Multi-Dimensional object basics: data sources; data source views, dimensions, measure groups, cubes
  • Data sources and Data source views: Friendly names; Relationships; Calculated columns; Named queries;
  • Dimensions: Time; Parent-Child; Multiple table (snowflake); Unary Operator
  • Dimension Attributes: Key values and name values; Attribute relationships
  • Measures and Measure Groups; Data type design; Aggregation functions (Additive / Semi-Additive / Non-Additive)
  • Cubes: Producing the base cube; Dimensions usage and relationships
  • Best practice warnings
  • Processing and testing
  • Demonstrations
  • Create a data source (DS)
  • Create a data source view (DSV); Add named query; Add calculated column (full name, quarter with year, month with year)
  • Create dimensions; Time; Product-> Product Subcategory -> Product Category; Geography; Customers; Resellers
  • Designing dimensions for usability to required design
  • Design cube using dimensions
  • Set properties for measures

Module 6: Enhancing the Multi-Dimensional Analysis cube

  • Enhancing the cube using MDX: introduction to MDX, members, tuples and sets; common MDX functions
  • Methods to enhance the cube: calculated measures; Calculated members; Named sets; Key Performance Indicators (KPIs); Perspectives; Actions.
  • Introduction to MDX
  • Creating calculated measures, calculated members, named sets, key performance indicators (KPIs), perspectives and actions
  • Create calculated measures (totals and averages)
  • Create calculated members
  • Create named sets (top 10 products)
  • Create KPI (sales targets)
  • Create actions (drillthrough)
  • Design perspectives for Internet and Reseller sales departments

Module 7: Creating the Analysis Cube using the Tabular model

  • Analysis Services Tabular model basics: importing and filtering data, managing and visualizing relationships
  • Introduction to DAX
  • Creating the model using calculated columns and measures
  • Enhancing the model: Creating hierarchies, Using Time Intelligence, Key Performance Indicators, Perspectives and Partitions
  • Optimizing the model: In-memory vs. DirectQuery;
  • Testing and deploying
  • Importing and filtering data
  • Using DAX to create calculated columns and measures
  • Creating hierarchies, KPIs and perspectives
  • Create and configure a tabular data model from external data
  • Use DAX expressions to create calculated columns and measures
  • Create hierarchies, KPIs and perspectives according to end-user requirements
  • Testing and deploying the model

Module 8: Creating reports with Reporting Services

  • Introduction to reporting services: Pre-canned vs. Adhoc Reporting; The SSRS platform and its components and tools
  • Reporting Services basics: data sources, data sets and report design
  • Creating reports: tablix data regions; formatting, expressions, grouping, document maps; drilldown, sorting
  • Visualizing data: charts; gauges; maps; sparklines; data bars; indicators; images
  • Linking reports to each other: parameters; actions; subreports
  • Deploying: Standalone SSRS; SharePoint Integrated
  • Create a report using a tablix that shows sales by product category for a particular region (parameterized).
  • Create a report that holds a bar chart showing sales by regions
  • Link bar chart report to tablix report passing the selected region as a parameter.
  • Creating a report to display key performance indicators (KPIs)
  • Create a report to compare sales over time
  • Use gauges to show performance against targets
  • Create a linked reports for sales with drillthroughs to add interactivity

Module 9: Power BI

  • Describe purpose of Power BI and its position in the BI solution
  • Power BI Desktop
  • Importing and shaping data
  • Creating and exploring reports
  • Introduction to slicers
  • Saving Power BI documents
  • Produce a variety of views including charts and grids
  • Demonstrate creating
  • Import, manipulate and present data using Power BI Desktop

Server products and the BI developer's toolset BI Projects

Identify software requirements for solutions Three business requirements that the delegates need to identify which Microsoft products are needed to fulfill requirement; Group exercise

Ralph Kimball 'Data Warehouse Design' Demonstrations:

Thinking about Onsite?

If you need training for 3 or more people, you should ask us about onsite training. Putting aside the obvious location benefit, content can be customised to better meet your business objectives and more can be covered than in a public classroom. It's a cost effective option.

Submit an enquiry from any page on this site, and let us know you are interested in the requirements box, or simply mention it when we contact you.

ITILv3, RESILIA, PRINCE2, PRINCE2 Agile, AgileSHIFT, MSP, M_o_R, P3M3, P3O, MoP, MoV courses on this page are offered by QA, ATO of AXELOS Limited. ITIL, RESILIA, PRINCE2, PRINCE2 Agile, AgileSHIFT, MSP, M_o_R, P3M3, P3O,MoP, MoV are registered trademarks of AXELOS Limited. All rights reserved.

Upcoming Dates

  • GREEN This class is Guaranteed To Run.
  • SPVC - Self-Paced Virtual Class.
  • Click a Date to Enroll.
Course Location Days Cost Date
Midlands Birmingham5 3130 £3130 2019-10-07
Edinburgh Edinburgh5 3130 £3130 2019-10-07
Bristol Bristol5 3130 £3130 2019-10-07
Devon Exeter5 3130 £3130 2019-10-07
Midlands Birmingham5 3130 £3130 2019-10-07
London London5 3130 £3130 2019-10-14
Yorkshire Leeds5 3130 £3130 2019-10-14
London London5 3130 £3130 2019-10-21
Tyne and Wear Newcastle5 3130 £3130 2019-11-11
Midlands Birmingham5 3130 £3130 2019-12-02
Edinburgh Edinburgh5 3130 £3130 2019-12-02
Bristol Bristol5 3130 £3130 2019-12-02
London London5 3130 £3130 2019-12-09
Manchester Manchester5 3130 £3130 2019-12-16
London London5 3130 £3130 2020-01-20
Yorkshire Leeds5 3130 £3130 2020-01-20
London London5 3130 £3130 2020-02-17
London London5 3130 £3130 2020-02-17
Midlands Birmingham5 3130 £3130 2020-02-24