Business Intelligence Training: The Microsoft Self-Service BI Toolset

$995 | Hands-on | 20 hours | FlexClass Format

Leverage SharePoint BI by Combining PowerPivot, Power View, Report Builder and PerformancePoint to Build No-Code BI Applications and Reports

Are better business reporting and dashboards on your radar, but you just haven’t been able to hurdle the learning curve?  Are you good at analyzing data using tools like Excel and enriching the data using features such as functions and formulas?  Would self-service reports and dashboards like these give everyone in your organization fresh excitement for SharePoint?:

If so, are you aware that there has been a quantum shift in the Business Intelligence (BI) software market away from big, costly, complex and lengthy data warehouse projects toward fast and flexible self-service BI created by business users?

There has! Microsoft is leading the way with an integrated set of business user-oriented tools such as SharePoint, PowerPivot, Power View, SQL Report Builder and PerformancePoint.  If you need to be able to analyze and share a wide array of business data in hours instead of months or years, come take this 3-day class and we will teach you how these tools can get you there!

Module 1: Self-Service BI vs. Traditional Data Warehouses

This module sets the stage for the course, and answers key questions about what Self-Service BI is, why it is the way of the future, and what advantages it provides over Traditional Data Warehouses.  (For an introduction, watch our YouTube video, “What is the Microsoft Self-Service BI Toolset?“).

Of course, with new technologies, there are always some limitations in the early releases as well and those will also be covered in this module.  Topics covered in this module include:

• The business “pain” related to traditional data warehouses that has served as the impetus behind the new wave of self-service BI tools from Microsoft
• The BI “magic” that plentiful and inexpensive RAM has created
• The tools included in the Microsoft Self-Service BI stack and an overview of how they are designed to work together and be usable by everyday business and IT power users.  (To see a short demo of the tools you will learn how to use, watch our YouTube video, “End-to-End Demo: Microsoft Self-Service Business Intelligence“)
• Current limitations of the Microsoft Self-Service BI stack and anticipated enhancement roadmap for future versions

Module 2:  The Role SharePoint Plays in the Microsoft Self-Service BI system

SharePoint is the “glue” that when combined with the Microsoft Self-Service BI toolset, makes a complete system.  SharePoint provides much of the presentation layer for Microsoft Self-Service BI.  Without SharePoint, it would be very difficult, if not impossible, to use the tools in a way that dashboards and reports could easily be shared throughout the organization and appropriate permissions assigned.  Very clearly, the new tools provided are designed to work their best, if at all, when deployed in conjunction with SharePoint Enterprise Edition.  Topics covered in this module include:

• Overview of the end-user BI experience from within SharePoint
• The role of SharePoint groups and permissions in the self-service BI approach
• The role of SharePoint Content Types in the self-service BI approach
• Overview of the architecture and integration points between SharePoint and the SQL Server 2012 BI features

Module 3:  The Self-Service BI Engine – the BI Semantic Model

Non-IT professionals: don’t get scared of what might appear to be an overly technical term – the BI Semantic Model. The new Microsoft BI Semantic Model is really the engine of the Self-Service BI approach and it is necessary that you understand it at a functional level. But it is not difficult to understand if you are comfortable with using tables from external data sources in Excel. Topics covered in this module include:

• The BI Semantic Model explained in plain English (including explanation of seemingly meaningless, but important, terms such as xVelocity and VertiPaq)
• Two functionally equivalent approaches for developing BI Semantic Models:
  • PowerPivot for Excel
  • SQL Server Data Tools

Module 4:  PowerPivot Part 1 – Business User-Developed Data Models

Doing reporting and heavy data analysis against live production business data is not always a good idea. Often a better approach is to extract business data and build a database (or data model) that can be used solely for reporting and data analysis. This is one of the reasons that organizations have put so much time and money into building data warehouses. Another reason is that the details of live production database schemas are often too complex for the business user to understand. Therefore, a data warehouse can make the data itself seem more friendly to the business user.

Unfortunately, data warehouses have proven very complex and costly to initially develop and maintain. It is not unheard of for a data warehouse development project to take several months or even years. A new alternative is using PowerPivot for Excel to build reporting and analysis data models.
PowerPivot is a powerful plug-in to Excel that allows a power user to develop manageable data models in Excel.  These PowerPivot data models can have much of the same power and capacity that large-scale data warehouses have.   PowerPivot for Excel supports joining multiple external data sources and storing hundreds of millions of rows of data directly inside an Excel workbook.  Major advances in data compression make this all possible.  Finally, these PowerPivot workbooks have the added benefit of being created and maintained by the everyday power business users in a familiar and popular tool, Excel.  It all may sound too good to be true, but it is true.  Topics covered in this module include:

• What is a data model and why is it necessary to develop?
• PowerPivot Data Model design concepts
• Connecting to data sources
• Loading data using the Table Import Wizard
• Filtering Rows and Columns using the Table Import Wizard
• Handling data relationships
• Using the PowerPivot Query Designer for special cases
• Scheduling refreshes of the data model in SharePoint

Module 5:  PowerPivot Part 2 – Using Data Analysis Expressions (DAX) to Enhance Data Models

Not all data that you can bring into the PowerPivot data model is immediately suitable for reporting and analysis.  Frequently, some transformation needs to be done to render the data in a format more suitable to presenting it in readily accessible form to business users.  In PowerPivot, raw data from external sources can be imported into PowerPivot and transformed using PowerPivot’s Data Analysis Expressions (DAX) language which is an easy language to learn, and similar in many ways to Excel formulas.  Topics covered in this module include:

• Common data issues that DAX expressions can solve
• DAX Syntax
• DAX Data Types, Operators and Values
• Calculated Columns
• Measures
• DAX Functions
• Handling errors

Module 6: PowerPivot Part 3 – Using Excel PivotTables to Explore and Validate PowerPivot Models

A PowerPivot data model is a full-fledged BI Semantic Model and can be used as a data source by end-user tools for creating dashboards and reports.  In the next module, the student will create dashboards and reports using Power View, by using the PowerPivot data model created in Modules 4 and 5.  But first, it is helpful to explore the PowerPivot model and validate that it has been built properly to support the dashboards and reports that are required.  Using Pivot Tables in Excel is the best way to do this.  Topics covered in this module include:

• Inserting a Pivot Table in a worksheet that is based on the PowerPivot data model
• Using the PowerPivot Field List to design a Pivot Table
• Handling errors when automatic relationship detection doesn’t work properly
• Refreshing the Pivot Table when the underlying PowerPivot data model changes
• Using the PowerPivot-Enhanced Data Slicers

Module 7: Power View – Creating Highly Visual and Interactive Dashboards and Reports From PowerPivot Models

Power View is a new product which is included with SQL Server 2012 and Excel 2013.  It falls into the category of a Rich Internet Application (RIA) because it has rich functionality like desktop software, yet it runs in the browser since it is Silverlight-based.  Power View has been critically acclaimed as an easy-to-use tool that offers point-and-click authoring of highly visual and interactive dashboards and reports.  The screen shots shown at the top of this course outline are typical dashboards and reports that can be developed using Power View.  Topics covered in this module include:

• The Power View user interface and terminology
• Table layout visualizations
  • Using Filters
  • Using Tiles
  • Sorting
  • Creating multiple Views
• Matrix layout visualizations
• Card layout visualizations
• Charting
• Adding interactivity

Module 8: SQL 2012 Report Builder 3.0 – Ad-Hoc End User Report Writing from within SharePoint

Sometimes, business professionals need traditional table and cross-tab (or matrix) reports with grouping and subtotals.  The most common way to obtain these reports is to request that IT build them using tools like Business Intelligence Development Studio (BIDS) and Crystal Reports.  Unfortunately, the IT department typically has a significant backlog of work requests and often cannot get to report development projects on a timely basis.   The end result is that the business professionals’ reporting needs go unmet.  An alternative is to give the business professionals access to a report writing tool that is easy enough for them to learn and use for simpler reporting projects.  That is what Report Builder 3.0 aims to do, and its tight integration with SharePoint rounds out the experience so that users can store and manage their ad-hoc reports in the familiar paradigm of a SharePoint Document Library.  Topics covered in this module include:

• The Report Builder Interface
• Data Sources and Datasets
• Data Regions
• Expressions
• Formatting
• Grouping
• Saving and running reports in SharePoint libraries

Module 9: Creating a PerformancePoint Dashboard using a PowerPivot Data Model as the Data Source

PerformancePoint is a Microsoft technology that was originally developed under the name Balanced Scorecard Manager.  Once a standalone Microsoft product, starting with SharePoint 2010 it became bundled with SharePoint Server Enterprise as a Service Application.

PerformancePoint is a tool that has traditionally been targeted at the professional BI developer, rather than business professionals.  Its mechanics are a good deal more complex than PowerPivot, Power View and SQL Report Builder, but with a bit of training, some business professionals will be able to use it to create web part-based dashboards and scorecards – particularly since PerformancePoint now supports PowerPivot data models as a data source.  Topics covered in this module include:

• The PerformancePoint Dashboard Designer user interface
• Data sources
• Indicators
• KPIs
• Scorecards
• Reports
• Dashboards
• Filters
• The SharePoint Business Intelligence Center site template
• Saving PerformancePoint components to SharePoint
• Publishing PerformancePoint Dashboards to SharePoint

Module 10: Understanding the differences in Microsoft Self-Service BI between the 2010 and 2013 versions of SharePoint and Office

The bulk of the technology that underlies the Microsoft Self-Service BI toolset is provided by SQL Server 2012 and students will be hands-on with this latest version of the software throughout the course.  However, since SharePoint and Office also play a critical role in the platform, there are some differences in the capability and user experience depending on whether the organization is using the 2010 or 2013 versions of SharePoint and Office.

In this module, we will review and explain the major differences between the two platform versions.  Some of the key enhancements that were made in the 2013 version that we will address include:

• PowerPivot ships with Excel 2013 – no need to download and install the add-in
• Improvements in creating KPIs in PowerPivot for Excel
• Automatic categorization of data columns in PowerPivot for Azure DataMarket suggestions and Power View visualization suggestions
• Pie Charts are now supported in Power View
• Maps are now supported in Power View
• Key Performance Indicators (KPIs) are now supported in Power View
• Hierarchies are now supported in Power View
• Drill up and drill down are now supported in Power View

This course is targeted at business and IT professionals who are capable of developing BI and reporting types of business applications using no-code, power user-oriented tools.  For instance, business and IT professionals who are comfortable working with medium to complex Excel workbooks with formulas, multiple data sources, PivotTables and Pivot Charts will be able to learn the tools that are used and successfully complete this course.

(Note: to use these new BI tools in production, your organization must have deployed (or be planning to deploy) the on-premises version of SharePoint 2010 or 2013 Enterprise Edition and an instance of SQL Server 2012 Enterprise or Business Intelligence Edition as an add-on application server in your SharePoint farm.  Previous versions of SQL Server can still be used simultaneously for storing your SharePoint content.)