microsoft bi performancepoint services for
TRANSCRIPT
Working with the Microsoft Business Intelligence EcosystemJohn P [email protected]@diverdown1964http://whitepages.unlimitedviz.com
John WhiteCTO/Co-Founder of UnlimitedVizSharePoint Server MVP, SQL Server v-TS
[email protected]://whitepages.unlimitedviz.com@diverdown1964
Agenda
The Microsoft BI Stack
Business Intelligence 101
ToolingArchitecture Tips and tricks
BI in action
The state of the world
The Microsoft BI Stack
Microsoft BI
PerformancePoint Services
for
Office 365
Business Intelligence containers
Office
SQL Server
SharePoint
On Premises Cloud
Azure IAAS
Excel Services
Power BI
Excel
Power Pivot
Power View
Power Query
Power Map
DB Engine
SSAS
SSRS
SSIS
Data Tools
SSRS SP Mode
PerformancePoint
PowerPivot for SharePoint
Filters
Report Builder
Business Intelligence 101
“One does not simply walk into Mordor report on live data”
- Boromir
BI Architecture 101
Data Marts
Extract, Transform, and Load (ETL)
Middleware Server(s)
DataWarehouse
Storage Design and Visualization
Data Cubes and Tabular
Models
E
T
L
Reporting Server(s)
BI and Designer Clients
Source data
Microsoft enterprise (classic) BI
SQL Server DBSQL Server Integration Services (SSIS)
SharePoint (with)• Excel Services• PowerPivot for
SharePoint• SSRS SharePoint Mode• PerformancePoint
SQL Server DB
Storage Design and Visualization
SQL Server Analysis Services
Multidimensional and Tabular modes
L
SQL Server Reporting Services (SSRS)
ExcelSQL Data ToolsReport Builder3rd party tools
ETL
E
T
Source data
Microsoft personal BI (All in Excel)
Worksheets
Tabular Data Model (xVelocity)
Pivot Charts and Tables
Power View (Analytic reports)
Power Map (Geospatial and time series data)
Power Pivot (Model design)
Power Query (ETL)
Power Pivot Import (EL)
SharePoint IS the deployment platform
Power Pivot Worksheets• Pivot Tables and
Charts• Power View
Data Marts and other
Data Cubes and Tabular
Models
Standard Worksheets• Pivot Tables and
Charts
PerformancePoint Reports• Analytic Charts and Grids• Decomposition trees
SQL Server Reporting Services Reports• Standard• Power View
PerformancePoint Scorecards and KPIs
Architecture
Standard 2 server SharePoint farm
Server OS
SharePoint Foundation
FE ProfileExcel Servic
es
Search …….
Server OS
SQL Server
DB SSAS
SSIS
BI integration – classic mistake
Server OS
SharePoint Foundation
FE ProfileExcel Servic
es
Search …….
Server OS
SQL Server
SharePoint Foundation
DB SSAS
SSIS
SSRS PP
BI integration – 2 server farm
Server OS
SharePoint Foundation
WFE ProfileExcel Servic
es
Search ……. SSRS PP
Server OS
SQL Server
DB SSAS
SSIS
Large farm deployment
Server OS
SharePoint Foundation
Profile Indexer
Search ……. SSRS
Server OS
SQL Server
DB SSAS SSIS
Server OS
SharePoint Foundation
FE
Server OS
SQL Server
Analysis Services – SharePoint Mode
Server OS
SharePoint Foundation
Profile Indexer
Search ……. SSRS
Server OS
SharePoint Foundation
Profile Indexer
Search ……. SSRS
Server OS
SharePoint Foundation
ProfileExcel
Services
Search ……. SSRS
Server OS
SharePoint Foundation
FE
Server OS
SharePoint Foundation
FE
Server OS
SharePoint Foundation
FE
Server OS
SQL Server
Analysis Services – SharePoint Mode
Server OS
SQL Server
Analysis Services – SharePoint Mode
Server OS
SQL Server
Analysis Services – SharePoint ModePP
Power BI – Data Management Gateway
Browser
Data Management Gateway
Internal data source
PowerPivot for SharePoint vs Power BI
Feature PowerPivot for SharePoint
Power BI
Interaction with Embedded Models
Yes Yes
Power View rendering Yes (with export) Yes
Use embedded model as data source
Yes No
Auto refresh Yes Yes (limited)
Model size limit 2Gb 250 Mb
Thumbnail gallery Yes Yes
Pre-optimize workbooks No Yes
Publish OData feeds No Yes
Natural language query No Yes
Mobile client No Yeshttp://whitepages.unlimitedviz.com/2013/12/whither-power-pivot-for-sharepoint-a-comparison-with-power-bi/
Tooling
Data Acquisition (ETL)
Power Pivot Import
SQL Server Integration Services
Power Query
Visualizations (on prem)
ExcelSQL Server
SharePoint
Pivot Charts/TablesPower ViewPower Map
SSRS Native ModePerformancePoint
Scorecards
Analytic Charts/Grids
Dashboard Designer
Excel Services Web Part
SSRS Web Part
Power View (SSRS)
Visualizations (Office 365)
ExcelOffice 365
Pivot Charts/TablesPower ViewPower Map
Excel Services Web Part
Filters
Power Q&A
Power BI for Windows 8.1
Dashboarding
Excel OnlyPerformancePoint*SSRS Only*
SharePoint Mashup
* On-prem only
Demo – the tools
Acquisition and visualization
Tips and tricks
It’s a little known fact
Not everything that counts can be counted, and not everything that can be counted counts.Albert Einstein
Requirements gathering
Minimum Viable Product
MVP
Define target
audience groups
Exclude less important
groups
Define use cases
Prioritize Use Cases
Translate into
requirements
Test with target
audience
Translate requirements
into functionality
Draw wire frames
Test with target
audience
MVP definition
Funding
Technology
Source: JumpStartCTO - http://jumpstartcto.com/how-do-you-gather-and-prioritize-the-requirements-and-functionality-for-a-minimal-viable-product/
Authorization
KerberosSSRSExcel Direct ConnectPerformancePoint
BISMPowerView in SSRS
SetUser()SSRS DB Reports
EffectiveUserName()Excel ServicesPerformancePoint
Per User
Excel Services
SSRS SharePoint Mode
Document Level
Impersonation in SSRS
Impersonation Options
SQL Server Analysis ServicesAnalysis Services SharePoint Mode
Excel Services No Yes No
Reporting Services Yes Yes No
Power View N/A Yes No
PerformancePoint No Yes No
Both SETUSER() and EFFECTIVEUSERNAME require elevated permissions (Administrator for AS, dbowner or sysadmin for SQL)
Data freshness
Cos
t
Data Latency
Data freshness
StreamInsightDirectQuery
PowerPivot for SharePoint*Power BI
Real time Periodic Daily
SQL Server Integration Services
* Hackable – For more granularity, see Ian Smith’s blog:
http://smithicus.wordpress.com/2011/08/09/using-a-custom-data-refresh-schedule-in-powerpivot-for-sharepoint/
Power BI
Licensing
Excel OnlyPerformancePoint
SSRS Only SharePoint
On Premises Office 365
PerformancePoint
SharePoint Enterprise
Excel Only
SharePoint EnterpriseSQL Server Standard (basic)SQL Server BI (tabular or PowerPivot)
SSRS Only
SharePoint FoundationSQL Server Express or above Power BI
Licensing
Excel OnlyPerformancePoint
SSRS Only SharePoint
On Premises Office 365
Turn OFF Rendering for xlsx files
Office Web Apps
New-SPWOPISuppression –extension xlsx –action view
http://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/archive/2013/01/31/powerpivot-for-sharepoint-browser-refresh-fails-data-refresh-not-supported-in-office-web-apps.aspx
Power Query data load options
Default is this Should be this
Power BI Boundaries
SharePoint storage
Power BI ModelMaximum workbook size
Excel Services rendering
2 Gb
10 Mb 250 Mb
260 Mb
Browser editing
30 Mb
http://whitepages.unlimitedviz.com/2013/08/power-bi-office-365-and-file-size-limits-be-careful/
Reporting Services does it nativelySQL Server Integration Services
WarehousingOperational efficiency
SharePoint List Source and Destination (Codeplex Project)
http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652 (Download)http://dataqueen.unlimitedviz.com/2011/06/how-to-use-a-sharepoint-list-as-a-data-source-in-your-ssis-package/ (How to)
OData Source for Microsoft SQL Server 2012 (Official)http://www.microsoft.com/en-us/download/details.aspx?id=42280 (Download)http://whitepages.unlimitedviz.com/2014/03/using-the-odata-source-connector-with-sharepoint-online-authentication/ (How to)
SharePoint as a data source
Good Tool, but too many false positivesPowerPivot has special rulesCorrect the real problems, disable the othersHow to
http://whitepages.unlimitedviz.com/2012/06/the-health-analyzer-and-powerpivot-for-sharepoint/
Health Analyzer
DBI-B323 Power Query in Modern Corporate BI – today @ 3:15
Related content
DBI-B322 Improving Power Pivot Data Models for Microsoft Power BI – Thurs @ 8:30
DBI-B210 Building the Modern Analytics Architecture: HDInsight and Power BI – Thu @ 1:00
Find me at MSE today at
Q&A
@diverdown1964whitepages.unlimitedviz.com
Resources
Learning
Microsoft Certification & Training Resources
www.microsoft.com/learning
msdn
Resources for Developers
http://microsoft.com/msdn
TechNet
Resources for IT Professionals
http://microsoft.com/technet
Sessions on Demand
http://channel9.msdn.com/Events/TechEd
Complete an evaluation and enter to win!
Evaluate this sessionScan this QR code to evaluate this session.
Twitter: @diverdown1964 blog: whitepages.unlimitedviz.com email: [email protected]
© 2014 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.