Transcript Power BI In SharePoint and Office 365

SharePoint Saturday PittsburghGreetings from Philly
Power BI in SharePoint and Office 365
Power BI In SharePoint and Office 365
Dan Hartshorn – [email protected]
Architect
Computer Enterprises, Inc.
Thanks to our Sponsors!
 Platinum:
 Gold:
 Silver:
 Raffle:
More
Fun Stuff
Raffle: Please join us in the Atrium at 5:15 PM for the raffle. We are
raffling some exciting prizes including Fitbits, HP tablets, and who
knows, maybe a Surface 3 (need to be present to win)!!!
SharePint will be held at Mad Mex (370 Atwood St, Pittsburgh, PA
15213). While it starts at 5:30 PM, there’s no end time!!!! 
Pittsburgh Area SharePoint User Group
Meets at the Microsoft office on the North Shore
More Info: https://www.linkedin.com/groups/Pittsburgh-Area-SharePointUser-Group-3769745/about
We do Request that…
You fill out the Session Evals. These will also be your Raffle tickets.
Print your name clearly if you intend to participate in the Raffle and
drop the forms at the registration desk after the last session.
You visit the sponsors. The event is possible due to their generous
support and we request that you visit them and inquire about their
products & services.
Cell phones be kept on silent as a courtesy to other attendees and
speakers
The world of data is changing
A powerful new way to work with data
Self-service business intelligence and analytics with Excel and the power of the cloud
Analyzing data with Excel
Discover
Analyze
Visualize
Explore
Analyzing data with Excel
Discover
Analyze
Visualize
Explore
Analyzing data with Excel
Discover
Analyze
Visualize
Explore
Analyzing data with Excel
Discover
Analyze
Visualize
Explore
Power Everywhere in Excel
• Power Pivot: For creating and customizing flexible
data models within Excel.
• Power View: For creating interactive charts, graphs
and other visual representations of data.
• Power Query: Enables customers to easily search
and access public data and their organization's data,
all within Excel (formerly known as "Data Explorer").
• Power Map: A 3D data visualization tool for
mapping, exploring and interacting with geographic
and temporal data (formerly known as product
codename "Geoflow").
Look at Excel & Excel Services
A powerful new way to work with data
Self-service business intelligence with familiar Excel and the power of the cloud
Immersive insights
Share
Report
Clean
Visualise
Mash-up
Explore
Share & collaborate with Power BI for Office 365
BI Sites
Shared Queries
Data Refresh
Data Search
Mobile Access
Natural Language
Questions
Share & collaborate with Power BI for Office 365
BI Sites
Shared Queries
Data Refresh
Data Search
Mobile Access
Natural Language
Questions
Share & collaborate with Power BI for Office 365
BI Sites
Shared Queries
Data Refresh
Data Search
Mobile Access
Natural Language
Questions
Power BI
for Office 365
Workbook
Cloud
On Premise
Data Management Gateway
Installed on-premises
Share & collaborate with Power BI for Office 365
BI Sites
Shared Queries
Data Refresh
Data Search
Mobile Access
Natural Language
Questions
Search for:
• Public Data
• Corporate Data
• Shared Queries
Share & collaborate with Power BI for Office 365
BI Sites
Shared Queries
Data Refresh
Data Search
Mobile Access
Natural Language
Questions
Share & collaborate with Power BI for Office 365
BI Sites
Shared Queries
Data Refresh
Data Search
Mobile Access
Natural Language
Questions
A powerful new way to work with data
Discover
Analyze
Visualize
Search, access, and transform
public and internal data sources
with Power Query
Easy data modeling and lightning
fast in-memory analytics with
Power Pivot
Bold new interactive data
visualizations with Power View
and Power Map
Share
Question
Q&A
Mobility
Share data views and workbooks
refreshable from on-premises
and cloud based data sources,
with Power BI Sites
Ask questions and get immediate
answers with natural language
query
Mobile access through HTML5
and touch optimized apps
Lots of data sources!
Web page
Excel or CSV file
XML file
Text file
Folder
SQL Server database
Windows Azure SQL
Database
• Access database
• Oracle database
•
•
•
•
•
•
•
IBM DB2 database
MySQL database
SharePoint List
OData feed
Hadoop Distributed File
System (HDFS
• Windows Azure
Marketplace
• Active Directory
• Facebook
•
•
•
•
•
Power BI – Conceptual Architecture
Index
Public Cloud
Data Sources
O365 Power BI
Public
Data Catalog
Data Refresh
Data Management
Power BI
Corporate
Data Catalog Admin Center Portal
SPO team sites,
BI Sites, Q&A
Cloud Data
Sources
Import Data
Import Data
Data Refresh
Import data,
Publish queries
Publish queries
Data Management
Gateway
Configure
Corporate
Data Sources
IT Admin
Import Data
Publish raw
data sources
or expose
them as
OData feeds
Manage Data
Data Steward
Publish reports &
create featured
questions, configure
scheduled refresh
IW
(Author)
Import Data
Consume interactive
reports, ask questions
Power Query
Power Pivot
Power View
Power Map
Excel,
Tablet/Mobile,
Browser –
IW
Silverlight,
(Consumer) HTML5
IT Infrastructure Services for Power BI
• Govern usage, secure access and grant authority
• Provides a single pane of glass to manage and monitor all services and
servers
• Enabling IT to expose and protect data sources and complex feeds
• Respond to incidents - service KPIs, SLA metrics, audit logs
Role of the IT Admin in Power BI
•
•
•
•
Enabler of Self Service BI
Varying levels of control across data sources, departments
Oversight and monitoring of cloud data access
Ability to make corporate data sources easier to discover, and easier to
access
Power BI Admin Center
•
•
•
•
•
Access via link in O365 admin portal
Configure Power BI roles
Install and monitor the Data Management Gateways for your organization
Configure access to cloud enabled data sources
Expose OData feeds to corporate data sources
Admin Portal
Demo
Where does this fit?
Personal BI
Team BI
Organization
BI
Where does this fit?
Scenario 1
Personal BI
Team BI
Organization
BI
• Data exploration tool
• Consumes data from your enterprise DW for
personal/team reporting requirements
• Combine internal data with external data sources
• Self-service ETL, data modelling and data visualisation
Where does this fit?
Scenario 2
Personal BI
Team BI
Organization
BI
• Share datasets with the team
• Share reports/files
• Add governance and control around your data/reports
Where does this fit?
Scenario 3
Personal BI
Team BI
Organization
BI
• Use powerful features with minimum end-user training
• Utilise Power BI site to share and manage datasets and
reports across the team/department or organisation.
• Data Management Gateway, Scheduled Refresh
Where does this fit?
Personal BI
Team BI
Organization
BI
• Give end-user a more powerful tool that requires
minimum training
• Add governance and control around your data/reports
• Seamless transition of BI applications from Personal BI to
Team BI to Organisational BI
Power BI Designer
Demo
Data Management Gateway
Data Management Gateway
Data Management Gateway
Power BI Admin Center
capabilities
Enabling Corporate
OData Feeds
Enabling Discovery
in Power Query
Enabling Excel Workbook Data
Refresh using
SharePoint Online
Data Management Gateway - Conceptual
Data Management Gateway
Connects to corporate data sources
and sends data to Microsoft cloud
services through a secure channel
(Service Bus).
Secure Credential Store
All credentials used by the
gateway are stored on-premises.
Encrypted backup of credentials
can also be stored in the cloud
to enable business continuity
scenarios.
Power BI Admin Center
Allows IT to configure, manage
and monitor access to corporate
data sources.
Corporate Data Sources
The Gateway can connect to
a variety of data sources.
Extend with Hybrid Cloud Solutions
Extend with Hybrid Cloud Solutions
Extend with Hybrid Cloud Solutions
Current Limitations
Cloud: Windows Azure SQL Database, SQL Server (in Azure VM), OData
On-Prem: SQL Server (2005+), Oracle (10g, 11g, and 11gR2)
New: Power Query support
Cloud: Embedded in workbook, or read from SharePoint Secure Store
On-Prem: Configure through the Power BI Admin Center
Data Management Gateway - OData
Requires a primary key or unique index
Automatically registered in the Data Catalogue
Currently only used for discovery and authentication
Actual OData results are served from connection to Gateway
Org ID authentication, and redirection from cloud to on-premises
Gateway Configuration Details
Requires outgoing ports (9350-9354)
Falls back to 443/80 if the other ports aren’t open
No incoming ports from the internet (messages received via Service Bus)
Requires incoming port
default: 8050 (and 8051 if HTTP is used)
Recommended to use HTTPS with signed certificate
Data Sources
Users and groups configured through the portal
Static credentials used for refresh
More data sources coming soon
Expose SSIS data flow as SQL view
Corporate OData Feeds
Power BI Cloud Services
Power Query
Data Management
Gateway
Data Refresh from Power BI Sites
Excel Workbook in
SharePoint Online
(2) Connects to Gateway
Cloud Service
(8) Returns data to Excel
Workbook
(3) Checks whether user
is authorized to perform
a refresh
(4) Sends command
(SQL statement,
connection string) to
on-premise Gateway
(5) Sends SQL to
SQL Server
Gateway
Cloud Service
(7) Compress,
chunk and return
the data
Data Management
Gateway
(6) Return Results
Power BI vs. Competitors
Power BI vs. the other
competitors
Feature
Power BI
Tableau
Qlikview
Data Visualisation
Power View is not as rich as
competitors in dashboards
and visual analytics. However,
competitors do not have an
equivalent tool to Power Map
Very strong visual analytics and
dashboards
Similar level to tableau but not as
easy to create dashboards but
are regarded as better quality
than tableau, may require
scripting
Data Modelling
Strong data modelling and
analytics/ easier to use than
competitors
Basic data modelling tools
Does have a data modelling tool
but may require an IT specialist
and is known for taking time to
perfect data model
Combining data
Ease of access to
public/corporate data sources
including tables from the web,
merge and append
Large list of data source support
and feeds
Ease of connection to proprietary
data sources may struggle with
others
ETL
Power Query is easy to use
powerful ETL tool, Q&A
unique to Power BI
Does not have an ETL tool, ETL
needs to be done in a database or
via Excel
Does have an ETL tool but may
not be included in standard
package
Power BI and Tableau: Best Friends Forever!
http://www.powerpivotpro.com/2014/11/power-bi-and-tableau-best-friends-forever/
On-Premises
 SharePoint still has dash boards - PerformancePoint
*(Virtually unchanged from 2010)
 Query Libraries
 Connection Libraries
 On the horizon Datazen
Datazen
Part of Microsoft for all of a month
Future roadmap is unclear
Runs with all devices (including mobile)
Provides a rich platform for
Dashboards and Visualizations
 Plays well with SQL Server and SSAS
 Provides for Drill Down and Through
 “Free” with SQL Server Enterprise
Edition and SA




Scenarios & Features by BI product
Excel in Office 365
ProPlus/E3
BI content creation
Power Query, Power Pivot,
Power View, & Power Map
SharePoint Online
P2
Power BI for Office 365

BI sharing & viewing
Viewing (HTML5): <10MB
Viewing (HTML5):
<250MB


BI Sites (organized report homepage)

Query Sharing & Enterprise Data Search

Data Management & Mobility
Data Stewardship & Scheduled Refresh

Q&A (Natural Language Query)

Power BI for Windows & iPad (Mobile BI)

Power BI in Office 365 vs. SharePoint
Feature
Power BI for Office 365
SharePoint On Premise (Power User)
SharePoint On Premise (IT Pro)
In memory
Yes
Yes
Yes
Data size
250MB
Configurable up to SharePoint limit (2gb)
Unlimited (or limited by RAM)
Data Refresh
Yes, using Gateway
Yes, Power Pivot Gallery
Yes
Direct access to on premise data
No
Yes
Yes
Refresh from Power Query
Yes
No
No
Support for Power View in PowerPoint No
Yes
Yes
Render Power Map in browser
No
No
No
Create Power View reports
No
Yes
Yes
Render Power View Sheets in browser Yes
Yes (SP 2013), No (SP 2010)
Yes (SP 2013), No (SP 2010)
HTML5 support / Mobile Access
Yes
No
No
Natural Language (Q&A) query
Yes
No
No
Synonyms in Power Pivot model
Yes
No
No
Windows Authentication (corporate
domain)
Yes (DirSync)
Yes
Yes
Power BI in Office 365 vs. SharePoint
Max Workbook Size
Power BI: the maximum workbook size currently is 250MB. Nearly all of the
data needs to be in the Power Pivot model because only 10MB is permitted
outside of the model.
SharePoint: the maximum workbook size is 2GB.
Evolving Product
• Example: Custom Map
Resources
Getting Stated with Power BI for Office 365 preview
http://office.microsoft.com/en-us/excel/power-bi-download-add-inFX104087144.aspx
Power BI blog http://blogs.msdn.com/b/powerbi/
Introduction to Power BI Admin Center
Power BI Admin Center Help
Restore a Data Management Gateway
Monitor System Health using the System Health OData Feed
Publish SSIS Packages as OData Feed Sources
Supported Data Sources and Data Types