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:
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.
In this course, you learn to:
Overview of the Business Intelligence Stack
In this opening topic, we dig into the Microsoft Business Intelligence platform. We will review all the major components of the BI stack, including SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), SharePoint, PowerPivot, and Excel. We will explore how these products interact to deliver fast, accurate, and scalable solutions.
Designing a Data Mart
There are many things about designing a data mart that is non-intuitive and challenging. Converting a transactional, normalized schema into a non-normalized schema can be very cumbersome. Decisions made while designing fact tables and dimensions can affect the performance and scalability of the data mart for years to come. It is important to get these decisions right! Our goal is to make these topics approachable and easy. We will examine fact table and dimension table modeling, best practices for gathering user requirements, how to design a data mart off of existing reports, and how to decide where to store aggregations.
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. Included in this section is an overview of Change Data Capture which offers cutting-edge, time-saving ETL techniques with minimum impact to the SQL server, and ensures crucial data quality.
Building Cubes with SQL Server Analysis Services
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. Cubes often fail because they are designed with a specific report in mind, while ignoring cube processing speed, real-time data requirements, and the reporting needs of the business as a whole. This module will help to avoid these common cube building mistakes. We will create measure groups, dimensions, hierarchies, partitions, and MDX calculated measures. We will examine where to place the detail data for drill through, and how to implement security. This module is filled with an abundance of cube design best practices.
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.
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.
PerformancePoint Services 2010
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.
Excel is the number one spreadsheet application worldwide. You would be hard pressed to find a business that doesn't use it. Unfortunately, it has always suffered from being used by multiple users who will change business crucial workbooks, remove formatting, alter formulas, clear macros causing colossal problems with data integrity and Excel-based reporting. Excel Services will help you solve these challenges with workbook distribution that successfully maintains a single version of the truth. In this module, we will see how to use Excel and Excel Services as a powerful analytic tool in the Microsoft BI stack.
Providing Data with Excel PowerPivot
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.
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.
C# Content was appropriate and actually very much on to the type of things we would want to do in our applications at work.