![Page 1: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/1.jpg)
April 10-12, Chicago, IL
Deep Dive into PowerPivot in Office and SharePointDiego Oppenheimer, MicrosoftKay Unkroth, Microsoft
![Page 2: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/2.jpg)
April 10-12, Chicago, IL
Please silence cell phones
![Page 3: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/3.jpg)
4
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.
![Page 4: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/4.jpg)
April 10-12, Chicago, IL
Demo
XLTweet
![Page 5: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/5.jpg)
Thanks to author Aaron Meyers for permissions to reuse his original code.
Our starting point…
![Page 6: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/6.jpg)
7SharePoint Content Database
Solution Architecture Overview
RS add-in
PPS Web Service
PowerPivot Web
Service
Browser
Excel client
Excel Calculation
Services (ECS)
PPS Service App
RS Service App
PowerPivot System Service
Client Front-End Service Apps Analysis
Services
AD
OM
D.N
ET
SPC
lien
t
SPClient
XML/A Client
MSOLAP
SPClient
Data Model
Data Refresh
Timer Job
SSPM
EWA
PPivot App Database
(WorkQ Table)
TwitterImport DB
Xlviewer.aspx
XLTweet.xlsx
Office Apps
Import Now
Search
Terms
WorkItemDataImport
TimerJob
DataImportTimerJob
Status List
Twitter Data
Import during Model
ProcessingSearchTerms List
Tone Dictionary
List
SharePoint Work Item
Queue
Web Parts
Import Now
Refresh Now
Refresh Direct
Refresh Interactive
Power View
![Page 7: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/7.jpg)
8
What we needed to get started
RS add-in
PowerPivot Web
Service
Browser
Excel client
Excel Calculation
Services (ECS)
RS Service App
PowerPivot System Service
Client Front-End Service Apps Analysis
Services
AD
OM
D.N
ET S
PC
lien
t
SPClient
XML/A Client
MSOLAP
SPClient
Data Model
Data Refresh
Timer Job
SSPM
EWA
PPivot App Database
(WorkQ Table)
Xlviewer.aspx
XLTweet.xlsx Refresh Interactive
Office 2013 ProPlusSharePoint 2013 EnterpriseSQL Server Analysis Services SP1 in SharePoint modeSQL Server 2012 DBMSVisual Studio 2012 with SharePoint 2013 Project Types
Power View
![Page 8: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/8.jpg)
9
Our demo lab in Windows Azure
![Page 9: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/9.jpg)
10SharePoint Content Database
What we built
RS add-in
PowerPivot Web
Service
Browser
Excel client
Excel Calculation
Services (ECS)
RS Service App
PowerPivot System Service
Client Front-End Service Apps Analysis
Services
AD
OM
D.N
ET SPC
lien
t
SPClient
XML/A Client
MSOLAP
SPClient
Data Model
Data Refresh
Timer Job
SSPM
EWA
PPivot App Database
(WorkQ Table)
TwitterImport DB
Xlviewer.aspx
XLTweet.xlsx
Office Apps
Import Now
Search
Terms
WorkItemDataImport
TimerJob
DataImportTimerJob
Status List
Twitter Data
Import during Model
ProcessingSearchTerms List
Tone Dictionary
List
SharePoint Work Item
Queue
Web Parts
Import Now
Refresh Now
Refresh Direct
Refresh Interactive
Power View
![Page 10: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/10.jpg)
11
What key features our solution uses
Browser Interactivityand Web Parts
Workbooks as a Data Source
Timer Jobs and Scheduled Data
RefreshPower Pivot
Office Apps
Data Feed Support(List Import)
![Page 11: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/11.jpg)
12
Workbooks as a Data Source
RS add-in
PowerPivot Web
Service
Browser
Excel client
Excel Calculation
Services (ECS)
RS Service App
PowerPivot System Service
Client Front-End Service Apps Analysis
Services
AD
OM
D.N
ET S
PC
lien
t
SPClient
XML/A Client
MSOLAP
SPClient
Data Model
Data Refresh
Timer Job
SSPM
EWAXlviewer.aspx
XLTweet.xlsx Refresh Interactive
Power View
![Page 12: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/12.jpg)
April 10-12, Chicago, IL
Demo
Accessing a Workbook as a DataSource
![Page 13: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/13.jpg)
14
Scheduled Data Refresh
Analysis Services Engine
PowerPivot App Database (Work
Queue and Refresh History)
SharePoint Content
Database
SharePoint Configuration Database
XLSX
RDBMS Server
PowerPivot System Service
Excel Calculation
ServicesPowerPivot Data
Refresh Timer Job
User Interface(Manage Data Refresh page)
SharePoint Timer Service
Secure Store Service (Refresh Credentials)
![Page 14: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/14.jpg)
April 10-12, Chicago, IL
Demo
Running a Scheduled Data Refresh on Demand
![Page 15: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/15.jpg)
Excel Services SOAP API for Refresh
HttpContext.Current = null; LogStatus(currentSite, "Performing PowerPivot Workbook Refresh", "Refresh Direct"); Status[] status; ExcelService ecs = new ExcelService(this.workbookPath); string sessionId = ecs.OpenWorkbookEx(this.workbookPath, "en-US", "en-US", true, out status); CheckExcelServicesReturnValue(status); WorkbookModelInfo modelInfo = 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);
![Page 16: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/16.jpg)
April 10-12, Chicago, IL
Demo
Building the workbook
![Page 17: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/17.jpg)
18
Tweets have:Authors | Mentions
TimestampsHashTags
Type (Tweet | ReTweet)
Tone Dictionary allows to
calculate a tone score
Time table allows us to show
friendly time strings
• A mention can also be an author
• Hashtags are shared
![Page 18: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/18.jpg)
List Office App<?xml version="1.0" encoding="utf-8"?><OfficeApp xmlns="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> <DisplayName DefaultValue="Twitter App"> </DisplayName> <Description DefaultValue="Twitter App Panel"> </Description> <IconUrl DefaultValue="http://jonlaumain-t1/cc/AgaveIcons.png"> </IconUrl> <Capabilities> <Capability Name="Workbook"></Capability> </Capabilities> <DefaultSettings> <SourceLocation DefaultValue="http://sp2013demo/SitePages/SearchEmbeddedApp.aspx"> </SourceLocation> <RequestedWidth>400</RequestedWidth> <RequestedHeight>400</RequestedHeight> </DefaultSettings> <Permissions>ReadWriteDocument</Permissions> <AllowSnapshot>true</AllowSnapshot></OfficeApp>
![Page 19: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/19.jpg)
20
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/evals or 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.
![Page 20: April 10-12, Chicago, IL Deep Dive into PowerPivot in Office and SharePoint Diego Oppenheimer, Microsoft Kay Unkroth, Microsoft](https://reader030.vdocuments.us/reader030/viewer/2022032517/56649c895503460f94942d9c/html5/thumbnails/20.jpg)
April 10-12, Chicago, IL
Thank you!Questions and Answers?
Diamond [email protected] Twitter: @[email protected]
Platinum Sponsor