This event has ended

Getting Started with PowerPivot & Other Microsoft BI Topics by Bill Pearson

SQL Saturday Tampa

Friday, March 9, 2012 from 9:00 AM to 5:00 PM (PST)

Getting Started with PowerPivot & Other Microsoft BI Topics...

Ticket Information

Ticket Type Sales End Price Fee Quantity
Intro to PowerPivot & Other BI Ended $99.00 $6.44

Share Getting Started with PowerPivot & Other Microsoft BI Topics by Bill Pearson

Event Details

Getting Started with PowerPivot and Other Microsoft  Business Intelligence Topics

Pre-Conference Description 

SQL Server MVP and Business Intelligence Architect Bill Pearson leads this day-long collection of current Microsoft Business Intelligence topics, beginning with a half-day introduction to PowerPivot for Excel, and followed by three independent presentations focused upon MDX (for PowerPivot and Reporting Services perspectives),  and the optimization of Analysis Services and Reporting Services, together with other MSBI subject matter.  In summary, we expect to have a concentrated day of learning about the promises of PowerPivot technology, as well as many ways to make our business intelligence implementations more successful, and performing in tip-top shape.



Getting Started with PowerPivot for Excel

Session Description 

This four-part segment of the preconference represents a half-day, hands-on introduction to using PowerPivot for Excel to deliver self-service business intelligence.  The focus of the course is to help those new to PowerPivot become familiar with the assembly of data from diverse sources into models that deliver business intelligence upon demand. 

Participants will gain exposure to accessing and relating data, and to employing the Data Analysis Expressions (DAX) language, to construct and share PowerPivot applications that support analysis and reporting throughout the enterprise.


Intended Audience 

Information workers (business- or IT-based) involved in analysis and reporting of data,  intermediate to advanced Excel users,  practitioners that want to gain familiarity with PowerPivot to build compelling analysis and reporting applications to deliver self-service BI.


Course Objectives

Upon completion of this course, attendees will be able to: 

* Assemble and relate data from diverse sources into a PowerPivot model to support self-service business intelligence

* Employ DAX functions to create basic calculations within the PowerPivot data model

* Construct basic PowerPivot data models with an eye toward applications that support enterprise analysis and reporting

* Perform basic PowerPivot – based analysis and reporting with PivotTables in Excel 2010


Attendees with the following will benefit the most from this course:

* Basic familiarity with the Microsoft Windows operating system

* Exposure to Excel PivotTables and PivotCharts

* Exposure to standard Excel functions * Exposure to basic relational database concepts 

PowerPivot Module 1: Overview and Installation

In this module we overview the course layout and preview the objectives of the individual modules.  We will then walk through the installation of the PowerPivot add-in for Excel 2010, discussing sources of the add-in, 32- vs. 64-bit considerations, and related details.  Finally, we will walk through the steps involved in creating a basic PowerPivot application.


After completing this module, attendees will: 

* Understand objectives of the course and background of the instructor

* Be aware of sources from which PowerPivot for Excel can be obtained

* Understand which of 32- or 64- bit versions of PowerPivot are appropriate for your PC

* Be able to install PowerPivot for Excel

* Be able to ascertain that PowerPivot for Excel has been installed

* Understand navigation of PowerPivot windows 

* Understand how the PowerPivot components work within an Excel workbook

* Gain an end-to-end understanding of the steps involved in the creation of a rudimentary PowerPivot application


PowerPivot Module 2:  Building the Basic PowerPivot Data Model

In this module we learn the detailed steps of constructing a PowerPivot data model, beginning with the import of diverse data sources into PowerPivot, and moving through inspection of data (for population accuracy and completeness, etc.) cleansing of data, and the establishment of relationships between data from different sources.


After completing this module, attendees will: 

* Be aware of the data sources that can be imported into PowerPivot

* Be able to import different data sources into PowerPivot

* Know how to inspect imported data for population accuracy and completeness, etc.

* Understand how to approach cleansing data for utility within the PowerPivot data model

* Establish relationships between data imported from different sources into PowerPivot


PowerPivot Module 3:  Working with PowerPivot and Excel PivotTables, and an Introduction to DAX

In this module we examine the key components of a PowerPivot BI solution, and gain an understanding of how these components work together to deliver effective self-service business intelligence.  As a part of our practical exercises surrounding these components, we gain an introduction to the Data Analysis Expressions (DAX) language, learning how to employ rudimentary DAX functions in the creation of calculations at two important levels in the PowerPivot solutions we construct.

 We first explore the navigation of the PowerPivot Window and how to create calculated columns, discussing the nature and effective use of these calculations as a part of our examination.  We then shift our focus to Excel PivotTables and PivotCharts that support PowerPivot models, initially exposing enhancements over standard Excel PivotTables and PivotCharts, so that we gain an understanding of the role that PowerPivot plays as a source to PivotTables. We gain experience accessing calculations we have created in the underlying PowerPivot window from within a PivotTable.  We then create calculations at the PivotTable level via calculated measures.

Finally, we discuss the differences in PowerPivot calculated columns and PivotTable calculated measures, and learn when to employ each for optimal performance and understandability of our PowerPivot BI solutions.  Moreover, we introduce the concept of evaluation context, and examine its importance within our PowerPivot BI applications.


After completing this module, attendees will: 

* Be able to navigate the PowerPivot window

* Understand the roles of the PowerPivot window and PivotTable / PivotChart components within the PowerPivot BI application design

* Understand the role of the Data Analysis Expressions language in the  design, creation and use of a PowerPivot BI application

* Be able to create basic calculated columns in PowerPivot using DAX

* Understand the nature and use of a PowerPivot calculated column

* Be able to create a PivotTable and a PivotChart in the Excel workbook, understanding the potential uses for each, along with differences between them

* Understand the enhancements to PivotTables and PivotCharts over their standard Excel counterparts

* Be able to access previously created PowerPivot calculated columns from the PivotTable

* Be able to create basic calculated measures in the PivotTable using DAX

* Understand the nature and utility of a calculated measure

* Appreciate the differences between PowerPivot calculated columns and PivotTable calculated measures, and grasp the optimal employment of each in overall BI application design. 

* Understand the importance of evaluation context, and be able to apply this knowledge in the design, creation and use of PowerPivot BI applications.


PowerPivot Module 4:  PowerPivot Analysis and Reporting with PivotTables

In this module we build a reporting layer upon the PowerPivot model we constructed in Modules 2 and 3, performing a detailed exploration of the PowerPivot-enhanced PivotTable as a part of the process.  We examine various PivotTable types, contrasting each to the Excel 2010 PivotTable.  We expose details about the new PivotTable, discussing its many possible uses with diverse data sources, and then get hands-on exposure to meeting typical, as well as special, reporting needs,

such as the computation of ratios and averages and custom sorting and aggregation.  Finally, we create a simple Excel dashboard atop our application to demonstrate the ease with which we can deliver user-friendly capabilities with our integrated PowerPivot analysis and reporting.



After completing this module, attendees will: 

* Be able to describe the PivotTable types and contrast features among them with the Excel 2010 PivotTable

* Understand analysis and reporting uses of the Excel 2010 PivotTable with diverse data sources (particularly PowerPivot)

* Have gained hands-on exposure to creating a PivotTable atop PowerPivot, and accessing PowerPivot calculated columns therein

* Have created calculated measures within the PivotTable, working further with DAX functions, to meet illustrative reporting needs

* Have gained exposure to custom sorting and aggregation within the PivotTable

* Have constructed a simple dashboard within the Excel workbook

* Have defined Analysis Services –based sets within the PivotTable

* Have gained exposure to saving and reusing PivotTable components



Microsoft Business Intelligence Topics


Topic #1:  Reciprocal Symmetry: Working with MDX in PowerPivot

Synopsis:   MultiDimensional eXpressions (MDX) is a powerful query language that allows us to retrieve data from Analysis Services cubes and published PowerPivot models.  In this session, BI Architect and MVP Bill Pearson overviews some MDX basics, and then leads an examination of PowerPivot’s graphical query designer for MDX. He then compares the PowerPivot MDX query designer to the SSMS MDX Query Editor, where we can write more powerful, sophisticated queries that fully leverage MDX. We ultimately arrive at a “best-of-both-worlds” approach where we optimize the more potent SSMS query for PowerPivot.

Session Level: Intermediate


Topic #2:  Serene Velocity Series:  Reporting Services and SSAS 2008 R2 Data

Synopsis:   SSRS 2008 R2 provides powerful capabilities to help us develop sophisticated reports from Microsoft SQL Server Analysis Services. Unfortunately, many of these features, such as the server aggregation function, are commonly ignored by report developers. The result: redundant work in the reporting layer. In this session, BI Architect and MVP Bill Pearson exposes ways to optimize reporting from SSAS by writing MDX that leverages, rather than replicates, what the cube is designed to do best.

Session Level: Intermediate


Topic #3:  Beyond Plateaux:  Optimize SSAS via Best Practices


Synopsis:  Whether we inherit our Analysis Services environments from predecessors or create them - learning as we go - ourselves, we often conclude that performance (processing and / or querying) and functionality can be enhanced.  In this session, Microsoft BI Architect and SQL Server MVP Bill Pearson overviews ways to launch new capabilities and to propel

performance beyond its current plateau, using design best practices to better meet consumer performance and functional needs.  This is the initial session of a set of detailed Best Practices presentations.

Session Level: Intermediate




Course Hardware and Software Requirements


* Windows 7, Windows XP (SP3), or Windows Vista (SP1)

* 1GB RAM minimum (with 2GB RAM recommended)

* Microsoft Excel 2010 (32-bit or 64-bit) installed * PowerPivot for Excel  (32-bit or 64-bit, as appropriate to attendee machine) installed

* Microsoft SQL Server 2008 R2 installed, or available for connection * Connectivity available to AdventureWorksDW2008R2 database  * Connectivity available to Adventure Works Analysis Services database



About the Instructor

Bill Pearson, MVP (SQL Server BI), CPA, CITP, CMA, CIA, MCDBA, MCSE, is a consultant, author, mentor and business intelligence architect who has been working with business intelligence for over 18 years, and with the integrated Microsoft Business Intelligence solution since its inception.  Bill has been awarded the Microsoft Most Valuable Player multiple times for his technical experience implementing Microsoft BI, as well as his contributions to the community as an author and speaker.  Bill’s recent series’ include the Stairway to MDX and Stairway to PowerPivot and DAX columns at, and he presents regularly at SQL Saturdays and other, diverse events worldwide.

Have questions about Getting Started with PowerPivot & Other Microsoft BI Topics by Bill Pearson? Contact SQL Saturday Tampa

When & Where

Hampton Inn & Suites / Ybor City

, FL 33605

Friday, March 9, 2012 from 9:00 AM to 5:00 PM (PST)

  Add to my calendar

Please log in or sign up

In order to purchase these tickets in installments, you'll need an Eventbrite account. Log in or sign up for a free account to continue.