Building a Modern Data Warehouse in Azure for Power BI
About Us
Phil Spokas
email phil@intellitect.com
Web Intellitect.com/phil
Twitter @philspokas
Linked In /in/philspokas web Intellitect.com
Twitter @intellitect
Facebook fb.com/intellitect
Building a Modern Data Warehouse Solution for Power BI
1.Why build a Data Warehouse solution
2.Walk through building Modern Data
Warehouse solution in the cloud
3.Wrapping up
Data Warehouse Solution
Power BI, a Data Analytics Solution … in a box
Power BI let’s you experience your data
When is a Data Warehouse Solution not needed?
1. Not an enterprise use case or
scenario
2. Simple model, few reports
3. Very few users
4. Short term use
Why is Power BI not a Data Warehouse solution
1. Performance
2. Data Quality
3. Reliability
4. Access and Updates
So you decided you do need a Data Warehouse solution… now what?
Build a modern Data Warehouse solution in the cloud …
Demo Scenario
• Project Tracking System
• Reports and Analysis on hours and services by customer and service
type
• Transactional system reports from remote devices to an on premise
system
Ingest
Source Staging
pt-source pt-staging
Azure Data
SQL Server Factory Azure SQL
Azure Data Factory
to Azure SQL On premise Azure
Azure Data Factory
Components
• Azure cloud service for cloud scale
ingestion and transform pipelines Pipelines
• Built in monitoring and Activities
management Azure SQL Data
1.2 GBps
• SSIS support in V2
Warehouse Triggers
Azure Blob
• Currently 67 data sources storage
1.0 GBps Data sets
• Designed to move large amounts of Azure Data Lake
1.0 GBps
Linked Services
data Store
Azure SQL and Azure Data Warehouse
• It’s just SQL Server, managed by Azure
• Good for databases up to about 2 TB to 4 TB though your mileage
may vary
• Managed instances for SQL Agent and Cross DB joins now available
and then … Prepare and Transform
Staging
pt-staging pt-reporting
Transform
Azure SQL
Azure Functions
Azure SQL
Transform and Load
using Azure Functions Staging
pt-clients
Azure SQL
Azure Functions
• Serverless compute
• Build with PowerShell, Node.js, C#, …
• Easy access to Azure resources
• Hybrid connectivity as needed
• Scale as needed
and then … Publish with Analysis Services
pt-reporting
Dimensional
model
pt-model
Azure SQL Azure Analysis Services
• Common names
• Standard business calculations
• Pre build joins
Azure Analysis Services
• SQL Server Analysis Services Tabular,
but deployed to Azure
• Same as data model in Power BI, Excel
(Power Pivot)
• Get Data with “M” (Power Query)
• Develop and deploy with SSDT or web
designer (in preview)
• If you have a Power BI Desktop file
model (pbix), you can import it
• Connects to *many* data sources
• Async refresh with REST API
and then … Consume with Power BI
Presentation Layer – Power BI
• Power BI Reports are easily
created off of a well structured
data model
• Direct Query eliminates
refreshes
• Reports easily added, updated,
extended
Data Warehouse and BI Process Flow
Extract Transform
Source Staging Model
Load
Azure Data Factory Azure Function Azure SQL DB
Azure SQL DB Analysis Services
Master/
Reference
Azure SQL DB
Scaling Up and Out
• Azure Data Factory handles Enterprise and Big Data scale
• Azure SQL can go to 4 TB
• Azure Data Warehouse starting at 1 TB to Petabytes
scale
• Azure Analysis Services
• Power BI Premium with dedicated
• Azure Resource Manager (ARM)
• Azure Active Directory
• VPN from point to site to Express Route dedicated
Azure Data Warehouse
• Scale to Azure Data Warehouse as
requirements grow
• MPP
• Polybase
• Potentially better cost model: scale
cpu separate from size
• 1 TB generally the minimum
References
docs.microsoft.com The place to go first for documentation on
Azure and Microsoft technologies
Azure Essentials Recently published site
Data Modelling and Analytics in Azure Part of the Essentials program, quick
overview of this entire presentation…
without the demos
Azure Data Warehouse Good review of the platform and
recommended practices
Power BI Home of Power BI on the web and a great
place to get started
