1 / 20

Deep Dive into PowerPivot in Office and SharePoint

Deep Dive into PowerPivot in Office and SharePoint. Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft. Please silence cell phones. Goal. Deep dive into Excel Services and PowerPivot for SharePoint 2013 based on a sample Twitter application that analyzes Tweets according to

idania
Download Presentation

Deep Dive into PowerPivot in Office and SharePoint

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. Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft

  2. Please silence cell phones

  3. Goal Deep dive into Excel Services and PowerPivot for SharePoint 2013 based on a sample Twitter application that analyzes Tweets according to customizable search terms.

  4. DemoXLTweet

  5. Our starting point… Thanks to author Aaron Meyers for permissions to reuse his original code.

  6. Solution Architecture Overview Client Front-End Service Apps Analysis Services SPClient EWA Xlviewer.aspx Excel Calculation Services (ECS) MSOLAP Browser Refresh Interactive SSPM PPS Web Service Power View PPS Service App RS add-in SPClient ADOMD.NET Data Model Excel client RS Service App PowerPivot Web Service Web Parts SPClient PowerPivot System Service Data Refresh Timer Job XML/A Client Refresh Direct WorkItem DataImport TimerJob Import Now DataImport TimerJob Twitter Data Import during Model Processing Refresh Now XLTweet.xlsx Office Apps SharePoint Content Database Status List Search Terms List Tone Dictionary List SharePoint Work Item Queue Twitter Twitter Import DB PPivot App Database (WorkQ Table) Search Terms Import Now

  7. What we needed to get started Client Front-End Service Apps Analysis Services SPClient EWA Xlviewer.aspx Excel Calculation Services (ECS) MSOLAP Browser XLTweet.xlsx Refresh Interactive SSPM Power View RS add-in RS Service App SPClient Data Model ADOMD.NET Excel client PowerPivot Web Service SPClient Office 2013 ProPlus SharePoint 2013 Enterprise SQL Server Analysis Services SP1 in SharePoint mode SQL Server 2012 DBMS Visual Studio 2012 with SharePoint 2013 Project Types PowerPivot System Service Data Refresh Timer Job XML/A Client PPivot App Database (WorkQ Table)

  8. Our demo lab in Windows Azure

  9. What we built Client Front-End Service Apps Analysis Services SPClient EWA Xlviewer.aspx Excel Calculation Services (ECS) MSOLAP Browser Refresh Interactive SSPM Power View RS Service App RS add-in SPClient ADOMD.NET Data Model Excel client PowerPivot Web Service Web Parts SPClient PowerPivot System Service Data Refresh Timer Job XML/A Client Refresh Direct WorkItem DataImport TimerJob Import Now DataImport TimerJob Twitter Data Import during Model Processing Refresh Now XLTweet.xlsx Office Apps SharePoint Content Database Status List Search Terms List Tone Dictionary List SharePoint Work Item Queue Twitter Twitter Import DB PPivot App Database (WorkQ Table) Search Terms Import Now

  10. What key features our solution uses Browser Interactivity and Web Parts Office Apps Workbooks as a Data Source Power Pivot Timer Jobs and Scheduled Data Refresh Data Feed Support (List Import)

  11. Workbooks as a Data Source Client Front-End Service Apps Analysis Services SPClient EWA Xlviewer.aspx Excel Calculation Services (ECS) MSOLAP Browser XLTweet.xlsx Refresh Interactive SSPM Power View RS add-in RS Service App SPClient Data Model ADOMD.NET Excel client PowerPivot Web Service SPClient PowerPivot System Service Data Refresh Timer Job XML/A Client

  12. DemoAccessing a Workbook as a DataSource

  13. Scheduled Data Refresh User Interface (Manage Data Refresh page) XLSX PowerPivot System Service Twitter Secure Store Service (Refresh Credentials) Excel Calculation Services SharePoint Timer Service PowerPivot Data Refresh Timer Job Analysis Services Engine SharePoint Content Database PowerPivot App Database (Work Queue and Refresh History) SharePoint Configuration Database RDBMS Server

  14. DemoRunning a Scheduled Data Refresh on Demand

  15. Excel Services SOAP API for Refresh HttpContext.Current = null; LogStatus(currentSite, "Performing PowerPivot Workbook Refresh", "Refresh Direct"); Status[] status; ExcelServiceecs = newExcelService(this.workbookPath); stringsessionId = ecs.OpenWorkbookEx(this.workbookPath, "en-US", "en-US", true, out status); CheckExcelServicesReturnValue(status); WorkbookModelInfomodelInfo = ecs.EnsureWorkbookModel(sessionId, out status); CheckExcelServicesReturnValue(status); if (modelInfo.Version == 15) { string[] workbookConnections = ecs.GetWorkbookConnections(sessionId, out status); CheckExcelServicesReturnValue(status); if (workbookConnections != null && workbookConnections.Length > 0) { ecs.RefreshEx(sessionId, workbookConnections[workbookConnections.Length - 1], null, out status); CheckExcelServicesReturnValue(status); ecs.SaveWorkbook(sessionId, out status); CheckExcelServicesReturnValue(status); } } ecs.CloseWorkbook(sessionId, out status); CheckExcelServicesReturnValue(status);

  16. DemoBuilding the workbook

  17. A mention can also be an author • Hashtags are shared Time table allows us to show friendly time strings Tone Dictionary allows to calculate a tone score Tweets have: Authors | Mentions Timestamps HashTags Type (Tweet | ReTweet)

  18. List Office App <?xml version="1.0" encoding="utf-8"?> <OfficeAppxmlns="http://schemas.microsoft.com/office/appforoffice/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ContentApp"> <Id>ab2bcc59-5cd3-4fbf-9fda-666ed674fa51</Id> <Version>1.0.0.1</Version> <ProviderName>Microsoft</ProviderName> <DefaultLocale>en-US</DefaultLocale> <DisplayNameDefaultValue="Twitter App"> </DisplayName> <Description DefaultValue="Twitter App Panel"> </Description> <IconUrlDefaultValue="http://jonlaumain-t1/cc/AgaveIcons.png"> </IconUrl> <Capabilities> <Capability Name="Workbook"></Capability> </Capabilities> <DefaultSettings> <SourceLocationDefaultValue="http://sp2013demo/SitePages/SearchEmbeddedApp.aspx"> </SourceLocation> <RequestedWidth>400</RequestedWidth> <RequestedHeight>400</RequestedHeight> </DefaultSettings> <Permissions>ReadWriteDocument</Permissions> <AllowSnapshot>true</AllowSnapshot> </OfficeApp>

  19. Win a Microsoft Surface Pro! Complete an online SESSION EVALUATION to be entered into the draw. Draw closes April 12, 11:59pm CTWinners will be announced on the PASS BA Conference website and on Twitter. Go to passbaconference.com/evalsor follow the QR code link displayed on session signage throughout the conference venue. Your feedback is important and valuable. All feedback will be used to improve and select sessions for future events.

  20. Thank you!Questions and Answers? diego@Microsoft.com Twitter: @doppenhe kayu@Microsoft.com Diamond Sponsor Platinum Sponsor

More Related