1 / 31

Mastering PowerPivot for Excel and SharePoint: Insights from Virtual Tech Days 2010

Join Joy Rathnayake, MVP and Microsoft Certified Trainer, as he guides you through the advanced features of PowerPivot for Excel 2010 and SharePoint 2010 from the Virtual Tech Days 2010 event held in India. This comprehensive session covers loading and preparing data, creating interactive reports, and integrating with SharePoint for data management. Learn how to leverage PowerPivot to enhance your business intelligence capabilities and improve your data analysis workflow. Ideal for Excel users, SharePoint aficionados, and BI professionals!

nerita
Télécharger la présentation

Mastering PowerPivot for Excel and SharePoint: Insights from Virtual Tech Days 2010

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. INDIA │ 22-24 November2010 virtual techdays PowerPivot for Excel 2010 and SharePoint 2010 Joy Rathnayake│ MVP

  2. INDIA │ 18-20 august2010 virtual techdays • MVP – SharePoint Server • Microsoft Certified Trainer (MCT) • Author • Tech-Ed Speaker • Blog – http://www.joyr.ws • Web – http://www.SharePoint24x7.com • Email – send2joy@gmail.com Joy Rathnayake – Who am I?

  3. INDIA │ 18-20 august2010 virtual techdays • Introducing PowerPivot for Excel • Loading and preparing Data into PowerPivot • Creating and designing PowerPivot Reports • Introducing PowerPivot for SharePoint • Working with PowerPivot Workbooks in SharePoint • Using the PowerPivot Management Dashboard S E S S I O N A G E N D A

  4. INDIA │ 18-20 august2010 virtual techdays • What feature, which is present in just about every BI tool/ application on the market, helps people to edit, format, customize, share, print, and archive their work? Speaking of familiar tools…

  5. INDIA │ 18-20 august2010 virtual techdays

  6. INDIA │ 18-20 august2010 virtual techdays • Excel has been users’ favorite tool for data • Starting with Excel 2007 MS invested heavily in Business Intelligence in Excel • Lots of new and redesigned BI features in Excel • Excel Services extends the reach of spreadsheets beyond the desktop • The goal: • Make Excel & Excel Services the tool of choice for users doing Business Intelligence Setting the scene…

  7. INDIA │ 18-20 august2010 virtual techdays Excel 2010 overview Excel 2010 helps you communicate your data clearly, work from anywhere and gain deeper insight into your data - all with better performance and scalability.

  8. INDIA │ 18-20 august2010 virtual techdays Introducing PowerPivot PowerPivot for Excel 2010 PowerPivot for SharePoint 2010

  9. INDIA │ 18-20 august2010 virtual techdays • A collection of applications and services that supports the creation of user-managed, data-driven BI solutions • Supports relating and enriching large volumes of heterogeneous data • Visualizes data using PivotTables, PivotCharts, filters, and slicers • Solutions may be published to, and managed in, SharePoint • Functionality is delivered by two add-ins: • SQL Server PowerPivot for Excel add-in • SQL Server PowerPivot for SharePoint add-in What is PowerPivot?

  10. INDIA │ 18-20 august2010 virtual techdays • Client add-in that extends Excel 2010 to support extremely large data sets • Achieved with a client-side version of Analysis Services, known as the VertiPaq processor • A separate PowerPivot Window is used to load, explore, relate, and enrich data with calculations • Imports and relates data from corporate, local, and ad hoc data stores • Results in a new data source embedded in the Excel workbook that becomes the basis for interactive reports PowerPivot for Excel 2010 Add-in

  11. INDIA │ 18-20 august2010 virtual techdays • Excel users who know how to structure and work with relational data in workbooks • SharePoint users who use team sites and document management features • Database and IT professionals who: • Want to delegate business data development tasks to those who need the data most • Need to retain sufficient control so that data can be secured, monitored, reproduced, and archived Who is the PowerPivot for Excel 2010?

  12. INDIA │ 18-20 august2010 virtual techdays PowerPivot Workflow

  13. INDIA │ 18-20 august2010 virtual techdays • The interface used to load, explore, relate, and enrich data • Launched from the PowerPivot tab in the Excel ribbon • Provides wizards and tools to source data from: • The clipboard (copy and paste) • Databases and data feeds • Local Excel workbook tables • Relationships can be defined to allow filtering of data using related columns and for looking up values in related tables • When saved, all PowerPivot data and metadata is embedded within the Excel workbook Introducing the PowerPivot Window

  14. INDIA │ 18-20 august2010 virtual techdays • Copying and Pasting Data • Loading External Data • Linking Excel Tables Loading Data

  15. INDIA │ 18-20 august2010 virtual techdays • Managing Tables and Columns • Managing Relationships Preparing data in PowerPivot

  16. INDIA │ 18-20 august2010 virtual techdays • Reports based on PowerPivot models can be produced with: • PivotTables • PivotCharts • CUBE functions Creating PowerPivotreports

  17. INDIA │ 18-20 august2010 virtual techdays • Reports can be further enhanced with native Excel features: • Conditional formatting • Slicers • Charts • Sparklines Formatting reports

  18. INDIA │ 18-20 august2010 virtual techdays DEMO: Exploring PowerPivotfor Excel 2010 Joy Rathnayake│ MVP

  19. INDIA │ 18-20 august2010 virtual techdays • Create database views in corporate databases to prepare and facilitate the loading of data • Develop custom data services to pipe XML data to PowerPivot workbooks via data feeds • Manage Data Feed Libraries in SharePoint 2010 • Develop PowerPivot models built on top of existing LOB applications • Develop proof-of-concept applications • Note: • PowerPivot was designed for self-service BI, so there are limited development opportunities in the 2010 release • The SQL Server PowerPivot for Excel add-in cannot be enhanced or automated Developer Opportunities - Excel

  20. INDIA │ 18-20 august2010 virtual techdays • Provides centralized storage and management of workbooks created by using PowerPivot for Excel • Uses Excel Services to enable browser access to workbooks • Processes workbook data in special server-based instance of Analysis Services • Complements traditional Analysis Services implementations by supporting integration of data sources external to data warehouse • Includes tools for IT to monitor usage of specific workbooks and impact on server PowerPivotfor SharePoint 2020 Add-in

  21. INDIA │ 18-20 august2010 virtual techdays • Excel users who • Integrate and analyze data using PowerPivot workbooks • Need to share results with other users • SharePoint users who • Use team sites and document-management features to store information and collaborate with colleagues • Consume and reuse results of analysis stored in PowerPivotworkbooks • Database and IT professionals who • Want to delegate business data development tasks to those who need the data most • Need to retain sufficient control so that data can be secured, monitored, reproduced, and archived Who is the PowerPivot for SharePoint?

  22. INDIA │ 18-20 august2010 virtual techdays PowerPivot for SharePoint 2010 - Architecture

  23. INDIA │ 18-20 august2010 virtual techdays • PowerPivot Gallery • PowerPivot Data Refresh • PowerPivot Web Service Working with PowerPivot in SharePoint 2010 http://<site>/PowerPivot Gallery/myPowerPivotWorkbook.xlsx

  24. INDIA │ 18-20 august2010 virtual techdays DEMO: Exploring PowerPivotfor SharePoint 2010 Joy Rathnayake│ MVP

  25. INDIA │ 18-20 august2010 virtual techdays • Manage Data Feed Libraries in SharePoint 2010 • Develop applications that consume PowerPivot models using MDX queries • Excel • Reporting Services • Custom applications Developer opportunities – SharePoint 2010

  26. INDIA │ 18-20 august2010 virtual techdays • PowerPivot Management Dashboard • PowerPivot Usage Data Collection Using the PowerPivot Management Dashboard

  27. INDIA │ 18-20 august2010 virtual techdays • Collection of Web parts and PowerPivot reports that display data related the PowerPivot service application • Server health • Query response times • Workbook usage • Data refresh status • Access to dashboard is limited to service application administrators • Usage data available in PowerPivot Management Data.xlsx report for customization PowerPivot Management Dashboard

  28. INDIA │ 18-20 august2010 virtual techdays PowerPivot Usage Data Collection

  29. INDIA │ 18-20 august2010 virtual techdays • Microsoft PowerPivotWebsite • http://www.powerpivot.com • PowerPivot Product Team Blog • http://blogs.msdn.com/powerpivot RESOURCES

  30. INDIA │ 18-20 august2010 virtual techdays • SharePoint 2010 and Social Computing • Harish Ranganathan • 3.00 pm • FAST Search Server 2010 for SharePoint • VedantKulshreshtha • 4.15 pm RELATED CONTENT

  31. THANKS│18-20 august2010 virtual techdays send2joy@gmail.com │ http://www.joyr.ws http://www.SharePoint24x7.com │

More Related