1 / 13

Self-Service BI with PowerPivot

Self-Service BI with PowerPivot. Melissa Meyer. Covenant Technology Partners. Senior Consultant. mmeyer@mailctp.com. Housekeeping. Follow SharePoint Saturday St. Louis on Twitter @ spsstlouis and hashtag # spsstl

trevor
Download Presentation

Self-Service BI with PowerPivot

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. Self-Service BI with PowerPivot Melissa Meyer Covenant Technology Partners Senior Consultant mmeyer@mailctp.com

  2. Housekeeping • Follow SharePoint Saturday St. Louis on Twitter @spsstlouisand hashtag #spsstl • Play “Sponsor Bingo” to register for your chance to win one of the many great giveaways at the end of the day • Schedule and evaluate each session you attend via our mobile app that can be used across devices at http://spsaturday.cloudapp.net

  3. Outline • What is PowerPivot and why does it rock? • What’s new in the newest edition? • Demo • Questions

  4. PowerPivot for Excel • Free Add-in for Excel • Import, Model, Relate data from disparate sources • SSAS • Excel • Relational DBs • Create Calculated fields and use them anywhere • xVelocity In Memory Engine • Store Millions of rows of data in Excel

  5. PowerPivot for SharePoint • Server hosting of PowerPivot Data • Scheduled Refresh • Leverages SharePoint to administer and secure PowerPivot workbooks • Ability to easily collaborate and share PowerPivot workbooks • Source for Power View, SSRS Reports and Excel

  6. DAX • Based on Excel formulas and relational concepts • Easy to get started • Ability to create complex calculations • Recursion • Time Intelligence • Lookups based on Relationships • Examples TotalSales:= SUM ( [SalesAmount] ) SalesYTD:= TOTALYTD ( SUM ( [SalesAmount] ) , ’Date’ [FullDateAlternateKey] , ALL ( Date ) ) SalesPctOfQuota:= IF ( ISFILTERED ( 'Date‘ [EnglishMonthName] ) , BLANK() , DIVIDE ( SUM ( ResellerSales [SalesAmount] ) , SUM ( [SalesAmountQuota] ) , 0 ) ) Types of Functions • Date and Time • Filter • Logical • Math and Trig • Statistical • Time Intelligence

  7. Date Best Practices • Create a Date table • Date table should have a continuous range of dates – no gaps • Mark it as Date table • Lowest grain should be day without hours/min/secs

  8. What’s New In PowerPivot • New DAX Functions • USERELATIONSHIP • DISTINCTCOUNT • RANK • CROSSJOIN • LOOKUPVALUE • SWITCH • PATH • PATHITEM • And More… • Diagram View • Hierarchies • Calculation Area • Perspectives • KPIs • Date Table Settings • And More…

  9. DEMO

  10. Session Evaluations • Schedule and evaluate each session you attend via our mobile app that can be used across devices at http://spsaturday.cloudapp.net • You will be able to evaluate a session 25 minutes before the scheduled end time • Evaluations are stored anonymously and your feedback is appreciated • The app will be the only method available to submit session evaluations for the event and we hope you find it intuitive and convenient

  11. Thanks to Our Sponsors! Raffle Gold Silver Silver

  12. Resources • How to get PowerPivot for Excel 2010 and 2013 • http://office.microsoft.com/en-us/excel/download-powerpivot-HA101959985.aspx • PowerPivot Resources • http://technet.microsoft.com/en-us/sqlserver/dn151372.aspx • http://www.powerpivotpro.com/ • http://www.powerpivotblog.nl/

  13. Thank You! Questions?

More Related