Intro to PowerPivot

Intro to PowerPivot

Télécharger la présentation

Intro to PowerPivot

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

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

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

27. Thanks to our platinum sponsors