Call Us: +32 2 466 00 16
Email: info@u2u.be
Follow Us:

Microsoft Business Intelligence - Technology Overview

2 days
UBIT
2 days

Upcoming Sessions

Date: currently not scheduled

Format: Classroom

Price: 900€

Subscribe to waiting list

Date: currently not scheduled

Format: Classroom

Price: 900€

Subscribe to waiting list

Interested in a private company training? Request it here.

Business Intelligence in the Microsoft stack

In this introduction we take a brief look at the three options for doing business intelligence in the Microsoft stack: enterprise BI on-premises with SQL Server, self-service BI on-premises with Power BI, and the cloud based options with Azure. In this way we set the scene for the next modules.

  • The need for Business Intelligence
  • Self Service BI versus Enterprise BI

Data Warehousing with SQL Server

What is a data warehouse and why do we need it? This is the main question we want to answer in this module. But we also take a look at SQL Server Data Tools, a free Microsoft solution for developing data warehouses and other databases.

  • What is a data warehouse and why do we need one?
  • Dimensional modeling
  • Star versus snowflake dimensions
  • Fact Tables
  • Slowly Changing Dimensions
  • SQL Server data tools and dacpacs

Implement ETL with Integration Services

Once we have an empty data warehouse we need an ETL (Extract, Transform and Load) tool to pump the data from our operational data sources into the data warehouse. Microsoft SQL Server Integration Services is the enterprise tool to make this possible.

  • Extract, Transform and Load
  • Initial versus incremental ETL
  • Integration Services
  • Data Flow: Sources, Transformations and Destinations
  • Control Flow
  • Automating the ETL

Improve Data Quality

"Garbage in, garbage out!"
Data quality is crucial, and in the SQL Server stack there are two tools to help you clean up dirty data. Data Quality Services let business users build a knowledge base and uses this to automatically clean up data. Master Data Services on the other hand helps business users to directly identify data issues and fix them from Excel or the browser.

  • Data Quality Services Introduction
  • DQS Data Cleansing and Deduplication
  • Master Data Services
  • The MDS Excel plugin

OnLine Analytical Processing (OLAP)

Building reports directly on top of a data warehouse requires some SQL skills, especially when more complex calculations such as year-over-year growth etc. are needed. To simplify report creation a semantical model (often called 'cubes' or OLAP) removes the need to have good SQL skills if you want to do reporting. Microsoft SQL Server has Analysis services on board to create these models.

  • What is OLAP, and why do we need it?
  • Analysis Services Multi-Dimensional cubes
  • Enhancing cubes with calculations, KPIs and Translations
  • Differences between Analysis Services Tabular and Multi-Dimensional
  • Data Mining

Reporting Services

The last product from the SQL Server stack is Reporting Services. Just as with the previous services you will see the basics of how reports are created and consumed.

  • Report authoring: Report Designer versus Report Builder
  • Push Delivery
  • Power View reports

The Microsoft Azure Data Platform

The Microsoft Azure Data Platform is a collection of services in the Microsoft Azure cloud to deal with collecting, storing and analyzing medium to large volumes of data. In this module we take a brief look at each of these services.

  • Storing large amounts of data in the cloud: Azure Blob Storage and Azure Data Lake Storage Gen 2
  • Using Azure Data Factory for ETL
  • Azure SQL Data Warehouse: using T-SQL on terabytes of data
  • Azure Machine Learning

Self-service BI: Power BI

Even with the best enterprise BI solution there will always be a need for business users to deal with custom data sets or modeling requirements. To support these self-service BI solutions, Microsoft released Power BI. Power BI is a set of software services that is composed of the Power BI Desktop app, the Power BI Service and the Power BI mobile apps for iOS and Android. In this module we see how you can use Power BI Desktop to load and model data.

  • Introducing BI Desktop
  • Loading data with Power Query
  • Cleansing data in Power BI
  • Data modeling with Power BI Desktop
  • A brief glance at DAX

Power BI Reporting

We end this training by taking a look at how to create interactive reports and dashboards in Power BI.

  • Introduction to the Power BI Service
  • Creating and working with Reports in Power BI Desktop and the Power BI Service
  • Creating and using Dashboards in Power BI

Business Intelligence (BI) is a hot topic today: In an information driven society, analyzing and reporting upon the data that lives within an enterprise is crucial. Microsoft offers a lot of products that can help you setup your Business Intelligence infrastructure. From analyzing a few 10000 rows of data in Excel up to building a multi-terabyte enterprise-wide data warehouse, it's all possible. In this course, we focus on both self-service BI with Power BI as well as enterprise BI with SQL Server.

But also the story of Big Data and cloud-based BI solutions are touched upon. Microsoft Azure Big Data is a collection of cloud solutions to store, transform and analyze both static and streaming data sets in the Azure cloud.

In this demo-rich course, we briefly demonstrate the capabilities of each of these tools and focus upon their advantages and disadvantages. Student will have a 360-degree view of the Microsoft Business Intelligence offering, which will help them decide which technologies are useful in their context.

This course is intended for project managers, analysts and developers who want to obtain an overview of the Microsoft Business Intelligence stack. This course does not contain hands-on exercises nor does it require any prior knowledge regarding business intelligence.

© 2020 U2U All rights reserved.