Practical BI
Solutions
with
SharePoint 2013
Dave McMahon
Who I Am
Software Systems Architect
Ridgian
SharePoint 2007
Excel Services
Business Data Connections
SQL 2008
SQL 2008 R2
Analysis Services
Reporting Services
Integration Services
SharePoint Portal Server 2003
SQL Server 2005
SharePoint 2010
Office Web Apps
PerformancePoint Server
PerformancePoint Services
Integrated Reporting Services
PowerPivot
SharePoint 2013
SQL Azure
Silverlight
HTML 5
CSS 3.0
ADFS
OAuth
Claims Based Auth
PowerView
SharePoint 2013
Office 2007
Office 2010
Office 2013
Office 365
BPOS
Office Online
Agenda
Overview of Microsoft
Business Intelligence
Suite
SharePoint 2013
Business Intelligence
Components
Pathway to Business
Intelligence
Overview
Overview of Microsoft
Business Intelligence
Suite
SharePoint 2013
Business Intelligence
Components
Pathway to Business
Intelligence
Microsoft Business Intelligence Suite
SQL Server
Analysis Services Reporting Services Database Services Integration Services SharePoint Excel Services and PowerPivot Reporting Services and PowerView Performance Point Services Visio Services ExcelMicrosoft Business Intelligence Suite
Analysis Services Reporting Services Database Services Integration ServicesAnalysis Services Reporting Services Database Services Integration Services
Date Time Event
17/10/2012 6:00 AM Wake 17/10/2012 7:00 AM Before Meal 17/10/2012 9:00 AM After Meal 17/10/2012 10:00 AM BP only 17/10/2012 12:00 PM Before Meal 17/10/2012 1:00 PM After Meal Averages TABULAR
SharePoint
Excel Services PowerPivot Reporting Services PowerView Perf Point Visio Services TABULARReporting Services
PerformancePoint Services
PowerPivot
Excel Services
PowerView
Agenda
Overview of Microsoft
Business Intelligence
Suite
SharePoint 2013
Business Intelligence
Components
Pathway to Business
Intelligence
Components
Overview of Microsoft
Business Intelligence
Suite
SharePoint 2013
Business Intelligence
Components
Pathway to Business
Intelligence
Private SharePoint 2010 Server
Fasthosts
Demonstration Platform
Private SQL 2008 R2 Server and Domain Controller
Reporting Services
Managed BI
Reporting Services
Responsibilities
Installed by
IT Services
Designed by
Business
Owners
Built by
Developers
Deployed by
IT Services
Reporting ServicesReporting Services 2012: Integrated Mode
SharePoint
Reporting Services Add-In
SSRS Service Application (SA)
SharePoint Content Databases
Reporting Services
Report Server
PerformancePoint Services
Data Exploration
PerformancePoint Services
Responsibilities
Installed by
IT Services
Designed by
Business
Owners
Built by
Developers
Deployed by
IT Services
PerformancePoint ServicesPerformancePoint Services
SharePoint
SharePoint Content Databases
Settings
PerformancePoint Services
PerformancePoint Service
Application
Dashboard Designer
PerformancePoint Content
REPORTING SERVICES AND
PERFORMANCEPOINT
Teradata
Warehouse Forecasts & Budgets SQL Server 2012
Data Mart
Private SharePoint 2013 Farm
SQL Server 2012 Analysis Service Tabular Data Model
On Premise Azure
Demonstration Platform 2
Azure Virtual Network MarketplaceExcel, PowerPivot and
Excel Services
Responsibilities
Installed by
IT Services
Designed by
Business Owners
Built by Business
Owners
Deployed by
Business Owners
Excel Services and PowerPivot
PowerPivot
Excel
POWERPIVOT
SharePoint Bulk Processing
SharePoint Front End
POWERPIVOT SA Proxy PowerPivot SA Excel SA Excel Services and PowerPivot
EXCEL SERVICES AND
POWERPIVOT
PowerView
Authoring and Presentation
Responsibilities
Installed by
IT Services
Designed by
Business Owners
Built by Business
Owners
Deployed by
Business Owners
PowerView
Excel
POWERVIEW
SharePoint Bulk Processing
SharePoint Front End
POWERPIVOT TABULAR MDOLAP PowerPivot SA PowerView SSRS Add-In POWERVIEW
POWERVIEW
Pathway
Steer your Organisation from Silos of Information to
Enterprise Business Intelligence
Agenda
Overview of Microsoft
Business Intelligence
Suite
SharePoint 2013
Business Intelligence
Components
Pathway to Business
Intelligence
Pathway
Overview of Microsoft
Business Intelligence
Suite
SharePoint 2013
Business Intelligence
Components
Pathway to Business
Intelligence
Excel
LOB Systems Files External Sources
Excel
Stage 1 – Tactical– No SQL Server
Stage 1
Stage 2
Stage 3
Stage 4
Stage 5
PowerPivot
Excel Excel PowerPivot
Excel used as data integration tool, as well as
for data modelling and reporting.
Users key data directly into Excel Worksheets or
Copy and Paste into PowerPivot Excel 2013 OR Excel 2010 with PowerPivot add-in IT Maintain Silos of Information
Excel
Data Warehouse
LOB Systems Files External Sources
Excel
Stage 2 – Personal – No SharePoint
PowerPivot
Excel Excel PowerPivot
Stage 1
Stage 2
Stage 3
Stage 4
Stage 5
Single repository for all reporting needs. Located on a server so accessible by multiple
users
Individual users build their own reporting models
Excel 2013 OR
Excel 2010 with PowerPivot add-in SQL 2012/ SQL 2008R2 Users can share models
via a shared drive or a copy of the model
Where possible data from external sources loaded
via an automated interface, such as web
service
IT Maintain Silos of Information and Data
Data Warehouse
LOB Systems Files External Sources
SharePoint 2013
Stage 3 – Team– Shared Content
PowerPivot Excel Services
Standard users are able to consume Excel
reports via browser
SharePoint Enterprise 2010 OR 2013
Power Users are able to create Excel and publish Excel Reports
Content is held on Intranet portal
Data is refreshed into PowerPivot on a
schedule or on demand
IT Maintain Silos of Information and Data Warehouse and SharePoint
Data Warehouse
LOB Systems Files External Sources
SharePoint 2013
Analysis Services TABULAR and MDOLAP
PowerPivot Excel Services
Stage 1
Stage 2
Stage 3
Stage 4
Stage 5
SQL 2012 BI Edition
PowerPivot data model is transferred into SSAS Tabular via Wizard and
maintained and enhanced by IT using SQL Server Data Tools (SSDT – Visual Studio) End users continue
to access the Tabular models via Excel
Services in SharePoint portal as
before
Data Warehouse
LOB Systems Files External Sources
SharePoint 2013 SSRS
Analysis Services TABULAR and MDOLAP
PowerPivot Excel Services IDV Mapping Necto Infograms
PPS Visio Services
Stage 5 – Enterprise – Expanded Toolset
Stage 1
Stage 2
Stage 3
Stage 4
Stage 5
SharePoint 2010/2013 Enterprise
plus integrated products
Developers create and publish BI content through Visual Studio
End users have single portal experience for all
BI content May also include entering/updating
data Access also available
Functionality
More Business Analyst Independence
Scalability
More Reliance on IT/BI Professionals
Governance and Security
Maturity Stages
BI Maturity
