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
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
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.