Building a BI Solution in the
Cloud
Stacia Varga, Principal Consultant
2
Stacia (Misner) Varga
• Over 30 years of IT experience, 17 years of BI
experience
• Frequently speaks, writes, and teaches about
Microsoft BI technologies
• Principal Consultant and Founder,
Data Inspirations
• Data Platform MVP and SSAS Maestro
• Las Vegas User Group Chapter Leader and SQL
Saturday Organizer
• Wants you to make sure you can understand what
Agenda
The Current Microsoft BI Landscape
What’s New in SQL Server 2016 BI—On Premises
BI Client—Excel and Power BI The Gateway to Your Data
5
Classic Data Warehousing
Data Warehouse & BI Solutions
ETL
Power BI 1.0
Similar to the combination of SharePoint 2013 and Power View/Pivot
Added Q&A Functionality Limited Mobile Functionality
8 Excel Sa aS Da ta So ur ce s
Power BI web browser With natural language query, visual exploration, etc.
Az ur e-‐ re sid en t sy st em s Azure Stream Analytics Azure SQL/DW HDInsight
Power BI Mobile Apps with dashboards, alerting and visual exploration
AS Tabular Corporate Data Sources PBI Desktop Power BI AS Connector On -‐pr em ise sy st em s / o th
er Real time Dashboards using Push API
Power BI Personal Gateway
Infrastructure as a Service (IaaS)
VM running SQL Server, Virtual Network
Cloud-‐Only Model
Office 365 Azure
Hybrid Model #1 On-Premises Assets Domain Controller Data Management Gateway Office 365 (SaaS) VM running SQL Server, Virtual Network (IaaS)
Client SQL Server ServicesAnalysis SQL Database, HDInsight, Storage
• Benefits
• Shut down during non-‐business hours during development cycle
• Scale up as workloads increase
• IP Addressing
• Dynamic
• Static (although properties show as dynamic)
• Data Warehouse Image Options
• Load an image from an on-‐premises source
• Use predefined image
o Up to 500GB for SQL Server 2012 o Up to 1TB for SQL Server 2014
• Performance Options
o Spread I/O over multiple filegroups and data files
o Use AlwaysOn Availability Groups in asynchronous replication mode o Implement Azure virtual load balancing
o Configure read-‐only routing
• BI Resources (SSIS, SSAS, SSRS)
o Spread over multiple VMs to reduce latency and scale out as needed
o Use same virtual network and affinity group as DW to keep all components
together during maintenance
Hybrid Model #3 On-Premises Assets Domain Controller Data Management Gateway Office 365 (SaaS)
Hybrid Model #4 On-Premises Assets Domain Controller Data Management Gateway Office 365 (SaaS)
SQL Database
One of the earliest Azure offerings (SQL Azure)
Originally a subset of both T-‐SQL language and SQL engine
Now nearly code complete with SQL Server
Architecture Control Node Compute Nodes Compute Nodes Compute Node SQL Server
Data Movement Service
Where does SQL Data Warehouse fit?
OLTP / DW workloads Lift and Shift
Customer managed
1GB-‐1TB+
OLTP/ DW workloads Net new development Fully managed service
1GB-‐1TB
DW workloads only Fully managed
Dynamic compute scaling and pause
ETL Strategies
Data Connections ETL Design Patterns
for the Cloud File Compression
Data Connections
Virtual Machine or On-‐Premises SQL Database
ADO.NET
ETL Design Patterns for the Cloud
Lookup
MERGE Sensitive Data
SELECT AuditKey from [dw].[DimAudit] where TableName = @tableName and ExecStartDT = @execStartDate
Use parameter names rather than ? for ADO.NET
Use ? for ODBC query but start parameter mapping with 1
MERGE dw.DimProductCategory AS target USING (SELECT Name, rowguid, ModifiedDate, ProductCategoryID, AuditKey
FROM tmp.scdProductCategory) AS SOURCE
Data Subsets
select
IsNull(Max(ProductAlternateKey), @minKey) from dw.DimProduct
"SELECT
File Compression On-‐ Premises SQL Server SQL Virtual Machine SQL Database
(or Alternate Data Center)
Blob storage
Azure Data Factory—ETL in the Cloud
ETL Service for Data
Transformation in Azure
Priced by service—you pay for what you
use
Still immature relative to SSIS
• Less Tooling
• Fewer Supported Data Sources
Cloud vs On-‐Premises
3 4
Short Term Use Rapid Scale Test Use Cases Pay as you go
Internet data source
Large long term implementations
Well known workloads Shared clusters
Large initial investment
On-‐
On-‐Premises BI—SQL Server 2014
SQL Server Excel 2013
SQL Server Reporting Services
SQL Server Analysis Services
• Tabular
• Multidimensional
SQL Server Integration Services SharePoint 2013 Excel • Power Pivot • Power Query • Power Map • Power View
Power BI Desktop
SQL Server 2016—BI Enhancements SSRS
HTML5
Sunburst and Treemap Charts Modern Report Builder
SQL Server 2016—BI Enhancements SSAS
GUI support for Extended Events Better performance for DirectQuery
Parallel Processing for Tabular models
Great deal of enhancements to DAX
SQL Server 2016—BI Enhancements SSIS AlwaysOn Support Incremental Package Deployment Support OData v4 Support Enhanced Logging
SQL Server 2016—BI Enhancements Engine
Revolution Analytics integration
Columnstore Enhancements
SQL Server Data Tools and BIDs
Finally One Unified Toolkit in Visual Studio
SSMS Support for
DAX One Visual
Studio for All the Versions
Power BI Desktop
Independent of Excel so it can evolve faster
Has its own In-‐Memory engine
Supports DAX
Complete dashboard design experience Revs every month—so stay on top of it
Power BI in Excel 2013
Power Query
Get, filter, and shape data Get, filter, and relate data, add calculations to modelPower Pivot
Power View
Gateways
Personal Gateway
Enterprise Gateway
SQL Server Analysis Services Connector Note—Personal and
• Domain Controller (DC) in Azure supports authentication if network
connection to on-‐premises DC
• AD Federation supports Single Sign-‐On for SQL Database, Azure
Management Portal, and Azure applications
• Multi-‐tenant sever environment security features similar to Office 365
• Some security elements managed by tenant
• VPN connections
• DNS servers
• Internet connectivity
• PaaS Components
• SQL Database
• HDInsight
• Security managed by control of inbound IP addresses
• IaaS Components
• Virtual Machine
• Virtual Network
• Security managed by VPN implementation
• Supports access to IaaS resources
• Requires assignment to new or existing affinity group
• Optionally supports on-‐premises or public DNS server for name resolution
• Certificate secures connection
between on-‐premises and cloud resource
• Easy option for quick start
• POINT-‐TO-‐SITE VPN • POINT-‐TO-‐POINT VPN
• Option 1: Windows server running
Routing and Remote Access Services
• Option 2: Approved VPN device on-‐
premises
Summary
Modern Business Intelligence has a lot of moving parts
Power BI simplifies combining them
SQL Server 2016 has big gains for BI
Whitepapers
Security and Azure SQL Database
http://tinyurl.com/h3jw5m3
Using Power BI in a Hybrid Environment
