240 likes | 247 Views
Join Chris Campbell, a Principal Consultant with BlueGranite, as he explores how PowerPivot redefines the line between IT and end-user roles. Learn how to process massive amounts of data in seconds, take advantage of familiar Excel tools, and load even the largest datasets from virtually any source. Discover the power of PowerPivot in revolutionizing business intelligence.
E N D
About the Presenter • Chris Campbell • Principal Consultant with BlueGranite • Certified Professional in Microsoft BI Development • Microsoft Virtual Technology Specialist • Business Intelligence Technical Architect at the Microsoft Technology Center, Detroit ccampbell@blue-granite.com b-chcamp@microsoft.com http://www.blue-granite.com http://twitter.com/ckcampbell
Agenda • Introduction • What is PowerPivot? • Data Modeling for Analysis • Demo • Resources • Questions
Trends in Business Intelligence • IT Owned & Managed • Slow to Respond • Monolithic • Not Interactive BI 1.0 BI 2.0 Silo Silo Structured & UnstructuredData UnstructuredData StructuredData • Business Owned, IT Managed • Agile • Flexible • Collaborative
What is PowerPivot? A solution that re-defines the line between I.T. and end-user roles ... using familiar tools without requesting support … managing usage and resources involved
Why Use PowerPivot? • Process massive amounts of data in seconds • Take advantage of familiar Excel tools and features • Load even the largest data sets from virtually any source • It is FREE!
Download and Install PowerPivot Go to http://www.powerpivot.com and choose a version
Download and Install PowerPivot Make sure to select the appropriate version for your version of office (32-bit or 64-bit)
Finding out what version of Excel You Have In Excel, Click “File” then Help. The version of Excel is noted under “About Microsoft Excel”
OLAP Data Modeling • OLAP Data Models generally take the form of a “Star” Schema • Two kinds of tables • Dimension • Fact • Fact tables have a 1:M relationship to all Dimension tables.
Data Modeling for Analysis • PowerPivot blurs the line between dimensions and facts. • Only requires that a user understand where the numbers are and how they want to slice them. • Can join together many disparate data sources. • DAX allows you to manipulate the data and build custom calculations.
DEMO Building a Data Model and Dashboard with PowerPivot
DAX Evaluation Context • Row Context • “The current row” • Determined by the content of the TABLE • Includes ALL columns • May include “RELATED()” columns from other tables • Query Context • What row(s) and column(s) are selected? • Determined by what are on rows/columns in your pivot table • Filter Context • What rows are left after the filter is applied?
DAX Evaluation Context – Example Row Context Query Context Filter Context
DEMO PowerPivot Management Dashboard
PowerPivot Resources • Web sites • http://msbiacademy.com • http://powerpivotpro.com • http://www.powerpivot.com • http://powerpivotgeek.com • http://powerpivot-info.com • BOL • http://msdn.microsoft.com/en-us/library/ee210682(SQL.105).aspx
Questions? ccampbell@blue-granite.com b-chcamp@microsoft.com http://www.blue-granite.com http://twitter.com/ckcampbell