BRK3128

Power BI for the Enterprise

Adam Wilson
Dimah Zaidalkilani
Agenda
• Connecting to enterprise data
• Building and managing content
• Security and compliance
Connecting to Enterprise Data
 Data refresh in Power BI

Where is your How do you How do you

data? connect? refresh?
• Cloud • Import data • Personal Gateway
• On-premises • Direct Query • On-premises data
gateway
 Data refresh in Power BI

Where is your How do you How do you

data? connect? refresh?
• Cloud Import data • Personal Gateway
• On-premises Direct Query • On-premises data
gateway
 Data in the cloud
• Access/refresh the data through
• Direct Query (Azure SQL, DW and Spark HDInsight)
• Import data - scheduled refresh
• Rest APIs to stream data

• Supported cloud sources

• SaaS sources
• Azure – SQL, DW, Blob, Table, HDInsight, Marketplace
• SharePoint, web sources, OData
• OneDrive
Data on-premises

Live Power BI reports &

dashboards

Live query Direct query Scheduled refresh

Cloud

On-premises

SQL Server Data Data

Analysis Services Source Source
 Supported on-prem data sources
• SQL Server, Teradata, Oracle, DB2, MySQL, PostgreSQL, Sybase, SAP
HANA, Access, Custom SQL, Custom ODBC Drivers
• SQL Server Analysis Services (tabular and multi-dimensional)
• Files/folder, SharePoint on-premises
• ODBC driver based connections
Data refresh in Power BI

Where is your How do you How do you

data? connect? refresh?
• Cloud Import data • Personal Gateway
• On-premises Direct Query • On-premises data
gateway
 How do you want to refresh data?
Import (cached mode) Direct query or Live connection

Refresh frequency Scheduled - hourly or daily Real-time

Performance No noticeable delay since data is already Depends on how fast the data source is as
cached queries are executed in real-time
Data storage in Since it is cached mode, data is stored in No data is stored in Power BI. Data is always on-
Power BI the cloud premises*
Data size Current limit of 1 GB (compressed) per The on-premises database is the limit; no Power
model BI limitation
Security Can create row-level security on the PBI Re-use on-prem row level security for Analysis
dataset (import only) Services

*Some data is cached for optimizing first-time load performance

 Data refresh in Power BI

Where is your How do you How do you

data? connect? refresh?
• Cloud • Import data • Personal Gateway
• On-premises • Direct Query • On-premises data
gateway
On-premises data gateway

Cloud services
Power BI

Read access, scheduled refresh, live connection

Gateway Cloud Service

Data source connection credentials are encrypted

Azure Service Bus

Application Gateway
Data source connection credentials can only be
decrypted by the gateway

SQL Server Other

On-premises data SQL Server Files, SharePoint
Analysis Services data sources
sources MICROSOFT CONFIDENTIAL – INTERNAL ONLY
On-premises data gateway
One gateway for multiple cloud services and experiences
Cloud services Preview Preview
Power BI PowerApps Microsoft Flow Azure Logic Apps
Live connection, CRUD support (create, read, update and delete)
Read access, scheduled refresh, live connection

Gateway Cloud Service

Data source connection credentials are encrypted

Azure Service Bus

Application Gateway
Data source connection credentials can only be
decrypted by the gateway

SQL Server Other

On-premises data SQL Server Files, SharePoint
Analysis Services data sources
sources MICROSOFT CONFIDENTIAL – INTERNAL ONLY
 Personal v/s On-premises data gateway
Personal Gateway On-premises data gateway (Enterprise
gateway)

Target Business analyst sets up and uses the • BI Admins set up the gateway for their
Persona gateway for her data sources department/ company
• Multiple users use the gateway setup by
the admins

Usage Directly by analysts BI Admin

Features Import with scheduled refresh Direct query and scheduled refresh
Data source connections managed per Central data source mgmt. and access control
user
No central monitoring/control Central monitoring and control

Services Power BI Power BI, PowerApps, Microsoft Flow, and

supported Azure Logic Apps
Demo:
On-premises data gateway
Architecture: Refresh with Gateways
Power BI 2 cred
1.Gateway is installed & configured. During configuration, a
corresponding service bus instance is also configured. Scheduler 3 Data Movement
service Service
2.Credentials entered for the data source in Power BI are encrypted
then stored in the cloud. Only the gateway can decrypt the
credentials. Personal Gateway windows credentials is stored in the 4
Gateway only.
3.Power BI kicks off a refresh Cloud
On-premises
Service bus
4. Data Movement Service analyzes the query and pushes to
appropriate service bus instance 7 5

5.Gateway polls bus for pending requests. It takes the pending request 1 Gateway
cred 2
6.Gateway gets the query, decrypts the credentials, sends query to the
data source for execution 6

7.After execution, gateway securely pushes the data to Power BI

DB
Security in Gateway
• Encryption key based on recovery key never leaves On-Prem
infrastructure
• symmetric key is what encrypts all creds and never leaves the gateway
• Power BI service never knows the on-prem credential values
encrypted / cannot intercept credentials (web client encrypts the
credential with a public key associated with the specific gateway it is
communicating with)
Troubleshooting tips and tools
- Cannot create a data source on the gateway
- Try connecting to the data source from a different client
• Sometimes the data source is really unreachable
• Take a look at the gateway service logs

- Data stopped refreshing

• Take a look at the refresh history
• Ensure Data source is still accessible
• Look at the gateway service logs and configuration logs
• Open fiddler and ensure the right request is being sent to the gateway

- Source: https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-tshoot/
Disaster recovery and gateway restore
• Gateway admin can use the recovery key to restore a gateway to a
different machine
• Once a gateway is restored, all data sources and credentials will
continue to work through the new gateway
• Restored gateway will have the same name, so no need for re-publish
Disaster recovery and gateway restore
 “Where to install the gateway?”

- It always depends on the usage for the gateway

• Machine specs needed for a heavily used Direct Query report is different
than a dataset that is set to refresh once a day with small amounts of data
- Recommendation:
• Start with an 8 core machine
• Keep an eye on your performance counters
• Depending on your usage, you can decide to scale up or down
 Tips and Best practices
• Monthly updates: Always update to the latest version
• Keep it always on and credentials up-to-date
• Outbound ports to be opened on the gateway computer (only if needed)
• 443, 9350-9353
• Can be installed on Windows Server or client OS
• Cannot be installed on the same machine with a local domain controller
• Installing multiple gateways on the same computer
• Enterprise Gateway can be installed alongside Personal Gateway
• Visit the FAQ and troubleshooting section in our documentation, leave
comments/questions
Building and Managing
Content
Building and Managing Content
• The content distribution lifecycle
• Collaboration tips and tricks
Content Distribution Lifecycle

Create Collaborate Distribute

Content Distribution Lifecycle

Create Collaborate Distribute

Creating Content
• Reports in Power BI Desktop
• Dashboards in the Power BI service
• Choose based on capabilities you need
Dashboards
Direct or live
connection

Report Tile Cloud Model

Tile Types
Report tiles
SSRS
Excel tiles
Widgets Excel Charts and
visuals
Navigational apps Excel Tiles
Tiles
Excel ranges
Tile Actions Q&A and Insights
Entry into Focus mode Text
Navigate to source asset or
custom URL Videos & Images
Export tile data to .csv Widgets
Insights Web
Alerting
Real-time
streaming
Reports

Interactivity
Cross-filtering
Slicers
Detailed control of
layout and styling
Content Distribution Lifecycle

Create Collaborate Distribute

Collaborating with Power BI
Group
• Manage artifacts within a My
Workspace
Workspace
workspace or group s
• Set up data refresh per Dashboards
Co-owned
workspace or group Dashboards

• Co-create artifacts in Co-owned

Reports
Group workspaces Reports

• User acceptance testing

Co-owned
Datasets
Datasets

Co-owned
Content
Content
packs
packs
Collaboration Best Practices
• When in doubt, start with a group workspace
• OneDrive for Business for versioning PBIX
• Great recap of the process:
https://aka.ms/usingpbiworkspaces
• Complete governance/deployment whitepaper:
https://aka.ms/pbideploywhitepaper
Content Distribution Lifecycle

Create Collaborate Distribute

Distribution Options
• Sharing
• Broad distribution with content packs
• Embedding and linking in portals
• Publish to Web
• Static consumption
• Mobile
• Cortana
Sharing
• For limited, ad-hoc
distribution
• Sharing one dashboard
at a time
• Changes visible
immediately
• Can share outside
organization
Content Packs
• Many reports and
dashboards
• Control who can access
• Stage changes and only
republish when ready
Embedding and linking
• Embed report or
dashboard tile into
portal or app
• Still uses same user
security and
authorization as in the
Power BI service
Publish to Web
• True public embedding
(think blog or public
company web site)
Static
• Print
• Alerts
• Export to PowerPoint (coming in October)
• Email subscriptions (coming this year)
Mobile
• View reports and dashboards
• Mobile-optimized dashboards available
Cortana
• She can return answers and report sheets
from Power BI
• Enabling Cortana in Windows 10
• Enable dataset for Cortana in Power BI
• Share a dashboard or republish a content pack to users who you
want to have access to Cortana
• Add your work or school account in Windows 10 (if you’re not
running the Anniversary Update, also add a Microsoft Account)
• https://aka.ms/pbicortanasetup

• Optionally create Cortana-optimized

report sheets in Power BI Desktop
Demo:
Collaboration
Security and Compliance
Data Security

How you
How Power BI
configure and
secures your data
restrict access
• User authentication • Data authorization
• Transport encryption • Row-level security
• Encryption of data at rest • Policy controls

https://aka.ms/pbisecuritywhitepaper
User Authentication
Power BI uses Azure Active Directory (AAD)
• Supports managed and unmanaged directories
• AAD features and policy apply to Power BI
• Authentication type (AAD-managed passwords vs. federated)
• Password and self-service password reset policies
• Conditional access policies
• Same tenant infrastructure across all services that use AAD
• Sign in once, signed in everywhere
• Same security groups can be leveraged across services
Data Authorization
1 Reporting authorization
• Users have access to dashboards, reports via sharing or
organizational content packs
• Scoped to user accounts, AAD security groups, or O365
Power BI
1 modern groups (content packs only)
2 Data source authorization
3 • (a) Calls to data sources are made using service-level
credentials in the case of cached and Direct Query sources.
Authorization in the data source is done using the single
service credentials.
• (b) for Analysis Services Live Connect, the user’s credentials
2b 2a are used and user authorization is performed in Analysis
Services (and RLS can be applied).

3 Row-level security (RLS) in Power BI

SQL Server data sources
• Row filters can be applied in the Power BI service for cached
Analysis Services data.
Policy Controls
Scenario How to achieve with Power BI Who can do it
Prevent certain users from accessing Assign licenses in Office 365 portal only to Global Administrator or
Power BI authorized users and disable automatic license User Administrator
assignment.
https://aka.ms/m2g7wu
Prevent access off corporate networks Configure Azure Active Directory Conditional Global Administrator or
Access User Administrator
https://aka.ms/nw75r3
Control use of features like data Set enterprise controls in the Power BI Admin Global Administrator or
export, anonymous access, external Portal Power BI Administrator*
sharing
Control use of mobile features (PIN Set up Intune Mobile Application Management Global Administrator
code, share sheet restrictions) https://aka.ms/wenojj
Audit Power BI activity Enable Power BI Auditing in both the Power BI Global Administrator or
Admin Portal and the Office 365 Security and user that is both a Office
Compliance Portal 365 Compliance
https://aka.ms/nw75r3 Administrator and Power
BI Administrator *
* Power BI Administrator role is available mid-October via PowerShell and soon after in the Office 365 Admin Center
Data Classification
• Tags defined by
admin and set by
each dashboard
owner
• Customizable links
• Not just for
compliance!
Demo:
Security and Controls
Row-level Security (RLS)
Azure Active Directory Conditional Access
Power BI Auditing
Learn more
Attend the following BI sessions

Boost your business Modern enterprise See what’s new in SQL Probe Microsoft Power BI
insights by using Excel reporting and mobile BI Server Analysis Services for Enterprise
with Power BI with SQL Server 2016 2016 Tabular Models
Session: BRK3134 Session: BRK3132 Session: BRK3289 Session: BRK3128
Tuesday, Sept. 27 – 10:45 am Tuesday, Sept. 27 – 12:30 pm Tuesday, Sept. 27 – 4:00 pm Wednesday, Sept. 28 – 10:45 am
Room A313 – A314 Room B207 – B208 Room A313 – A314 Room A313 – A314

Dive into effective report Model complex data easily Dive into Power BI Get your LOB application
authoring using Power BI with SQL Server 2016 Industry solutions with data into Microsoft
Desktop Analysis Services customer scenarios Power BI
Session: BRK3251 Session: BRK3133 Session: BRK3135 Session: BRK3131
Wednesday, Sept. 28 – 12:30 pm Wednesday, Sept. 28 – 4:00 pm Thursday, Sept. 29 – 10:45 am Friday, Sept. 31 – 9:00 am
Room A302 Room B304 - 305 Thomas Murphy Ballroom 4 Room A311 – A312

Visit our booths!

MS 46 and MS 47
Free IT Pro resources
To advance your career in cloud technology

Plan your Cloud role mapping

Microsoft IT Pro Career Center
career path www.microsoft.com/itprocareercenter
Expert advice on skills needed

Self-paced
Microsoft curriculum by cloud role
Get started IT Pro Cloud Essentials
with Azure www.microsoft.com/itprocloudessentials
$300 Azure credits and extended trials

Pluralsight 3 month subscription (10 courses)

Demos and Microsoft Mechanics
how-to videos www.microsoft.com/mechanics
Phone support incident

Weekly short videos and insights from Microsoft’s leaders and engineers
Connect with peers Microsoft Tech Community
and experts Connect with community of peers and Microsoft experts
https://techcommunity.microsoft.com
Please evaluate this session
Your feedback is important to us!

From your PC or Tablet visit MyIgnite at

http://myignite.microsoft.com

From your phone download and use the Ignite

Mobile App by scanning the QR code above or
visiting https://aka.ms/ignite.mobileapp
© 2016 Microsoft Corporation. All rights reserved.