leveraging bi in sharepoint with powerpivot and power view christina wheeler

Leveraging BI in SharePoint with PowerPivot and Power View

Christina Wheeler

About MeChristina Wheeler, MCTSSharePoint Trainer & Consultant

Critical Path Training

http://www.criticalpathtraining.com

– Blog: http://www.cwheeler76.com– Email: cwheeler76@outlook.com – Twitter: @cwheeler76– Published Books

• SharePoint 2010 Field Guide• SharePoint 2013 Inside Out

PowerPivot in Excel• Import millions of rows from multiple data sources• Easy fast calculations and analysis• Virtually Unlimited Support of Data Sources

– Import/combine source data• Relational databases, multidimensional & tabular sources, cloud

services, data feeds, Excel files, text files, Web data

• Security and Management• Data Analysis Expressions (DAX)

Power View• What is Power View?

– Interactive tool for data exploration, visualization, and presentation– Provides intuitive ad-hoc reporting to create dynamic dashboards– WYSIWYG visual design

• Web-based version of Power View introduced first – Supported with SharePoint 2010 and SharePoint 2013– Requires SSRS 2012 in SharePoint mode

• Power View Add-in for Excel introduced with Excel 2013– Sheets can be added to Excel 2013 workbook– Sheets based on internal or external Data Model

Charts and Other Visualization Types• Power View offers a variety of

visualization types• Table

– Table, Matrix, Card

• Chart– Pie, Column, Bar, Line,

Scatter, Bubble– Can have multiple numeric

fields and multiple series

Excel Services• Service Application

– Enables Excel workbooks to load, calculate, and display in SharePoint through the browser

• Excel Services Components– Excel Calculation Services– Excel Web Access– Excel Web Services

Excel Services Workbook Sample

Excel Services Benefits• Secure sharing of workbooks

– Share workbooks w/o exposing proprietary business logic– Prevent users from editing workbooks when required

• Business Intelligence– Connect to enterprise data sources– Create Dashboards and scorecards– Publish Workbooks to SharePoint

• Extensibility– Create reusable logic that is contained inside workbooks

• Reliability and availability– Calculations performed on server instead of client machines

Excel Services REST API• REST API introduced in SharePoint 2010

– Allows access of workbook parts or elements directly through URL

• REST Services based on two requirements:– Addressing scheme used to locate networked resources– Methodology for returning representations of resources

• REST Examplehttp://intranet.wingtip.com/_vti_bin/ExcelRest.aspx/Documents/MyWorkbook.xlsx/Model/Charts('Chart1')

Excel Services REST API

Excel Services REST API

DEMO

Questions?Blog: http://www.cwheeler76.com

Email: cwheeler76@outlook.com

Twitter: @cwheeler76

SharePoint 2013 Inside Outhttp://tinyurl.com/sp2010fg