deep dive into powerpivot in office and sharepoint
DESCRIPTION
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 - PowerPoint PPT PresentationTRANSCRIPT
April 10-12, Chicago, IL
Deep Dive into PowerPivot in Office and SharePointDiego Oppenheimer, MicrosoftKay Unkroth, Microsoft
April 10-12, Chicago, IL
Please silence cell phones
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.
April 10-12, Chicago, IL
DemoXLTweet
Thanks to author Aaron Meyers for permissions to reuse his original code.
Our starting point…
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
ADOM
D.NE
T
SPCl
ient
SPClient
XML/A Client
MSOLAP
SPClient
Data Model
Data Refresh
Timer Job
SSPM
EWA
PPivot App Database
(WorkQ Table)Twitter
Import 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
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
ADOM
D.N
ET SPCl
ient
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
9
Our demo lab in Windows Azure
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
ADOM
D.NE
T SPCl
ient
SPClient
XML/A Client
MSOLAP
SPClient
Data Model
Data Refresh
Timer Job
SSPM
EWA
PPivot App Database
(WorkQ Table)Twitter
Import 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
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)
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
ADOM
D.N
ET SPCl
ient
SPClient
XML/A Client
MSOLAP
SPClient
Data Model
Data Refresh
Timer Job
SSPM
EWAXlviewer.aspx
XLTweet.xlsx Refresh Interactive
Power View
April 10-12, Chicago, IL
DemoAccessing a Workbook as a DataSource
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
Services PowerPivot Data Refresh Timer Job
User Interface(Manage Data Refresh page)
SharePoint Timer Service
Secure Store Service (Refresh Credentials)
April 10-12, Chicago, IL
DemoRunning a Scheduled Data Refresh on Demand
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);
April 10-12, Chicago, IL
DemoBuilding the workbook
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
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>
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.
April 10-12, Chicago, IL
Thank you!Questions and Answers?
Diamond [email protected] Twitter: @[email protected]
Platinum Sponsor