Creating Business Intelligence Solutions with Microsoft SQL Server

Introduction:

In this five-day, hands-on instructor-led training seminar on Microsoft SQL Server Business Intelligence (BI), participants will learn to harness the Microsoft data platform for self-service BI and big data analytics. The course will guide participants through building IT solutions with self-service BI models, demonstrating the benefits of self-service BI, and creating data models and reports using Office 365 Power BI, Excel, SharePoint Server, and SQL Server Reporting Services (SSRS). Additionally, it covers big data analysis using Windows Azure HDInsight.

Objectives:

At the end of the Microsoft SQL Server Business Intelligence (BI) course, participants will be able to:

• Explain the distinguishing features and advantages of self-service BI.
• Implement self-service reporting solutions using SQL Server Reporting Services.
• Build analytical data models using Excel PowerPivot.
• Use Power Query in Microsoft Excel to import data into data models.
• Create interactive data visualizations using Excel Power View.
• Generate visualizations of geographic data with Excel Power Maps.
• Develop and deploy self-service BI solutions using Microsoft SharePoint Server.
• Search for and utilize open datasets from the Azure Marketplace.
• Implement cloud-based self-service BI solutions using Office 365 Power BI.
• Set up and manage a Windows Azure HDInsight cluster for big data processing.
• Analyze large datasets using Pig and Hive in Windows Azure HDInsight.
• Facilitate online BI and manage large-scale data workflows.

Training Methodology:

• Instructor-Led Interactive Training
• Laboratories
• Case Studies
• Group Exercises
• Demonstration Activities
• Forums
• Supervised Exercises

Course Outline:

Unit 1: Developing an Active BI Solution

• Components of BI Solution
• Project BI Solution Activities
• Understanding Microsoft BI Platform

Unit 2: Designing the SQL Business Intelligence Environment

• Overview of BI Environment
• Estimating Data Warehouse Server Requirements

Unit 3: Data Warehousing

• Introduction to Data Warehouse Architecture
• Creating Dimension Tables

Unit 4: Creation of an ETL Project

• Overview of ETL
• Data Extraction
• Data Change Detection
• Data Loading Procedures

Unit 5: Building OLAP Structures

• Concept of OLAP Analytical Data Models
• OLAP Structures Design

Unit 6: Outlining the Monitoring System

• BI Delivery Focus
• Typical Reporting Requirements
• Reporting Solution Selection

Unit 7: Outlining the Reporting Mechanism

• Reporting System Framing Techniques
• Report Design
• Ensuring Report Consistency

Unit 8: Creating an Excel-Based Reporting Solution

• Reporting and Data Analysis with Excel
• Excel PowerPivot
• Excel Power View

Unit 9: Preparing to Implement a BI Solution on SharePoint Server

• Introduction to SharePoint Server for BI
• Security Preparation for BI Solutions on SharePoint Server
• Reporting Services Configuration Planning
• PowerPivot Configuration Preparation
• PerformancePoint Services Preparation

Unit 10: Controlling and Improving BI Solutions

• BI Monitoring Aspects
• Data Warehouse Monitoring and Optimization
• Monitoring and Reporting Analysis Services
• Reporting Services Monitoring and Optimization

Unit 11: Employing a BI Solution

• Overview of BI Management
• ETL Operations
• Data Warehouse Activities
• Analysis Services Operations
• Report Services Operations