configuring sharepoint server 2013 environment for business intelligence platform with sql server...
TRANSCRIPT
![Page 1: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/1.jpg)
Configuring a SharePoint Server 2013 environment for Business Intelligence with SQL Server 2014
Jose Redondo
Microsoft SQL Server MVP | CEO EntornoDB | DPA SolidQ | @redondoj
![Page 2: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/2.jpg)
Speaker
• Business Intelligence Architect - Consultant for +20 years
• Owner of EntornoDB C.A. in Caracas, Venezuela. Company specialized in the Analysis, Design &Development of Information Systems, BI Solutions & DWH using Microsoft Data Platform (MDP), SyBase,Oracle and IBM
• Worked as Desktop - Web - Database in .NET & Java;
DBA Microsoft Data Platform, IBM & Oracle; and BI & DWH (MDP)
Arquitect and Developer
• Speaker in technical events of Microsoft in Latam – U.S.A. &
PASS SQL Saturday in Latam - U.S.A.
• SQL Server, MCP - MSTS – MTA
• DPA for SolidQ
• Contributing Technical Reviewer for Packt Publishing
• Blog in Spanish "El Blog de Cheo Redondo"
• Microsoft SQL Server MVP
![Page 3: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/3.jpg)
Configuring a SharePoint Server 2013 environment for Business Intelligence with SQL Server 2014
![Page 4: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/4.jpg)
Agenda
• SharePoint BI – What is it?
• Root of all evil• Requirements
• Architecture
• Installation
• How to take BI to the next level?
• Deployment scenarios
![Page 5: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/5.jpg)
SharePoint BI for beginners…
![Page 6: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/6.jpg)
![Page 7: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/7.jpg)
BI Components and SharePoint Dashboard
Power Pivot Worksheets
• Pivot Tables and Charts
• Power View
Data Marts and others
Database Multidimensionaland Tabular Models
Standard Worksheets
• Pivot Tables and Charts
PerformancePoint Reports
• Analytic Charts and Grids
• Decomposition Tree
SQL Server Reporting Services Reports
• Standard
• Power View
PerformancePoint Scorecards and KPIs
![Page 8: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/8.jpg)
BI Components
Business Intelligence Center
2007 - Excel and Connections2010 - PerformancePoint2013 - PerformancePoint and
Power Pivot
Reports
Excel and ConnectionsWeb Part PagesSharePoint KPIs
![Page 9: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/9.jpg)
SharePoint BI
Power BIExcel, Power X, Mobile, Data Management Gateway
PerformancePoint(Dashboard Designer)
Only SSRS(Report Builder, Data Tools)
SharePoint(Pages and Filters)
On Premises Office 365
![Page 10: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/10.jpg)
Root of all evil
RequirementsArchitecture
Installation
![Page 11: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/11.jpg)
Excel Services Requirements
License Office Pro Plus
Model xVelocity enabled is required
PowerPivot add-in included, must be enabled
PowerView and-in included, is important to be enabled
SharePoint Enterprise CAL
Included SP media
Deployed as application services
Requires AS SP Mode for xVelocity and interaction with Power View
PowerPivot for SharePointRequired for AS SP Mode and Excel Services
SQL Media
Deployed as an application service SP
SQL Server BI or Enterprise
SQL Media
Deployed as SQL Application Service on separate servers
Load balancing to run Excel Services
![Page 12: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/12.jpg)
ArquitectureCompatibility Table “xVelocity”
PowerPivot for SharePoint
(SQL Server 2008R2)
PowerPivot for SharePoint
(SQL Server 2012)
Analysis Services SharePoint Mode
(SQL Server 2012 SP1)
PowerPivot for SharePoint
(SQL Server 2014)
Analysis Services SharePoint Mode(SQL Server 2014)
Excel 2010 with PowerPivot (2008 R2)
Yes Yes* Yes* Yes Yes
Excel 2010 with PowerPivot (2012) Yes Yes Yes*** Yes Yes
Excel 2010 with PowerPivot (2012 SP1) No Yes** Yes** Yes Yes
Excel 2013 with or without PowerPivot No No Yes Yes Yes
* Can be updated only from Slicers** You can not use the features of SP1 with Power View*** Limited exclusively for all PowerPivot functionality in SQL Server 2012 SP1
![Page 13: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/13.jpg)
ArquitectureWhat is know about “Power BI”
Features PowerPivot for SharePoint
Power BI
Embedded Interaction Models Yes Yes
Power View rendering Yes (Data Exporting) Yes
Using embedded in the model as data source Yes No
Auto refresh Yes Yes (Limited)
Limit for the size of the model 2Gb 250 Mb
Thumbnail Gallery Yes Yes
Pre-optimize workbooks (Excel) No Yes
OData Feeds publication No Yes
Natural Language Queries No Yes
Customers mobiles No Yes
![Page 14: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/14.jpg)
SQL Server Reporting Services 2014 Requirements
Deployed as an Application Services SP
Required for BISM/Power View
Alerting
Self service subscriptions
Power View
![Page 15: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/15.jpg)
PerformancePoint Services Requirements
SharePoint EnterpriseDeployed as an Application Service SP
For Analytic Charts/Grids
![Page 16: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/16.jpg)
Root of all evil
RequirementsArchitecture
Installation
![Page 17: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/17.jpg)
Installing SharePoint BI Components
Excel Services
PerformancePoint
Other Stuff
Reporting Services
PowerPivot
Analysis Services
![Page 18: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/18.jpg)
ArquitectureSharePoint Farms with 2 servers
Server OS
SharePoint Foundation
FE ProfileExcel
ServicesSearch …….
Server OS
SQL Server
DB SSAS SSIS
![Page 19: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/19.jpg)
ArquitectureSharePoint Farms with BI – The Bad Practices
Server OS
SharePoint Foundation
FE ProfileExcel
ServicesSearch …….
Server OS
SQL Server
SharePoint Foundation
DB SSAS SSIS SSRS PP
![Page 20: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/20.jpg)
ArquitectureSharePoint Farms with BI – The Best Environment
Server OS
SharePoint Foundation
WFE ProfileExcel
ServicesSearch ……. SSRS PP
Server OS
SQL Server
DB SSAS SSIS
![Page 21: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/21.jpg)
How to take BI to the next level?
![Page 22: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/22.jpg)
Configuration scenarios…
• SQL Server Analysis Services SharePoint Mode
• Excel Services
• SQL Server PowerPivot for SharePoint
• SQL Server Reporting Services SharePoint Mode
• PerformancePoint Services
![Page 23: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/23.jpg)
SQL Server Analysis Services SharePoint Mode
![Page 24: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/24.jpg)
![Page 25: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/25.jpg)
![Page 26: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/26.jpg)
![Page 27: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/27.jpg)
![Page 28: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/28.jpg)
![Page 29: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/29.jpg)
![Page 30: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/30.jpg)
![Page 31: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/31.jpg)
![Page 32: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/32.jpg)
Demo Video“Installing PowerPivot”
![Page 33: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/33.jpg)
![Page 34: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/34.jpg)
Excel Services Configuration
![Page 35: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/35.jpg)
![Page 36: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/36.jpg)
Excel Services Cache
![Page 37: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/37.jpg)
Excel Services Data Model
![Page 38: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/38.jpg)
Default ConfigurationWarning…
![Page 39: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/39.jpg)
PowerPivot for SharePoint
![Page 40: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/40.jpg)
![Page 41: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/41.jpg)
![Page 42: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/42.jpg)
![Page 43: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/43.jpg)
![Page 44: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/44.jpg)
![Page 45: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/45.jpg)
![Page 46: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/46.jpg)
![Page 47: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/47.jpg)
![Page 48: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/48.jpg)
![Page 49: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/49.jpg)
![Page 50: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/50.jpg)
![Page 51: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/51.jpg)
![Page 52: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/52.jpg)
![Page 53: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/53.jpg)
![Page 54: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/54.jpg)
![Page 55: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/55.jpg)
![Page 56: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/56.jpg)
Demo Video“PowerPivot Configuration”
![Page 57: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/57.jpg)
![Page 58: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/58.jpg)
SQL Server Reporting Services SharePoint Mode
![Page 59: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/59.jpg)
![Page 60: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/60.jpg)
![Page 61: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/61.jpg)
![Page 62: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/62.jpg)
![Page 63: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/63.jpg)
![Page 64: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/64.jpg)
![Page 65: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/65.jpg)
![Page 66: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/66.jpg)
![Page 67: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/67.jpg)
Configuring Reporting Services SharePoint Mode
67
![Page 68: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/68.jpg)
![Page 69: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/69.jpg)
![Page 70: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/70.jpg)
![Page 71: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/71.jpg)
![Page 72: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/72.jpg)
![Page 73: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/73.jpg)
![Page 74: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/74.jpg)
![Page 75: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/75.jpg)
![Page 76: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/76.jpg)
Demo Video“Installing and Configuring of SQL Server Reporting ServicesSharePoint Mode”
![Page 77: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/77.jpg)
![Page 78: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/78.jpg)
PerformancePoint Services
![Page 79: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/79.jpg)
![Page 80: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/80.jpg)
![Page 81: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/81.jpg)
![Page 82: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/82.jpg)
![Page 83: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/83.jpg)
![Page 84: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/84.jpg)
![Page 85: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/85.jpg)
![Page 86: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/86.jpg)
![Page 87: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/87.jpg)
Demo Video“Configuring PerformancePoint Services”
![Page 88: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/88.jpg)
![Page 89: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/89.jpg)
Deployment scenarios
![Page 90: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/90.jpg)
Everything in one box
Server OS
SQL Server
SharePoint Foundation
FE Profile Indexer Search MetadataExcel
Services…. SSRS PP
![Page 91: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/91.jpg)
Two-server BI farm
Server OS
SharePoint Foundation
WFE ProfileExcel
ServicesSearch ……. SSRS PP
Server OS
SQL Server
DB SSAS SSIS
![Page 92: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/92.jpg)
Servers separated by Roles
Server OS
SharePoint Foundation
ProfileExcel Services
Search ……. SSRS
Server OS
SQL Server
DB SSAS SSIS
Server OS
SharePoint Foundation
FE
Server OS
SQL Server
Analysis Services – SharePoint Mode
PP
![Page 93: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/93.jpg)
Integration with BI – Distributed roles
Server OS
SharePoint Foundation
Profile Crawl Metadata
Server OS
SQL Server
DB SSAS SSIS
Server OS
SQL Server
Analysis Services – SharePoint Mode
Server OS
SharePoint Foundation
FE Search Excel
ServicesSSRS PP
SSS
![Page 94: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/94.jpg)
Deploy large farms
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
ServicesSearch ……. 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
![Page 95: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/95.jpg)
Expanding BI Services
• Reporting Services• Adding Reporting Services - SharePoint for all server applications
• Adding Reporting Services Add-In on every server front (+)
• xVelocity/Analysis Services/PowerPivot• Installing standalone servers for Analysis Services SharePoint Mode
• Installing server applications for both services
• Availability of PowerPivot Add-in for separate download (SQL Feature Pack) - http://www.microsoft.com/en-us/download/details.aspx?id=34702
![Page 96: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/96.jpg)
Identity Credentials
• The big problem called "Double Hop"
• Kerberos Security - A double edged sword
• Claims - The solution to follow
• Per user identities without Kerberos• EFFECTIVEUSERNAME - (Analysis
Services, Excel Services)
• SetUser() - (SQL Server)
![Page 97: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/97.jpg)
ImpersonatingOptions for SQL Server Reporting Services
SQL Server Analysis ServicesAnalysis Services –SharePoint Mode
Excel Services No Si No
Reporting Services Si Si No
Power View N/A Si No
PerformancePoint No Si No
Both commands SETUSER() and EFFECTIVEUSERNAME requires elevated permissions (Administrator for AS, dbowner or sysadmin for SQL)
![Page 98: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/98.jpg)
Integration
• Whole > @SUM(Parts)
• Dashboarding
• Filtering SharePoint Web Parts
• Filtering Lists
• Filtering concurrent users
• Filtering Analysis Services
![Page 99: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/99.jpg)
Office Web App
• Turn OFF Rendering for xlsx files
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
![Page 100: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/100.jpg)
SharePoint as Data Source
• SQL Server Reporting Services is not native
• SQL Server Integration Services• Data Warehouse
• Efficient execution
• SharePoint List as origin and destination data• http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652 (Descarga)
• http://dataqueen.unlimitedviz.com/2011/06/how-to-use-a-sharepoint-list-as-a-data-source-in-your-ssis-package/ (Como es?)
• OData sources for Microsoft SQL Server 2014• http://www.microsoft.com/en-us/download/details.aspx?id=42280 (Descarga)
• http://whitepages.unlimitedviz.com/2014/03/using-the-odata-source-connector-con-sharepoint-online-authentication/ (Como es)
![Page 101: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/101.jpg)
SQL Server Integration ServicesList a warehouse Data Collection
• Health Analyzer
![Page 102: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/102.jpg)
Is this all true?Let’s GO…
![Page 103: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/103.jpg)
DEMO“SharePoint BI in action”
![Page 104: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/104.jpg)
![Page 105: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/105.jpg)
Contact…
• Social Network:• Facebook: Cheo Redondo
• Twitter: @redondoj
• LinkedIn: https://www.linkedin.com/in/redondoj
• Blog in Spanish: “El Blog de Cheo Redondo” - http://redondoj.wordpress.com
![Page 106: Configuring SharePoint Server 2013 environment for Business Intelligence Platform with SQL Server 2014](https://reader031.vdocuments.us/reader031/viewer/2022020123/55a525ae1a28abdf0e8b46a9/html5/thumbnails/106.jpg)
Thank you #SQLFamily