Intro to PowerPivot

1 / 26

# Intro to PowerPivot - PowerPoint PPT Presentation

Intro to PowerPivot. Stockholm, Jan 27, 2010 Rob Collie, Sr. Program Mgr , PowerPivot. “The funny thing about Europe”. Agenda. Why PowerPivot ? Demos! Why SharePoint? Demos!. Why PowerPivot ?. Money  Biz logic bottleneck Time Cost Lack of agility Embrace and improve Excel

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Intro to PowerPivot' - elsie

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Intro to PowerPivot

Stockholm, Jan 27, 2010

Rob Collie, Sr. Program Mgr, PowerPivot

Agenda
• Why PowerPivot?
• Demos!
• Why SharePoint?
• Demos!
Why PowerPivot?
• Money
• Biz logic bottleneck
• Time
• Cost
• Lack of agility
• Embrace and improve Excel
• Manage and assist, not fight
• Supplement centralized BI
TempMashup Demo
• Simple Pivot
• Mashup with Internet data
• Calc columns and relationships
• DAX Measures
DAX Measure Golden Rules
• Home Table = Fact Table
• Wherever the column to be aggregated lives
• No naked columns!
• Sum([Quantity]), never just [Quantity]
• Measures themselves are also ok
• Calculations happen against the TABLES
• Ex: Sum of a column in a PowerPivot table
• NOT a sum of PivotTable cells
• NOT a navigation of dimensional space
• Two Phases: Filter, then Aggregate
• During Filter, think “Cell-by-Cell”
• Filters flow from One to Many, NEVER vice versa
Movie Explaining the Golden Rules
• Pretty sure I lost people here
• Probably (hopefully) clearer here:
• http://bit.ly/ambRrv
• http://bit.ly/bv7tBo
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”
The CALCULATE() Function
• Like SUMIF(), modernized

=CALCULATE(<Aggregation>,<filter1>,<filter2>,…)

Why SharePoint?
• XL Pro
• (Practical)Security
• No “Save As” hijacking
• Autorefresh
• “Professionalized” results
• DB Pro / IT
• Transparency
• Usage tracking
• Focused point of engagement
SharePoint Demos
• Gallery
• Autorefresh
• Usage dashboard
DB Pro/XL Pro Partnership
• DB Proprovides clean, complete, reliable data
• XL Pro provides biz logic and reports
• Deputized XL users as ambassadors
• Provide focused feedback on DW
• “If we had a table like this, we could do X”
• DB Pro can proactively offer additional support
• “I see you are using Y as a data source”
• “Can we help insulate against outages?”
• “I better not rename that db after all”
More DAX Functions You Should Know About
• ALL()
• Clears pivot filtering from specified fields
• Clears filters for measure calculation, not visually
• Useful for % of total calcs
• SUM([Sales]) /

CALCULATE(Sum([Sales]),All([Products])

• Time Series Functions
• 39 Functions
• PARALLELPERIOD(), TOTALMTD(), PREVIOUSDAY()…
Cube “Side Effect”
• PowerPivotdb engine IS Analysis Services
• PowerPivot Workbooks are exposed as cubes
• ONLY when published to SharePoint
• Query API (OLAP) is 100% unchanged
• Must use updated provider (OLEDB, ADOMD)
• Connect via workbook URL
• Subset of cube concepts supported
• No true hiearachies for instance
• All MS BI now centered on SharePoint
• This was bottom up process, not an executive edict…
• …which makes it a much more significant trend
• …and it’s also why we havenot said this as loudly as I’d like
• Complete BI solutions will now often involve:
• SharePoint Install/Config
• Integration between BI apps
• BI integration into existing SharePoint solutions
• Custom web part development (or integration)
• SharePoint authentication and security
• SharePoint pros more aware of BI than I had expected
Contact Info
• Send me questions!
• rcollie@microsoft.com
• http://PowerPivotPro.com
• These demos, in detail
• More demos, how-to’s
• Observations
• Humor (sometimes)
• http://PowerPivotFAQ.com
• And Portuguese translation! :)
• Official Site http://powerpivot.com
• Promo and how-to videos
• Virtual lab – try PowerPivot with no install
• Official team blog http://blogs.msdn.com/powerpivot/
• http://powerpivotpro.com/powerpivot-beta/
• Temp Mashup Demo
• Workbook http://bit.ly/3wt59J
• Posts/Videos http://bit.ly/1kBllz
• DAX Measure Tutorial http://bit.ly/2megXY
• The Great Football Project
• All posts: http://bit.ly/19OXdh
• Start from beginning: http://bit.ly/4Fi6CE
• Cube formulas
• http://bit.ly/6IjmJi and http://bit.ly/7olQb3
• Architecture http://bit.ly/bPnWHo
• DAX-specific FAQhttp://bit.ly/cRtbSu
• Data Feeds http://bit.ly/2igFYB
• Links to many PowerPivot experts’ sites
• http://bit.ly/7RKa6C