Download - Bimodal BI
Kansas City
SQLSaturday
10/3/2015
Melissa CoatesSolution Architect, BlueGranite
blue-granite.com
Blog: sqlchick.com
Twitter: @sqlchick
Power BIArchitecture, Integration Points, Implementation Options
Agenda:
1. Brief Overview and Evolution of Power BI
2. Implementation Options & Architecture
3. Bimodal BI
4. Recap + QuestionsSlides are available at
sqlchick.com
under ‘Presentations and
Downloads’
Power BIArchitecture, Integration Points, Implementation Options
Overview
&
Evolution of Power BI
Set of tools targeted primarily at analysts & power users to facilitate
acquiring, cleansing, standardizing, modeling, presenting & sharing of data.
Components of Power BI
Power
QueryPower
Pivot
Power View,
Power Map,
Excel PivotTable,
Excel PivotChart
| ------------ Power BI Desktop ----------- |
| ------------------ Excel ------------------ |
Reports,
Dashboards,
Sharing,
Q&A
Power BI
Site
Mobile
App
Typical Users of Microsoft Reporting Tools
Reporting Services
Datazen
PerformancePoint
Visio Services
Power BI Desktop
Power Pivot
Power Query
Power Map
Report Builder
Power View
Excel
Corporate BI Self-Service BI
Evolution of Power BI
V1: Power BI for Office 365GA Release Feb 2014
Excel Add-Ins
V2: Power BI DashboardsGA Release July 2015
Excel Add-Ins or Power BI Desktop
Integrated with Office 365 +
SharePoint OnlineIndependent Software as a Service
Data must be replicated in Excel
Data Model (Power Pivot)
Support for Excel Data Model +
Additional Data Sources, SaaS
Connectors, and APIs
Will be retired; customers required
to migrate to new Power BI service
Scope: Self-Service BIScope: Self-Service BI +
elements of Corporate BI/Analytics
Fast release cycles
Implementation Options
&
Architecture
Power BI Implementation Options
Excel Add-Ins and/or
Power BI Desktop only
Power BI for Office 365 (V1)
Power Pivot for SharePoint
Power BI (V2)
1
4a
On-Premises Cloud Services
Hybrid Environment
Pyramid Analytics Server (part of V2)
3
2
Integrated with Custom Application (part of V2)
4b
4c
V1 Deprecated
Excel Add-Ins and/or Power BI Desktop Only No usage of a portal collaboration area
Power BI (V2) End-to-End
Authoring Environments
Power BI Desktop Excel Add-Ins Power BI Service
New tool of choice
for creating queries,
data model, and
reports
Monthly release cycle
for new features
PBIX format
Includes Power Query,
Power Pivot, and
Power View add-ins
Much slower release
cycle of new features
due to Excel
integration
XLSX format
The web portal
component for sharing
& collaboration
Dashboards and Q&A
must be created in the
web portal; reports can
optionally be edited in
the web
Caution! There’s no download ability (PBIX or XLSX) yet from the Power BI Service.
Since a report originally uploaded from PBI Desktop or Excel can be modified in the
web, need to be careful with where ‘original’ resides.
Deciding Which Tool to Use?
Power BI Desktop Excel Add-Ins
You run an older Excel version
You have 32-bit Excel
You want the newest features as
quickly as possible
You want to access SaaS
connectors & other new sources
Release cycle: new Power BI Desktop
msi installer 1x / month *
You already have an Excel 2013 click-
to-run version that supports the
Power add-ins running in 64-bit mode
Your analysts feel strongly about
using Excel vs another tool
You want to use pivot tables, pivot
charts, and/or cube functions
It’s likely you’ll upsize to SSAS Tabular
* Although Power BI Desktop updates monthly, the Power BI Service (online) updates more
frequently - you have no control over new features being introduced to the Service.
Authoring & Reuse of Power BI ObjectsA dashboard can
contain elements
from many reports.
A report can
reference data from
one dataset.
A single dataset can
be reused among
various reports.
Two Types of Datasets
Embedded Dataset
An in-memory columnar data structure.
Limited to 250MB compressed in size which
applies to storage limits.
In this situation, the data is *stored* in the
dataset and imported to the Power BI Service.
Requires refresh schedule to stay current.
Embedded datasets can be created various ways:
Pull
Power BI Desktop
Power Pivot Excel add-in
SaaS Connector (SaaS Content Pack)
Push
Streaming data (Azure Stream Analytics)
API library
Direct Connect Dataset
Queries are live back to original data source.
Useful for utilizing existing data investments,
larger datasets that cannot or should not be
replicated, and when row-level security is
required.
No refresh schedule is required.
In this situation, the dataset in the Power BI
service is a connection string only.
Direct connect supported for:
Azure SQL DB
Azure SQL DW
Spark (HDInsight or On-Prem)
SSAS Tabular
Importing vs. Connecting to Data
Type of Item Import Connect
Embedded dataset from
Power BI Desktop
YesPower BI Refresh
Embedded dataset from
Excel – imported
YesPower BI Refresh
Embedded dataset from
Excel – stored in OneDrive
YesNo Refresh
Direct connect dataset
(from either Power BI
Desktop or Excel)
YesNo Refresh
Using an ‘Embedded’ DatasetData Stored & Refreshed in the Power BI Service
Refreshing an ‘Embedded’ DatasetWhen is a Personal Gateway Needed?
On-Premises
Data Source
Cloud Data
Source
SaaS
Connector
Gateway is needed to schedule refresh.
Typically no gateway is necessary for online sources.
Refreshed automatically once a day.
Direct
Connect
No refresh schedule needed. Though the Power BI service does
query the data source ~10 minutes to keep dashboard tiles
current (not configurable).
Refreshing an ‘Embedded’ DatasetPersonal Gateway on User Machines
Source: https://support.powerbi.com/knowledgebase/articles/649846-power-bi-personal-gateway
Communicates
on outbound
ports: TCP 443
(default), 5671,
5672, 9350
thru 9354.
Does not
require
inbound ports.
Using a ‘Direct Connect’ DatasetLive Queries to Source (No Data is Stored in Power BI Service)
Workspaces, Sharing, and Collaboration
Power BI Service Limits
# of reports per
dataset: 200
(Multi-page reports
still count as 1)
# of
datasets:
200
Size for embedded dataset
(non-direct connect):
250MB compressed
# of
dashboards:
100
# of reports:
40,000
Workspace data storage:
1 GB (Free license)
10 GB (Pro license)
API calls:
Single push: 10,000 rows
Continuous push: 500 rows once per second
Max rows per table: 5,000,000 (or 2,000,000 if continuous)
Pending requests: 5
Per user account or group:
Per tenant: Global data storage: 10 GB x # of Pro licenses purchased
Sharing to a group > 500 members is
not supported
Who Needs a Pro License?Freemium model: Free license & Pro license
Retail pricing of a Power BI Pro license is $9.99 / user / month.
Both producers and consumers need a Pro license if
any ‘paid’ features are being utilized.
Paid features include content which is:
Scheduled to be refreshed more frequently than daily
Streamed in excess of 10k rows/hour
Receives data via a direct connect data source (ex: SSAS Tabular)
Receives data via the Data Management Gateway or Personal Gateway
Shared across one or more Power BI Pro users
Published via organizational content packs
Published to a Group workspace
Queries shared via the Data Catalog (not yet available in V2)
Power BI (V2) End-to-End
Bimodal BI
Bimodal BI & Analytics
Traditional
Corporate BI
Defined IT-driven processes
Reliable
Governed & secured
Standardized
Agile
Self-Service BI
Business-driven
Rapid delivery
“Less” governance
Exploration freedom
Two “modes” for development
& delivery of information
Use Cases for Power BI
Power BI used for
data shaping,
modeling, and
reporting(data typically is
replicated & refreshed
in Power BI)
• Small individual self-service projects
• Data mashups (up to 250MB compressed in web)
• One-time or infrequent analysis
• ‘On the fly reporting’ with Q&A
• Standalone SaaS connectors
• APIs to programmatically push data to Power BI
Query Model Report
Power BI used for
reporting only(data queries only; not
replicated in Power BI)
• Requires direct connection to source data
• Reports from source w/ higher data volumes
• Reports from source w/ row level security (SSAS Tabular)
Report Only
Bimodal BI: Where Power BI Can Fit
Self-Service BI Corporate BI
Upsize
Mashup of
different
data sources
Standalone SaaS
Connectors
(ex: SalesForce,
CRM)
Application
Integration
(APIs)
Machine
LearningStreaming
Data
Warehouse
SSAS Tabular
ModelExcel Data Model
(Power Pivot) Prototype
One-time or
infrequent
analysis
Augment
corporate
BI solutionsPower BI Reporting
Supply info
needed
very quicklyData
LakeAnalytical
DW & Analytics Environment
Recap +
Questions
Recap of Main Points Various options, each with its own strengths and shortcomings, will
remain as viable choices for delivery:• Excel (Add-Ins Only)
• Power Pivot for SharePoint (On-Premises)
• Pyramid Analytics (3rd Party On-Premises)
• Power BI (SaaS)
• Coming at a later date: More complete integration within applications
Power BI is continually evolving and growing in its role as a self-
service BI tool, as well as elements of corporate BI
A hybrid approach can be utilized to take advantage of existing
BI/DW assets
A purposeful “Bimodal BI” approach can attempt to balance many
different agile and traditional needs
Things to Consider Getting Started Decision on using Power BI Desktop, or Excel, or both
Use of Office 365 unified groups
Organization of content by subject area and/or user security boundaries
Securing access to content and row-level security needs
Types of users; who will be consumers vs publishers
Data source types and usage
Options for distributing, publishing, sharing content
Melissa Coates
Twitter: @sqlchick
Blog: sqlchick.com
Check “Presentations & Downloads” at sqlchick.com for this slide deck.
Creative Commons License:
Attribution-NonCommercial-NoDerivative Works 3.0
Thanks for Attending!