What you will learn in this course

Essential Microsoft Business Intelligence provides hands-on training for Business Intelligence (BI) Professionals. Discover how to implement all of the core products in the Microsoft Business Intelligence Stack to create the most powerful BI tool currently available to solve the most complex business challenges providing maximum value to your business users and giving them the advantage in today's highly competitive business world. Explore everything from SharePoint 2010 to PowerPivot to SQL Server Analysis Services.

Find answers to these questions and more:

  • What are the 10 essential techniques to improve the performance of my queries?
  • How can I utilize a star schema to make reporting lightning fast?
  • What are the key ways to write ETL using SQL Server Integration Services for the fastest processing time?
  • When do PowerPivot capabilities outperform SQL Server Analysis Services?
  • What's the most powerful way to use SQL Server Reporting Services utilizing a reporting framework?
  • How can SQL Server Analysis Services scale to meet the demanding needs of my business users?
  • When should I implement MDX querying and when should I avoid it?
  • What features of SharePoint are valuable in Business Intelligence projects and how do I integrate them?
  • How do I get the maximum benefit out of Microsoft Excel?

Come and learn how to utilize SQL Server to the max!

DevelopMentor's Essential courses provide four intensive days of instructor-led training for the experienced developer. Gain deep understanding of your Business Intelligence platform. Increase confidence and master the skills you need to be productive today, build a solid foundation for more advanced topics and drive your reporting ability to the next level.

Course highlights

In this course, you learn to:

  • Understand the different components of the Microsoft Business Intelligence Stack
  • Dramatically improve performance using SQL Server Integration Services(SSIS) to load the data mart
  • Learn the ideal way recommended by top Microsoft professionals to create aggregations, hierarchies, and dimensions in SQL Server Analysis Services (SSAS)
  • Test new features of SQL Server Reporting Services (SSRS) like the Tablix control, Report Builder 3.0, and SharePoint Integration
  • Acquire the ability to write solid MDX queries against your SSAS cubes
  • Create impressive KPI collections and scorecards in PerformancePoint Services 2010
  • Harness advanced features in Microsoft Excel to reach a single version of the truth
  • Discover how to amaze your business users with Excel PowerPivot
  • Develop familiarity with the different data mining algorithms available in the stack for expert reporting and data discovery
  • Extensive set of hands-on practices at the end of each section for thorough comprehension of each topic

Course outline and topics

Day 1 - Day of ETL
Loading the Data Mart using an Effective ETL System

The extract, transform, and load (ETL) is the most difficult, demanding phase of building a data mart. Done correctly, companies can maximize data storage; otherwise, millions of dollars are squandered storing rarely used data. A solid ETL system extracts the data while enforcing data quality and consistency standards, and delivers valuable, practical data. This section will show how to plan, design and build an ETL system using established ETL techniques, applying best practices for extracting data from scattered sources, removing redundant and inaccurate data, and transforming the remaining data into correctly formatted data structures.

Loading the Data Mart using SQL Server Integration Services
Once you've learned about the general ETL process in the previous module, we will dive into this module and learn how to create those processes using SQL Server Integration Services (SSIS). SSIS is a powerful tool filled with performance tuning techniques that we can use to avoid locking the source systems, while delivering real-time reports to our users. We will look at the different components of SSIS and discuss when to use them. We will review how SSIS handles buffers and memory.

Loading Dimensions with SSIS
We will build Slowly Changing Dimensions with SQL Server Integration Services. We will also look at Change Data Capture, Change Tracking, the Merge statement, and a bunch of other tools we have to quickly and efficiently load data into dimensions.

Day 2 - Day of Data Quality
Data Quality Services

DQS is a great way to ensure that your data is as clean as possible. It's a new tool will ensure that the addresses in your data are real addresses. When data that is inputted in a variety of ways, it is correctly consolidated. When names of streets and cities are misspelled, it corrects and cleans that data. It also allows an organization to standardize on data input, like two-character state names. You can integrate these cleansing processes directly into SQL Server Integration Services.

Master Data Services
MDS is used to make sure that data is consistent through-out an organization. If an address is updated in one system, MDS will provide an easy way for it to be updated in another system. This way data is kept up to date through-out multiple systems in an efficient and logical manner. We'll also look at the Excel add-ins to aid in the process. Then we'll explain how MDS, DQS, and SSIS can be implemented as a unit to provide a comprehensive data cleansing and import strategy.

PerformancePoint Services
In this section, we will unleash the power of PerformancePoint Services (PPS) to gain real-time insight into all aspects of business performance. PerformancePoint Services is the main business intelligence feature of SharePoint 2010 used in building effective dashboards, scorecards, KPIs, strategy maps while guaranteeing security. We will use realistic, proven techniques to integrate PPS with Excel Services and Reporting Services.

Day 3 - Day of Reporting & Tabular
Creating Reports with SQL Server Reporting Services
SQL Server Reporting Services (SSRS) is used to create data-rich, visually appealing reports that quickly deliver relevant business intelligence data to your end-users. It can be used to create charts, graphs, tables, scorecards, and dashboards. This section will show how impressive reports can be generated off of the cubes and the data mart. We will use conditional formatting, linking, sub reports, and pivoting to create awesome, memorable reports.

PowerPivot & PowerView
PowerPivot has the muscle to analyze HUGE amounts of data, quickly and easily. In this module, we will explore this amazingly powerful tool. We will also look at when it's wise to use PowerPivot over SQL Server Analysis Services. We will increase your existing skills through data manipulation with Data Analysis Expressions (DAX). We will also share sophisticated calculations, using PowerPivot to rapidly turn mass quantities of data into meaningful information with immediate results. PowerView empowers users to create beautiful dashboards, reports, and animations. You'll be amazed at how quickly you can create useful and gorgeous artifacts.

Day 4 - Day of SSAS
Building Cubes with SQL Server Analysis Services - Tabular & DAX

In SQL Server 2012, Microsoft released a whole new way of creating cubes: Tabular. This method allows data marts and cubes to be created with blazing speed, literally cutting development time down by 75%. With data in our data mart, we can now create fast, scalable, and maintainable cubes against it that allow users to analyze business data quickly and easily. This module will help to avoid common cube building mistakes. We will create measure groups, dimensions, hierarchies, partitions, and calculated measures. We will examine where to place the detail data for drill through, and how to implement security. Data Analysis Expressions (DAX) is the method used to enhance many of these features, and we'll introduce it. You will find all of these powerful topics very easy to learn.

Building Cubes with SQL Server Analysis Services - Multidimensional & MDX
SSAS also has the tried and true method for building cubes: Multidimensional. This mature and feature-rich method has many advantages over Tabular, including a more comprehensive language. Multidimensional Expressions (MDX) is a powerful query language used to control cubes and interact with SQL Server Analysis Services. We will use MDX to query time dimensions, create custom aggregations, and achieve reliable security. We will work with tuples, sets, and expressions to retrieve the precise data users need through performing aggregation functions and navigating data hierarchies.

Data Mining Algorithms
A comprehensive, practical look at the concepts and techniques you need to know to get the most out of real business data. Data mining can uncover a variety of trends in your data such as: fraud, duplication, missed billing opportunities, market analysis, and inventory overages. We will cover all of the popular data mining techniques and algorithms including: frequent pattern mining, clustering, and classification; with a focus on how to use Excel and SQL Server together to effectively mine your data mart and to overcome the challenges data mining presents.