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.
- Stand-alone versus SharePoint integrated
- Report authoring: Report Designer versus Report Builder
- Push Delivery
- Power View reports
Big Data: Microsoft Azure Cortana Intelligence Suite
Cortana Intelligence 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 Storage and Azure Data Lake
- Azure SQL Data Warehouse: using T-SQL on terabytes of data
- Processing streams of data with Event Hub and Stream Analytics
- 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, a set of free tools which you can run either in Excel or in a dedicated product: Power BI Desktop. In this module we see how to use these tools to load and model data.
- Power BI Desktop versus Power BI in Excel
- Loading data with Power Query
- Cleansing data in Power BI
- Data modeling with Power Pivot
- A brief glance at DAX
Power BI Reporting
We end this training by taking a look at how to create interactive reports in Power BI, how to use them in the cloud solution, and how to deal with geographical data.
- Power View
- Custom visualizations in Power View
- The Power BI cloud service
R in the Microsoft stack
R is a statistical language developed in the open source world. Microsoft embraced this technology and embeds this in SQL Server, Power BI as well as their Azure cloud solutions.
In this module we take a brief look at how R can be used within the technologies discussed so far.
- R introduction
- Selecting an R distribution
- Microsoft R Server
- R 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. The Microsoft Cortana Intelligence suite 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.
This course does not require any knowledge regarding business intelligence.