Microsoft Excel Services, PowerPivot, and Power View Deep-dive

Excel has more capabilities and relevance in Business Intelligence within SharePoint and Office 365 than any other Microsoft Office program. The following section details some of the wide range of powerful capabilities for reports, score cards, and dashboards, as shown in the image below, which Excel and SharePoint 2013 can deliver to your organization.

Excel Services

Excel Services, among many other things, enables manual business processes such as the emailing or sharing of spreadsheets that cause manual errors to stop and a centralized version of the truth to be established.

The following is an overview of Excel Services 2013:

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

PowerPivot

PowerPivot is an add-in for Excel users that provides the capability to create powerful data models that can include calculated fields, reports, and score cards. PowerPivot sits among the three-dimensional business intelligence model that Microsoft’s BI stack offers, as shown in the image below.

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.

Excel Power View

Power View sheets can be added to a workbook to enable an interactive data exploration, visualization, and presentation experience, and they are also “presentation ready” at all times.

Power View reports can be based on an embedded PowerPivot data model or an external tabular data model that can be optimized to fully exploit the capabilities of the Power View experience.

Power View, in SharePoint Server 2013’s release, offers much deeper integration with Excel Services. Using the xVelocity in-memory analytics engine, it uses in-memory compression and data filtering to ensure that large data sets are processed quickly, and it enables users to create relationships between data from different sources. In this way, users can view complex data in a more refined and normalized form even if the data exists in multiple data sources.

This data modeling and visualization engine sits on top of the SQL Server Reporting Services add-in to SharePoint and provides an easy way to deliver interactive data modeling and visualization tools to your users.

EPC Group Tip

The xVelocity in-memory analytics engine is the next generation of the VertiPaq engine that was introduced in SQL Server 2008 R2, with PowerPivot for Excel 2010 and PowerPivot for SharePoint 2010. VertiPaq is an in-memory columnstore engine that achieves breakthrough performance for analytic queries by employing techniques such as columnar storage, state-of-the-art compression, in-memory caching, and highly parallel data scanning and aggregation algorithms. In SQL Server 2012, the xVelocity in-memory analytics engine has been enhanced to support both self-service BI (PowerPivot) and corporate BI (Analysis Services tabular mode) scenarios.

The xVelocity Engine

The xVelocity engine has two usage scenarios in the context of business intelligence:

• Information workers can use PowerPivot for Excel to integrate data from a number of sources, cleanse and model the data, enrich the data with business logic, analyze the data, and build reports and visualizations. Since PowerPivot uses the xVelocity engine under the covers, information workers are not limited by the restrictions of Excel. They can work with several million rows of data and still benefit from split second response times.
• BI developers and IT professionals can use SQL Server Data Tools to create an Analysis Services Tabular project and build a BI Semantic Model. The model can contain data from a number of sources, business logic expressed in the form of DAX calculations, role-based security, and large data volumes that can be managed using partitions in the xVelocity engine. When the model is deployed to an Analysis Services server, information workers can use tools like Excel and Power View to interact with the model and achieve split-second response times from the xVelocity engine.

EPC Group’s Nationally Recognized Practice Areas

EPC Group leading Custom Application Development, SharePoint, Office 365, Infrastructure Design and Business Intelligence Practice areas continue to lead the way in providing our clients with the most up-to-date and relevant information that is tailored to their individual business and functional needs.