Strategic Business IT Planning, Deployment & Management Courses

Designing Business Intelligence Solutions with Microsoft SQL Server


Particpants will learn how to use the Microsoft data platform to develop self-service business intelligence (BI) and big data analysis solutions during this five-day instructor-led course. The course covers the benefits of self-service BI as well as how to build self-service data models and reports using Microsoft Office 365 Power BI, Microsoft Excel, Microsoft SharePoint Server, and Microsoft SQL Server Reporting Services. The next section of the course covers how to analyze big data with Windows Azure HDInsight.

Targeted Groups

Database and business intelligence (BI) specialists who are familiar with data warehouses and enterprise BI solutions created with SQL Server technologies make up the course's main target audience. Attending this course will also be beneficial for seasoned data analysts who wish to understand how to use Microsoft technologies for self-service analysis and reporting.

Targeted Competencies

  • Understanding of data modeling and data warehousing principles.
  • Knowledge with Microsoft SharePoint Server 2013 and Excel 2013.

Course Objectives

  • Identify the main attributes and advantages of self-service BI.
  • To implement a self-service reporting solution, use SQL Server Reporting Services.
  • Excel's PowerPivot can be used to build analytical data models.
  • To import data into a data model, using Power Query in Microsoft Excel.
  • Microsoft Excel's Power View may be used to make interactive data visualizations.
  • Excel's Power Map may be used to create visualizations of geographic data.
  • Collaborative self-service BI solutions can be implemented using Microsoft SharePoint Server.
  • The Windows Azure Marketplace has public data that you may find and use.
  • Cloud-based self-service BI solutions can be implemented using Microsoft Office 365 Power BI.
  • For Big Data analysis, create and use a Windows Azure HDInsight cluster.
  • To analyze huge data in Windows Azure HDInsight, use Pig and Hive.
  • To support self-service BI, create and implement big data workflows.

Course Content

Unit 1: Planning a BI Solution

  • Elements of a BI Solution
  • Planning a BI Project
  • The Microsoft BI Platform

Unit 2: Planning SQL Server Business Intelligence Infrastructure

  • Considerations of BI Infrastructure
  • Planning Data Warehouse Hardware

Unit 3: Designing a Data Warehouse

  • Data Warehouse Design Overview
  • Designing Dimension Tables
  • Design FACT tables

Unit 4: Designing an ETL solution

  • ETL Overview
  • Planning for Data Extraction
  • Planning for Data Transformations
  • Planning for Data Loading

Unit 5: Designing Analytical Data Models

  • Introduction to Analytical data Models
  • Designing Analytical Data Models

Unit 6: Planning a BI Delivery Solution

  • Considerations for BI delivery
  • Common Reporting Scenarios
  • Choosing a Reporting Tool

Unit 7: Designing a Reporting Services Solution

  • Planning a Reporting Solution
  • Designing Reports
  • Planning Report Consistency

Unit 8: Designing an Excel Based reporting Solution

  • Using Excel for Data Reporting and Analysis
  • PowerPivot in Excel
  • Power View in Excel

Unit 9: Planning a SharePoint Server BI Solution

  • Introduction to SharePoint Server as a BI Platform
  • Planning Security for a SharePoint Server BI Solution
  • Planning Reporting Services Configuration
  • Planning PowerPivot Configuration
  • Planning for PerformancePoint Services

Unit 10: Monitoring and Optimizing a BI Solution

  • Overview of BI Monitoring
  • Monitoring and Optimizing the Data Warehouse
  • Monitoring and Analyzing Analysis Services
  • Monitoring and Optimizing Reporting Services

Unit 11: Operating a BI Solution

  • Overview of BI Operations
  • ETL OperationsData Warehouse Operations
  • Analysis Services Operations
  • Reporting Services Operations

Strategic Business IT Planning, Deployment & Management Courses
Designing Business Intelligence Solutions with Microsoft SQL Server (B)


Mercury dynamic schedule is constantly reviewed and updated to ensure that every category is being addressed at least once a month, if not once every week. Please check the training courses listed below and if you do not find the subject you are interested in, email us or give us a call and we will do our best to assist.