SharePoint Conference 2012 – Advanced Dashboard Creation Using Excel, Excel Services, PerformancePoint and Apps for Office

Later on day 3 of the SPC12 conference I sat in a packed session with Kevin Donovan talking about some of the advances of SharePoint, Excel, and PerformancePoint. Kevin started by talking the different levels of Business Intelligence that are being move evident within today’s workplace. These include:

  • Personal Business Intelligence – using Excel and connecting to data (available On Premises and Cloud)
  • Self-Service Business Intelligence – create Excel and publish to SharePoint (available On Premises and limited Cloud)
  • Corporate Business Intelligence – authored using SharePoint, Excel Services, and PerformancePoint for the entire organization (available On Premises and very limited Cloud)

Kevin jumped into great advances within the Excel 2013 including:

  • Quick Analysis – right click on table to access and this gives you many tools to quickly analyze your information
  • Recommended Pivot Tables – when creating a Pivot Table this will bring in any other pieces of data, such as Power View and make them available
  • Timeline Slicer – similar to regular slicers except based on time
  • Power View – which is now included within Excel 2013 and not an add-in

Kevin continued to talked about some of the issues that arise when self-service Business Intelligence becomes more prevalent within organizations. People tend to start questioning the validity of the data and ask where it came from. Microsoft recently acquired an auditing product called Audit and Control Management server. This product will be able to audit changes make to spreadsheets, configure an custom alerts and even access historical information about the file. This will help with internal compliance and regulatory needs.

One of the newer tools that is available within SQL Server Data Tools (called BIDS previously) allows IT to bring in a PowerPivot set of data found within an Excel workbook and use within Analysis Services. This helps to move the presentation layer for the data from a Self-Service BI to Corporate BI mentality. Of course when the organization is ready to make the move.

From the Excel Services side for SharePoint 2013 there are a lot of new/improved features that have been plaguing this feature since SharePoint 2007. Field List/Field Well is the capability of tapping into data connection within the web browser to choose a dimension. This will negate the need to having to always open the client when working with your data. Another important feature is EffectiveUsername. This allows you to pass a username identity to Analysis Services which is set on the connection string. Ultimately it connects using the as the process identity for Excel Services and then uses the logged on user name to access the appropriate data.

Another thing that I always had a hard time with in SharePoint 2007 & 2010 was remembering what a named range/object was within the Excel spreadsheet. Now with Named Object View v2 we have the capability of choose these named ranged while in the browser and interacting with the information.

Kevin talked some about the new Microsoft App Store. This store is not just for Windows phone 8. Included in the App Store are applications that are created for use within SharePoint 2013 and Office 2013 products. For example, he highlighted an election map for the just recent election that allows you to see over time what happened for electoral and popular vote for the presidency. The Add Apps for Office can be found in the Insert Page of an Office 2013 product.



The Apps for Office currently out there are taking free data sets and creating a dashboard to help developer understand what is possible. These Apps can be created internally of your organization allowing you to tie into existing data sources. Once these Apps have been created they can be published to File Shares, the Microsoft Marketplace, or Corporate Catalogs within SharePoint.

The presentation moved to talked about changes within the PerformancePoint environment. Kevin started out by outline big picture some of the changes within this SharePoint 2013 service.

  • Themes – any theme that is applied to a SharePoint site will be carried over into the dashboard.
  • Dashboard Designer can now be initiated from the SharePoint ribbon.
  • Filter Enhancements & Filter Search – this feature allows you to drill down into information quickly, search for a specific piece of information within the filter and then bolds the hierarchy so you know which nodes you selected.
  • EffectiveUsername – is the same feature as within Excel and Excel Services.
  • Server-Side Migration – allows a dashboard or individual components within a Dashboard to be exported from one location and imported into another SharePoint location. The export/import will also allow you to include data connections and Excel spreadsheets that are associated to your dashboard. This can also be scripted by using PowerShell from the SharePoint server.
  • Dashboard Designer Authentication allows organizations to not only use Windows Authentication but also Forms Based Authentication when creating dashboards.

Overall I found this session to be very informative when it comes to using out of the box features to help deliver Business Intelligence information to the users. In the scheme of things the Excel/Excel Services components would be considered Crawl and some Walking. The PerformancePoint would probably start within the Walk and move into the Run phases for organizations with BI needs.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s