Cepta The Future of Data with Power BI

The Future of Data
POWER BI AND THE DATA ECOSYSTEM
KELLYN GORMAN, AZURE DATA PLATFORM
ARCHITECT

© Microsoft Corporation
175 Zettabytes of Data in
the World by 2025

Average Human will have
5000+ Digital Interactions
per Day
https://www.forbes.com/sites/andrewcave/2017/04/13/what-will-we-do-when-the-worlds-data-hits-163-
zettabytes-in-2025/#28e14323349a

ActionsDecisionsData
Value
From Data to Decisions & Actions

Data already exists for many and its all
about doing more with it
The Azure Cloud simplifies advanced
analytics and services for everyone
Success for everyone involved and ability to
grow with the data
Microsoft brings technology to the World

Not Just One
Step
It’s not just connect Power BI to
data sources, it’s:
Extract
Transform
Load
◦ But where to?
◦ How?
◦ What is required?

Data Science VM
• Customized pre-configured
VM for data science pros
Deep Learning VM
• GPU based VM for training
deep learning models
Azure Machine Learning
• Predictive analytics services for
creating, deploying, &
managing predictive models
DataScienceTools
VisualizationTools
Reference
Architecture
Azure
Azure Active Directory
• Identity management & authentication across
Azure resources
Data sources
• Relational databases
• File exports
• Big data sources
Azure Data Factory
• PaaS hybrid ETL/ELT
service
• Can move data via
Data Factory Pipelines
or SSIS packages
Power BI
• SaaS analytics
• Excel integration
• Seamless, native
integration w/
Azure data sources
Excel
• Self-service analytics
• Pivot tables & charts
3rd Party
Visualization Tools
• Tools connecting
to SQL & Analysis
Services
AzureSynapseAnalytics
Azure Analysis Services
• PaaS semantic model
• Centralized calculations,
hierarchies, KPIs, etc.
• In-memory, compressed
Azure SQL Database
• PaaS SQL database
• Built in DR & HA
• On-demand scale
DataMarts&VirtualizedLayer
Azure Data Catalog
• Fully managed cloud metadata repository
• Enable data discovery & capturing team tribal knowledge
Azure Monitor
• Monitor cloud & on-premises environments to
maintain performance & availability
Power BI
Report Server
• Paginated reports
• Pixel perfect reports
• Document
generation
• Data driven
subscriptions
SQL Pool
SQL
Python
.NET
Java
R
Scala
Azure Synapse Studio
• Management
• Monitoring
• Security
• MetaStore

My Scripts to Deploy this All…
https://github.com/Dbakevlar/Modern-Data-Warehouse

Azure Synapse
Analytics
Limitless Scale
Powerful Insights
Unified Experience
Unmatched Security
Simpler Deployment

Azure Synapse Analytics
Integrated data platform for BI, AI and continuous intelligence
Platform
Azure
Data Lake Storage
Common Data Model
Enterprise Security
Optimized for Analytics
METASTORE
SECURITY
MANAGEMENT
MONITORING
DATA INTEGRATION
Analytics Runtimes
PROVISIONED ON-DEMAND
Form Factors
SQL
Languages
Python .NET Java Scala R
Experience Synapse Analytics Studio
Artificial Intelligence / Machine Learning / Internet of Things
Intelligent Apps / Business Intelligence
METASTORE
SECURITY
MANAGEMENT
MONITORING

Azure (15) Database & DW (26) File Storage (6) NoSQL (3) Services and App (28) Generic (4)
Blob storage Amazon Redshift Oracle Amazon S3 Cassandra Amazon MWS Oracle Service Cloud Generic HTTP
Cosmos DB - SQL API DB2 Phoenix File system Couchbase Common Data Service PayPal Generic OData
Cosmos DB - MongoDB API Drill PostgreSQL FTP MongoDB Concur QuickBooks Generic ODBC
Data Explorer Google BigQuery Presto Google Cloud Storage Dynamics 365 Salesforce Generic REST
Data Lake Storage Gen1 Greenplum SAP BW Open Hub HDFS Dynamics AX Salesforce Service Cloud
Data Lake Storage Gen2 HBase SAP BW via MDX SFTP Dynamics CRM Salesforce Marketing Cloud
Database for MariaDB Hive SAP HANA Google AdWords SAP Cloud for Customer (C4C)
Database for MySQL Apache Impala SAP table HubSpot SAP ECC
Database for PostgreSQL Informix Spark Jira ServiceNow
File Storage MariaDB SQL Server Magento Shopify
SQL Database Microsoft Access Sybase Marketo Square
SQL Database MI MySQL Teradata Office 365 Web table
SQL Data Warehouse Netezza Vertica Oracle Eloqua Xero
Search index Oracle Responsys Zoho
Table storage
90+ Connectors out of the box

Provisioning Synapse workspace
Provisioning Synapse is Easy
Subscription
Resource Group
Workspace Name
Region
Data Lake Storage Account

Develop Hub
Overview
It provides development experience to
query, analyze, model data
Benefits
Multiple languages to analyze data
under one umbrella
Switch over notebooks and scripts
without loosing content
Code intellisense offers reliable code
development

OVER clause
Defines a window or specified set of rows within a query
result set
Computes a value for each row in the window
Aggregate functions
COUNT, MAX, AVG, SUM, APPROX_COUNT_DISTINCT,
MIN, STDEV, STDEVP, STRING_AGG, VAR, VARP,
GROUPING, GROUPING_ID, COUNT_BIG, CHECKSUM_AGG
Ranking functions
RANK, NTILE, DENSE_RANK, ROW_NUMBER
Analytical functions
LAG, LEAD, FIRST_VALUE, LAST_VALUE, CUME_DIST,
PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK
ROWS | RANGE
PRECEDING, UNBOUNDING PRECEDING, CURRENT ROW,
BETWEEN, FOLLOWING, UNBOUNDED FOLLOWING
Windowing functions
SELECT
ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC
) AS "Row Number",
LastName,
SalesYTD,
PostalCode
FROM Sales
WHERE SalesYTD <> 0
ORDER BY PostalCode;
Row Number LastName SalesYTD PostalCode
1 Mitchell 4251368.5497 98027
2 Blythe 3763178.1787 98027
3 Carson 3189418.3662 98027
4 Reiter 2315185.611 98027
5 Vargas 1453719.4653 98027
6 Ansman-Wolfe 1352577.1325 98027
1 Pak 4116870.2277 98055
2 Varkey Chudukaktil 3121616.3202 98055
3 Saraiva 2604540.7172 98055
4 Ito 2458535.6169 98055
5 Valdez 1827066.7118 98055
6 Mensa-Annan 1576562.1966 98055
7 Campbell 1573012.9383 98055
8 Tsoflias 1421810.9242 98055
Azure Synapse Analytics > SQL >

Overview
A materialized view pre-computes, stores, and maintains its
data in Azure SQL Data Warehouse like a table.
Materialized views are automatically updated when data in
underlying tables are changed. This is a synchronous
operation that occurs as soon as the data is changed.
The auto caching functionality allows SQL DW Query
Optimizer to consider using indexed view even if the view is
not referenced in the query.
Supported aggregations: MAX, MIN, AVG, COUNT,
COUNT_BIG, SUM, VAR, STDEV
Benefits
Automatic and synchronous data refresh with data changes
in base tables. No user action is required.
High availability and resiliency as regular tables
Materialized views
-- Create indexed view
CREATE INDEXED VIEW Sales.vw_Orders
WITH
(
DISTRIBUTION = ROUND_ROBIN |
HASH(ProductID)
)
AS
SELECT SUM(UnitPrice*OrderQty) AS Revenue,
OrderDate,
ProductID,
COUNT_BIG(*) AS OrderCount
FROM Sales.SalesOrderDetail
GROUP BY OrderDate, ProductID;
GO
-- Disable index view and put it in suspended mode
ALTER INDEX ALL ON Sales.vw_Orders DISABLE;
-- Re-enable index view by rebuilding it
ALTER INDEX ALL ON Sales.vw_Orders REBUILD;

Monitor Hub
Overview
This feature provides ability to monitor orchestration,
activities and compute resources.

Manage Hub
Overview
This feature provides ability to manage Linked Services,
Orchestration and Security.

SQL On-Demand
Overview
An interactive query service that provides T-SQL queries over
high scale data in Azure Storage.
Benefits
Serverless
No infrastructure
Pay only for query execution
No ETL
Offers security
Data integration with Databricks, HDInsight
T-SQL syntax to query data
Supports data in various formats (Parquet, CSV, JSON)
Support for BI ecosystem
Azure Storage
SQL On
Demand
Query
Power BI
Azure Data Studio
SSMS
SQL DW
Read and write
data files
Curate and transform data
Sync table
definitions
Read and write
data files

Languages
Overview
Supports multiple languages to develop
notebook
• PySpark (Python)
• Spark (Scala)
• .NET Spark (C#)
• Spark SQL
Benefits
Allows to write multiple languages in one
notebook
%%<Name of language>
Offers use of temporary tables across
languages

Languages – PySpark (Python)

What Happens to Existing Azure SQL
Data Warehouse?
Its not going “away”
Current SQL Data Warehouses will continue
Azure Portal will soon display “Synapse SQL Pool”, which is more accurately named
◦ SQL Pool
◦ SQL On-demand
◦ Spark Pool
◦ Code Artifacts
◦ Metadata

Data Is Ready- Now What?
Push to Azure DB
Leave in Azure Data Lake Storage
Connect to Analysis Services for Multi-dimensional Modeling
Power BI for final modeling and visualizations/reports/dashboards/apps
Use third party tools with data

Experience your data, any way, anywhere

Live dashboards and interactive reports
146.03K145.84K145.96K146.06K 40.08K38.84K39.99K40.33K

Power BI family
Power BI Embedded
Power BI Report Server, (PBRS)

What Can
Power BI Do?
Visualizations
Interactive Dashboards
Paginated Reports
Integrated Apps
Data modeling

Web browserMicrosoft cloud
Microsoft cloud Non-Microsoft cloudOn-premises data Mobile apps

Web browser
HTML
Microsoft cloud
Power BI
Microsoft cloud Non-Microsoft cloud Mobile apps
Business analyst tools

Non-Microsoft cloudOn-premises data Mobile apps

Microsoft cloud Mobile apps
On-premises data

What Kinds of Visualizations?
Choose from numerous modern visualization types:
◦ Filter data:
◦ Slicer
◦ Display numeric values:
◦ Card, Multi Row Card, Table, Matrix, KPI
◦ Graphically visualize data:
◦ Bar, Column, Line, Combo, Scatter, Waterfall, Pie, Donut,
Funnel, Treemap, Gauge, R Script
◦ Spatially visualize data:
◦ Map, Filled map, Shape map (preview)
https://powerbi.microsoft.com/en-us/developers/custom-visualization/

Custom Visuals
Custom visuals can be imported to extend beyond the out-of-the-box visualizations
◦ A gallery of visuals created by the Power BI community is available at https://app.powerbi.com/visuals
◦ Browse through the visuals or submit one of your
own for others to use
◦ The list of available visuals is growing each month
◦ Custom visuals will render in the Power BI service

Designing reports
Custom visuals: Gallery (subset)
* And the list
is growing!

What Data
Sources
Can Power
BI Connect
TO?
Over 100 different data sources
What is available in the service may be
be different than the desktop
If hybrid connection, (on-prem/non-
Azure cloud) the Power BI Gateway
will be required

Get [A LOT
OF] Data
**Custom connectors can also
be created.

Modeling Data
Work in Data View to inspect, explore, and understand data in the
model
 It is a different experience from how you can view tables, columns, and
data in Query Editor
 This is a view of the data after it has been loaded into the model

Creating
Calculated
Columns
Created from Data View in Power BI
Aggregate data
Combine columns to create unique identifiers

Measures
Uses DAX, (Data Analysis Expressions)
Over 200 functions, operators and constructs
Incredibly flexible
Similar to Excel formulas, (MDX) but designed to work with relational data

KPIs
Key Performance Indicators, (KPIs) also called
“strategic measures”
◦ Helps understand if company goals are being
achieved
◦ Goal values must be part of the dataset in Power BI

KPI Demonstration
Choose the data
Choose the timeline for the KPI
Sort by the indicator
Change to a KPI visual
Update any fields

Performance Analyzer
In Power BI Desktop
Open Report
Start Recording
Optimize for Time

Turning on
Previews
This changes every release,
so update what previews
you’d like to try out!

Row Level Security
Excludes data from visualizations and reports
Is set up at report level
Uses DAX Filters
Filters assigned to roles
Assigned to users and groups through roles
Admins can test out roles before releasing to
production

Power BI Gateway
The Power BI Gateway—Personal is used to refresh supported on-
premises data sources
 Only available in 64-bit
 Runs as a service if configured with an administrator account;
otherwise runs as an application
 Data transfer is secured (SSL) through Azure Service Bus
 Often no requirement to open firewall ports, (unless VM installation)
 Certain scenarios cannot be scheduled for data refresh:
 Custom SQL statements
 Excel worksheet data
 Direct Connect or DirectQuery data sources

Power BI Gateway- Enterprise
Installation of the On-Premises Data Gateway serves large groups of users
to refresh supported on-premises data sources
 It is the successor to the Power BI Gateway—Enterprise
 IT can:
 Centrally manage the set of users who have access to the underlying data
sources
 Gain visibility into gateway usage, such as most commonly accessed data
sources, and the users accessing them
 Data sources:
 SQL Server Analysis Services
(Multidimensional and Tabular modes)
 SQL Server
 Oracle, Teradata, SAP HANA…

Analyze in Excel The most popular data visualization tool in
the world.

Report Info in Power BI
Service

Options In
Service
New Look on/off
Notifications
Settings
Download
Help
Feedback

Content Packs to Apps- Why?
Allows for distinct collection of reports/dashboards/visualizations
One link to access them all vs. searching
Package and distribute
Notifications, alerting and row level security

Creating a Content Pack/App
Significantly Easy
No Code Solution
Allows you to share multiple reports/dashboards/datasets with groups/users
Can embed URL to other applications

Summary
Power BI has extensive visualizations, reporting and
dashboard analytics features
Ability to pull data from over 100 data sources
As part of the larger analytics solution with Azure
Synapse Analytics, an enterprise solution for
analytics, IOT and machine learning can be created
with ease.

Resources
Power BI site
 http://powerbi.microsoft.com
Power BI documentation
 http://support.powerbi.com/
Power BI community
 http://community.powerbi.com/
Power BI blog
 http://blogs.msdn.com/b/powerbi/

References
Power BI Desktop knowledge base
 https://support.powerbi.com/knowledgebase/topics/68530-power-bi-desktop
Tips and tricks for creating reports in Power BI Desktop
 https://support.powerbi.com/knowledgebase/articles/464157-tips-and-tricks-for-
creating-reports-in-power-bi-d
DAX Resource Center
 http://social.technet.microsoft.com/wiki/contents/articles/1088.dax-resource-
center.aspx
Power BI Visuals Gallery
 https://app.powerbi.com/visuals

Thank you!
Kellyn Gorman
Azure Data Platform Architect
Microsoft Education
kegorman@microsoft.com

Cepta The Future of Data with Power BI

More Related Content

What's hot

Similar to Cepta The Future of Data with Power BI

More from Kellyn Pot'Vin-Gorman

Recently uploaded

Cepta The Future of Data with Power BI

Editor's Notes