1 / 26

Introduction to PowerPivot: Explore Power BI and Excel Integration

Learn about PowerPivot, Excel integration, budget benefits, DAX measures, SharePoint advantages, BI demos, and more. Join the insightful session on January 27, 2010, in Stockholm by Rob Collie. Discover the power of PowerPivot and its impact on BI professionals.

elsie
Download Presentation

Introduction to PowerPivot: Explore Power BI and Excel Integration

An Image/Link below is provided (as is) to download presentation 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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Intro to PowerPivot Stockholm, Jan 27, 2010 Rob Collie, Sr. Program Mgr, PowerPivot

  2. “The funny thing about Europe”

  3. Agenda • Why PowerPivot? • Demos! • Why SharePoint? • Demos!

  4. Why PowerPivot? • Money • Biz logic bottleneck • Time • Cost • Lack of agility • Embrace and improve Excel • Manage and assist, not fight • Supplement centralized BI

  5. TempMashup Demo • Loading Data • Simple Pivot • Mashup with Internet data • Calc columns and relationships • DAX Measures

  6. 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

  7. Movie Explaining the Golden Rules • Pretty sure I lost people here • Probably (hopefully) clearer here: • http://bit.ly/ambRrv • http://bit.ly/bv7tBo

  8. 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”

  9. Schema Diagram

  10. Schema Diagram, cont’d

  11. Dimension Tables

  12. Fact Tables

  13. Football Demo #1: Measures

  14. The CALCULATE() Function • Your new best friend • Like SUMIF(), modernized =CALCULATE(<Aggregation>,<filter1>,<filter2>,…)

  15. Football Demo #2: Cube Formulas

  16. Why SharePoint? • XL Pro • (Practical)Security • No “Save As” hijacking • Guaranteed deployment of updates • Autorefresh • “Professionalized” results • DB Pro / IT • Transparency • Usage tracking • Focused point of engagement

  17. SharePoint Demos • Gallery • Autorefresh • Usage dashboard

  18. Bonus Slides!

  19. 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”

  20. 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()…

  21. 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

  22. My Advice for BI Pros: Learn About SharePoint • 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

  23. Contact Info • Send me questions! • rcollie@microsoft.com • Twitter: @powerpivotpro • http://PowerPivotPro.com • These demos, in detail • More demos, how-to’s • Observations • Humor (sometimes) • http://PowerPivotFAQ.com • Already > 100 questions answered • And Portuguese translation! :)

  24. More Info • 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/ • PowerPivot Beta Links • 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

  25. More Info Cont’d • 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

  26. More Info Cont’d • PowerPivot’s Impact on BI Pros • http://bit.ly/4D5h8Y • http://bit.ly/2eu4MU • (Mostly in comments on those pages) • SharePoint’s Rising Significance for BI • Pt 1 http://bit.ly/9XkxRs • Pt 2 http://bit.ly/c6lfNj • Pt 3 http://bit.ly/zFE0B

More Related