PowerBI and SQL Server: Better Together Christopher Maneu @cmaneu Cloud Advocate - Data

Agenda The different ways to connect to SQL Server and Azure SQL from Power BI How to do quick analysis on our data? Securely connect to production database without impacting production performance

The different ways to connect to SQL Server and Azure SQL from Power BI

Select a Storage Mode  Specifies the storage mode of a table and lets Power BI determine how to cache data for reports.  Set the storage mode for each table individually.

Implications of using DirectQuery • Benefits: • Where data changes frequently. • Near-real time reporting is needed. • Supports large data volumes. • Supports multi-dimensional data. • Limitations: • Performance: Depends on the underlying data source. • Security: Understand how data moves between source and destination. • Modeling: Some modeling capabilities are limited or aren’t supported. • Transformation: Some data transformation techniques are limited.

On Premises Data Gateway

The importance of building the right schema Data warehouse schema designs • Often, a data warehouse is organized as a star schema, in which a fact table is directly related to the dimension tables, as shown on the right. • When attributes can be shared by multiple dimensions, it can make sense to apply some normalization to the dimension tables and create a snowflake schema.

How to do quick analysis on our data?

Profiling Data and Examining Structures Data profiling is understanding the state and structure of the data you are working with.

Analyze column distribution (Cardinality) • Helps you

The Q&A Feature • Explore data in your own words. • Ask natural language questions. • A “self-help” feature for insights the user is interested in.

Using the Analyze Feature Get fast, automated, and insightful analysis on your data.

Securely connect to production database without impacting production performance

Query Folding The process that lets Power Query generate a single query statement to retrieve and transform source data.

© Copyright Microsoft Corporation. All rights reserved. Scale analytics with Azure Synapse Analytics and Power BI • Azure Synapse Analytics is a unified, end-to-end solution for large scale data analytics. • Using Power BI with Synapse enables analysts to process large-scale data quickly. • Power BI and Synapse are natively integrated.

On-premises Azure Synapse Analytics Azure Machine Learning Azure Data Lake Storage Power BI Cloud data IoT data SaaS data ETL SQL pools Spark pools Azure Synapse Link for SQL Server Seamless analytics over on-prem operational data Break the wall between operational and analytical stores New change feed capability reduces impact on OLTP workloads Near real-time latency between SQL Server and Synapse Analytics Use SQL pools so harness the full power of a scalable warehouse solution Analyze all your data using both Spark and SQL runtimes in Synapse ETL expensive, out of date, and affects operational workloads https:/aka.ms/synapselinksql

Next event – This Wednesday

Introduction to data analytics on Azure https://aka.ms/PowerBIAzure

Build data analytics solutions using Azure Synapse serverless SQL pools https://aka.ms/PowerBISQL

Thank you! Christopher Maneu @cmaneu Cloud Advocate - Data