A Business Intelligence (BI) Deep-dive into SharePoint 2013

Business Intelligence (BI) capabilities in SharePoint 2013 are vast and in many cases are achieved with a combination of SharePoint and Microsoft Office or SharePoint and SQL Server’s capabilities. I have worked with a variety of organizations in presenting SharePoint 2013‘s BI capabilities and have found that it is best done by showing examples rather than speaking in abstract so I have included a larger number of screen shots in this section with brief summaries as I believe this is the best way to get a complete grasp of SharePoint 2013 and Business Intelligence (BI).

The following image details SharePoint’s different tiers of Business Intelligence which include SharePoint, Office and in many cases Excel, as well as SQL Server:

Reviewing the Business Intelligence Center in SharePoint 2013

SharePoint 2013 provides a Business Intelligence Center site that enables your organization to centrally store and manage data connections, reports, scorecards, dashboards, Apps and Web Part pages as shown below:

Microsoft Excel 2013 & SharePoint 2013

Excel has more capabilities and more relevance in BI and SharePoint than any other Microsoft Office program. The following details the wide range of powerful reports, scorecards, and dashboards that can be accomplished with a combination of Excel and SharePoint 2013:

Excel Services & SharePoint 2013

Excel Services allows for manual emailing or sharing of spreadsheets that causes manual errors to stop and the ability to share workbooks with others.

The following is an overview of Excel Services 2013 and SharePoint 2013:

• Excel Services 2013 is a SharePoint service application that enables the loading, calculation, and browser-based rendering of Excel workbooks (Enterprise Edition only)
• Designed to be a scalable, robust, enterprise-class service that provides feature and calculation fidelity with Excel
• Delivers rich analysis capabilities that are supported in a zero-footprint web client with a familiar interface
• Live connections to data sources are queried to deliver up-to-date data throughout analysis

The following is a graphical example of the moving pieces of SharePoint 2013 and how they come together at a very high level:

PowerPivot & SharePoint 2013

PowerPivot is an add-in for Excel User that provides the ability to create powerful data models that can include calculated fields, reports and scorecards.

The following is a summary of PowerPivot and SharePoint 2013:

• PowerPivot for SharePoint extends SharePoint 2013 and Excel Services to add server-side processing, collaboration, and document management support for the PowerPivot workbooks published to SharePoint
• The PowerPivot Gallery, a special type of document library, is provided to browse published PowerPivot workbooks and to configure automatic data refresh
• The PowerPivot Service deploys the embedded data model to an Analysis Service instance within the SharePoint farm, and Excel Services is used to query the deployed data model
• Data connections can be defined to query the PowerPivot data model by using the document URL

PerformancePoint Services & SharePoint 2013

PerformancePoint is one of the most used BI components within SharePoint 2013 and it primarily used to create and share centrally managed dashboards. In many cases PerformancePoint reports are design in a manner to be updated at any time and modified to be viewed in a variety of manners such as reports or even KPIs and scorecards.

The following image shows a PerformancePoint report as well as the web part \ features available to filter the data to provide a specific report or to drill-down to find specific and more granular data and underlying metrics:

PerformancePoint 2013 and the related site and capabilities comes with Dashboard Designer natively within the site’s ribbon. This integration has been a welcomed new feature to many users I have spoken with as they feel its more user friendly and easier to user. The following is a screen shot of a SharePoint 2013 site with the Dashboard Designer shown in the ribbon:

Visio Services & SharePoint 2013

Visio Services in 2013 comes with a list of new capabilities in this latest release and is a SharePoint service application that enables the loading and browser-based rendering of Visio diagrams. The following are the major list of features and capabilities of Visio Services in SharePoint 2013:

• Designed to be a scalable, robust, enterprise-class service that provides feature and rendering fidelity with Visio
• Delivers rich browsing and navigation capabilities that are supported in a zero-footprint Web client
• Live connections to data sources are queried to deliver up-to-date data
• Renders diagrams in the browser
• High quality PNG(s) for full fidelity
• No dependence on Silverlight
• Native support for Visio file format
• Diagram consumers do not need a Visio client
• Available across devices using desktop browsers and mobile browsers
• Users interact with content
• Pan, Zoom, Hyperlinks, View Shape Data
• Accessible only via SharePoint
• On-premises & in cloud using Office 365

The following graphic was created by Visio Services to be stored within a SharePoint page that users can view and is updated on a weekly bases:

Self-Service Business Intelligence (BI) Architectural Overview

Microsoft has introduce a huge push toward “self-service” BI (SSBI) and providing the user with tools that allow them to run reports and perform BI actions as needed at any time without the need for IT’s intervention.

The goal of SSBI is to empower analysts so that they can design, customize and maintain their own BI solutions but it is important to note that SSBI is not meant to be a complete solution or a replacement for corporate BI initiatives and standardized reports offered by the organization.

The following is a diagram of the entire “technology stack” around SSBI: