Christian E Stich Excel Services Consulting LLC www xlservicesconsulting com PRACTICAL EXCEL SERVICES PUGET SOUND SHAREPOINT USERS GROUP JULY 15 2010 Exc by mwq69184

VIEWS: 412 PAGES: 26

More Info
									Christian E. Stich
Excel Services Consulting, LLC
www.xlservicesconsulting.com




   PRACTICAL EXCEL SERVICES
        PUGET SOUND SHAREPOINT USERS GROUP
                   JULY 15, 2010
Excel Services Overview
 Premier Microsoft Business Intelligence platform
    Others (e.g. Proclarity) have been deprecated or extensively use
     Excel Services (e.g. Performance Point Services)
 Server version of Microsoft Excel
    Two tiered architecture:
          Web Front End and Excel Calculation Services
 Excel Services 2007 (v1) released as part of MOSS 2007
  Enterprise
 Excel Services 2010 (v2) released as part of SPS 2010
  Enterprise
 Excel Services 2010 also released as part of Windows Live
  SkyDrive (Excel Web App) and Microsoft BPOS (Business
  Productivity Online Suite)
 Excel Services:2007 vs. 2010

Excel Services 2007                  Excel Services 2010
Worksheets, Chartsheets, Charts,     Adds sparklines and slicers.
Array formulas, Tables, PivotTables,
Databars, Icon Sets, etc.
N/A                                  PowerPivot
Does not load workbooks with         Loads most workbooks with unsupported
unsupported features                 features in View Mode; loads many in Edit
                                     Mode
Charting                             Same
Performance                          Equivalent (on average the same)
 Excel Services:2007 vs. 2010

Excel Services 2007                    Excel Services 2010
View only, no edit support             Adds edit support (depending on SKU)
No support for collaboration           Multiple people can collaborate online on
                                       the same document
Support for Named Item View,           Adds scratchpad functionality to Excel
Parameters and SharePoint Web          Services Web Parts
Part framework/Dashboard pages
Supports SharePoint permissions,       Same
check-in/check-out, versioning, etc.
Web Services API, User Defined         Adds JavaScript Object Model, REST API;
Functions                              also updated Web Services API
Coming from Excel Client
What is different? What doesn’t work?

 Same calculation engine as Excel Client (2007 and
  2010 respectively)
   Some rarely used Excel Client functions (e.g. Info) are not
    supported
   Note: Date/Time return the date and time of the server,
    which may be in a different time zone!
 External data connectivity: SharePoint list access not
  natively supported -> can write UDF to accomplish
  this
  Consuming Sharepoint Lists in Excel Services
 Query Tables: updates not supported by Excel
  Services – instead cached (last refreshed) data is
  shown
Coming from Excel Client
What is different? What doesn’t work?

 Sheet/cell protection: not supported
 Comments: not supported
 Data validation: not supported
 User Interface
   No ribbon in Excel Services 2007, only toolbar at top
   Scrolling in Excel Services 2007: combination of scroll
    bars (local scrolling) and paging buttons
   No editing in Excel Services 2007
   Ribbon in Edit Mode in Excel Services 2010 – subset of
    Excel Client 2010 functionality
Coming from Excel Client
What is different? What doesn’t work?

 Unsupported features – still not displayed in 2010, but files
  are opened
    In 2007: many unsupported features were load blocking
    In 2010: many previously load blocking are no longer load
     blocking in VIEW mode; some unsupported features are load
     blocking in EDIT mode
   Improved Handling of Unsupported Features

 Parallel processing
    Excel Services 2007 and 2010 calculate different user sessions in
     parallel
    But no support for multi-threaded recalc within a single session

 Charting: Excel Services flattens Excel Client 2.5D (pseudo
  3D) charts to 2D; no shading support
Coming from Excel Client
What is different? What doesn’t work?

 Programmability model differs:
   No support for VBA or early Excel macros on
    server
   Both support User Defined Functions but there are
    differences
   Additional APIs on server: JavaScript Object
    Model, Web Services API, REST API

  See also:
  Differences between using a workbook in Excel and Excel Services
Excel Services
Features not present in Excel Client

 Named Item View
 Parameters
   Single cell named ranges defined as parameters
 Sessions
 Settings – adjusted using SharePoint Central
  Administration
   Global
   Trusted File Location (Note: may span multiple
    Document Libraries)
Excel Services 2010 – Edit Mode

 Installed SharePoint Server 2010 Enterprise
  Edition, but Excel Services does not let me edit a
  workbook???
   No “Edit in Browser” in the menu when clicking on a
    file in a SharePoint Document Library
   No “Edit in Browser” tab when viewing file using Excel
    Services
     -> Need to perform an additional installation step.
    Only customers who have licensed the Office Web
    Apps will be able to enable Edit Mode in SharePoint
    Server 2010 Enterprise Edition.
  Deploy Office Web Apps (Installed on SharePoint 2010 Products)
Excel Services 2010 – Scratchpad

 Requires the Office Web Apps to be installed on
  SharePoint – see previous slide
   This enables the “Allow typing and formula entry”
    property for the Excel Web Access Web Part
   Scratchpad is disabled by default on the xlviewer.aspx
    page. It can be enabled by adding the following query
    string: &AllowTyping=1


 Changes made in Scratchpad mode will not be
  saved back to the workbook and are only
  temporary
Common issues and cures

 SharePoint Server installed, but Excel Services
  does not work
 Getting an error when attempting to open a
  workbook using Excel Services
 Charts appear in black and white/greyscale, not
  color
 Running out of user sessions
 External data connections
   update connection in workbook, but no change when
    viewing using Excel Services
   Does not refresh
Common issues and cures

 Multiple Excel Services web parts using the
  same workbook on a dashboard page –
  changing values/drilling in one does not
  change the others?
SharePoint Server installed, but Excel
Services does not work

 Requires Enterprise edition, Standard edition
  does not support Excel Services
   Switch product key
 Enterprise features may not have been enabled
   Enable enterprise features
   Enable enterprise features on existing sites
 Excel Services may not be enabled
   Excel Services pre-configured for stand-alone
    installations
   Excel Services NOT pre-configured for farm
    installations
Getting an error when attempting to open a
workbook using Excel Services

 Correlation ID error (common in Beta releases)
      Wait for SharePoint to finish starting up
        Retry a minute or so later
      Possibly corrupted installation
 Unable to open workbook
    Wait for Excel Services components to finish starting up -> suggestion:
     Use script to activate Excel Services (loaded on demand by SharePoint)
    Corrupt workbook -> try in Excel Client (for 2007 use Excel 2007, for 2010
     use Excel 2010)
    User does not have permissions to view, open, or edit workbook; also
     SharePoint document library settings
    Excel Services does not allow workbook to be loaded (SharePoint,
     Windows Live SkyDrive)
        Unsupported features: use Compatibility Checker
        Upgrade to Excel Services 2010
      Excel Services incorrectly or not configured
Charts appear in black and
white/greyscale, not color
 Common problem when using Remote
 Desktop
   Verify that Display->Color Depth is set to highest
    settings (24 or 32 bit)

Running out of user sessions
 Common problem when using editing web part pages –
  each edit (or otherwise triggered page reload) causes new
  sessions to be created
    Adjust sessions per user
    Check web part property “Close Session before
      Opening a New One”
External data connections

 Updated connection in workbook, but no effect
  when viewing using Excel Services
 External data does not refresh as expected when
  refresh is requested
   ODC file in SharePoint Data Connection Library may
    override data connection defined in Excel workbook ->
    check Excel Services settings
   Check cache settings and periodic refresh/refresh on
    open for Excel Services
   Note: The lack of data updates can also occur with
    volatile functions, including User Defined Functions
    declared as volatile -> verify settings
Changes in one web part are not reflected in a
different web part, even though both use the
same workbook…
 Even though the web parts use the same underlying
  web part, they are using different sessions which are
  completely independent.
   Excel Web Access web parts can consume data via the
    SharePoint web part framework, but they are NOT data
    providers.
   Use filter web parts (choice, text, user, Analysis Services,
    etc.) and pass the output into one or more Excel Web
    Access web parts
   If communication between two or more Excel Web Access
    web parts is desired (using the same or different
    workbooks) -> use JavaScript code (inserted using a
    content editor web part or by editing the .aspx page) to
    access the Excel Services JavaScript Object Model. The
    JSOM can be used to pass data between different Excel
    Web Access web parts.
Design considerations for Workbooks
with Excel Services
 Use Named Item View and SharePoint
  permissions to protect intellectual property
 Excel Services 2007: combination of scrollbars
  and paging make navigation difficult ; not an
  issue with Excel Services 2010
 High fidelity representation of Excel
  workbooks but some minor differences:
   2D charts only
   Charts do not necessarily match client charts pixel
    for pixel
Design considerations for Workbooks
with Excel Services
 No VBA/macro support
   Explore alternatives including
     Utilize Excel Services APIs
     Utilize advanced Excel functions (lookup, array
      formulas, etc.) for same functionality
 Performance
   What works well in Excel Client typically works
    well in Excel Services (same calculation engine)
Many more areas...
What would you like to see?
 Dashboarding – the SharePoint web part
  framework:
   Excel Services web parts
   Filter and Analysis Services web part
   Using the JavaScript Object Model to make an
    Excel Services web part a data provider or for
    communication between multiple Excel Services
    web parts
Many more areas...
What would you like to see?
 Programmability in depth
   Building web applications (including mashups)
    using the JavaScript Object Model
   Using the Web Services API to use Excel Services
    as a calculation/model engine for your application
   A primer on the REST API – how to use it on web
    pages, in MS Word/PowerPoint, or to build
    mashups using other tools such as Google Maps,
    etc.
   Creating hyperthreaded UDFs. Creating
    ‘asynchronous’ UDFs for Excel Services.
Many more areas...
What would you like to see?
 Porting applications originally written in Excel
  client to Excel Services
   Converting Excel User Defined Functions to Excel
    Services User Defined Functions
   Writing User Defined Functions that work for
    Excel Client and Excel Services
   Using the JavaScript Object Model/Web Services
    API in place of VBA
Many more areas...
What would you like to see?
 Special considerations when using Edit Mode
   Check-in/Checkout
   Collaborative editing – multiple users (people
    and/or Web Services API)
   UDFs and external data when editing
    collaboratively
 Excel and Excel Services - PowerPivot
 Excel Services and Visio Services
  Building applications that utilize both
Many more areas...
What would you like to see?
 Excel Services and SharePoint Workflow
 Mobile Excel Web Access
 Excel Services Performance Counters and
  Logs
 Setting up external data connections;
  authentication/Kerberos
 Security and Excel Services
                     Christian E. Stich
              Excel Services Consulting, LLC

Slides will be uploaded to: www.xlservicesconsulting.com
Questions, suggestions? excelservices@cstich.com

PRACTICAL EXCEL SERVICES

								
To top