![Page 1: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/1.jpg)
PowerPivot: Business Intelligence and Massive Data Analysis for Humans
Rob Collie CTO, Pivotstream LLC Founder, PowerPivotPro.com & PowerPivotFAQ.com
![Page 2: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/2.jpg)
What you need to know about me
Worked at Microsoft as a Lead Program Manager – 1996-2010 – Mostly on Excel, and specific focus on Business Intelligence – Short stint on Bing Search – One of the first engineers on PowerPivot, designed a lot of it – Left MS last year to “go pro” with Pivotstream
PowerPivotPro.com, PowerPivotFAQ.com – Info, consulting/training, and software
I now make my living as a BI professional, but… – I still don’t know MDX – Never even seen Integration Services – Never built a Reporting Services Report – Never written a SQL View, or a sproc, or… you get the idea
page 2
![Page 3: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/3.jpg)
Industrial Scale Demo
page 3
![Page 4: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/4.jpg)
VertiPaq Engine
Analysis Services – queryable using “traditional” MDX!
100% in memory, NO paging (and I do mean NONE)
Column store, not a row store
Averages 15x compression, but varies a LOT
Additional 2x on disk
– But most compression is preserved in RAM!
– The compression IS the index
Going into Relational Denali as a new Index Type
The only thing I ever saw Microsoft choose to NOT patent
– I have forgotten most of its tricks, but…
page 4
![Page 5: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/5.jpg)
Traditional On-Disk, Row-Wise Storage
page 5
Record 1
Record 2
Record 3
Record 4
Record 5
Record 6
Col 1 Col 2 Col 3 Col 4 Col 5
![Page 6: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/6.jpg)
VertiPaq Column Storage
Records 1-15 (or 50 M)
Region Year
East
Central
West
…
1999
2000
2001
1999
2002
2003
2002
2005
1999
2002
2006
Records 16-25
Records 26-38
![Page 7: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/7.jpg)
Optimizing Data for VertiPaq
Less columns, more rows
page 7
YES NO
![Page 8: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/8.jpg)
Some Production PowerPivot Applications
page 8
![Page 9: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/9.jpg)
Pivotstream BI Applications: Built 100% on SharePoint 2010
Multi-million dollar gift pack campaign every year
“Does it help? Sometimes more than others? What would help more?”
![Page 10: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/10.jpg)
Pivotstream BI Applications: Built 100% on SharePoint 2010
Used by pharma sales reps
Identifies influential physicians
“Which ZIP codes offer densest opportunity?”
“Small practice vs. big practice?”
![Page 11: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/11.jpg)
Pivotstream BI Applications: Built 100% on SharePoint 2010
Identify when a given flavor/brand/type is not getting or delivering its fair share of promotional support.
![Page 12: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/12.jpg)
Why BI Matters More Every Day
page 12
![Page 13: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/13.jpg)
BI Spending ACCELERATES in Recessions
page 13
![Page 14: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/14.jpg)
Opportunity: BI is far from mature, it is wide open today
page 14
![Page 15: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/15.jpg)
Business Intelligence: The Too-Often Reality
page 15
SANDURZ: Yes sir. Prepare to move out! HELMET: What are you preparing. You're always preparing. Just go!
![Page 16: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/16.jpg)
The Great Football Project
Real Project! (2006)
Professional BI consultant
– $50k, 3 months
– Top-notch SSIS/SSAS pro
Professional data!
– $100k license to STATS Inc.
– “Professional” only meant “Expensive”
![Page 17: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/17.jpg)
From the archives of TGFP
page 17
![Page 18: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/18.jpg)
Football Demo!
page 18
![Page 19: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/19.jpg)
Football Project: Takeaways
One week elapsed became one hour with PowerPivot!
Requirements collection and test/validate
– Huge percentage of the project’s elapsed time
“Good enough” ETL isn’t good enough
– Clear unambiguous schema
– Biz units become informed consumers of DW
![Page 20: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/20.jpg)
Agility Demo
page 20
![Page 21: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/21.jpg)
Microsoft’s Changing BI Strategy: Unified Around SharePoint and PowerPivot
SharePoint is THE portal and visualization/reporting host
page 21
PerformancePoint Services
Excel Services “Project Crescent”
PowerPivot as data model, designed in XL
Reporting Services
Next release: Traditional modeling product adopts SAME modeling
philosophy as XL/PowerPivot
![Page 22: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/22.jpg)
More Info
http://PowerPivotPro.com
http://HostedPowerPivot.com
– Partnership with Rackspace
Twitter: @PowerPivotPro
– Questions, curiosities, feedback
– Proof of Concept PowerPivot sites
– Consulting/training
– Tools and components
– Movie quotes
page 22
![Page 23: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/23.jpg)
Backup Slides
page 23
![Page 24: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/24.jpg)
Examples from Microsoft: SQM and Watson
page 24
SQM (“Squim”)
Must opt in
Every command issued is captured
Introduced more than 10 yrs ago
Constantly discarding older data (weak!)
SQM (“Squim”)
Every crash and hang
100’s of millions of users
![Page 25: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/25.jpg)
#1: Economic downturns mean more focus on BI
1999-2002: Dot com meltdown
– IT Spending Plummets
Late 2002: We start planning Excel 2007 feature set
– We notice that during 1999-2002, BI Spending increased
– BI then becomes a big (and successful) focus of the product
Late 2008: Financial meltdown
– IT spending plummets
– But BI spending still shows small growth
2011: Analysts project 4-40% BI spending growth, but continued belt tightening elsewhere
page 25
![Page 26: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/26.jpg)
Contradictions?
page 26
![Page 27: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/27.jpg)
PowerPivot: The Right Tool at the Right Time (aka: “Humans! Here’s why you should care!”)
Business Intelligence is only becoming more important
– Data everywhere!
– More biz pressure = more “smarts” needed
But…
– There are more BI failures than successes to date
– Budgets continue to shrink
PowerPivot: the well-timed good news
– Opportunity and job security
– No prior experience BI experience required
– Pivotstream has bet heavily on PowerPivot… and “won”
page 27
![Page 28: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/28.jpg)
page 28
139 193
3
157 157 157
64 64 64
10 36
106
132 172 224
95 131
4
230 230 230
![Page 29: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/29.jpg)
What is PowerPivot? PowerPivot add-in
for Excel
PowerPivot add-in
for SharePoint
![Page 30: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/30.jpg)
Questions I Will Answer Today
“Why should I care?”
“How can it HELP my company?”
– “And wait, how can it help ME?”
– “Can I see some examples?”
– “Can I have some brain candy?”
“How does it IMPACT me if my company adopts it?”
page 30
![Page 31: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/31.jpg)
Trend #2: Data Collection and Storage Explosion
Library of Congress:
530 miles of bookshelves
10 Terabytes (cute!)
page 31
0
200
400
600
800
1000
1200
1400
1600
1800
2000
2006 2007 2008 2009 2010 2011
Worldwide Data Storage (EB)
Worldwide Data in Storage:
~180 Million TB in 2006
10x increase in 5 years!
180M Libraries of Congress today!
Translating that into ACTION is the trick
![Page 32: PowerPivot: Business Intelligence and Massive Data Analysis for Humans](https://reader035.vdocuments.us/reader035/viewer/2022071600/613d1554736caf36b7591d36/html5/thumbnails/32.jpg)
Opportunity #1: Awash in data, but ZERO usable information
page 32