Power BI Everywhere - Power BI and SQL Server

What is the session about?
SQL Server is a great database, and Power BI a great Data analysis and visualisation tool. So, what happens when we use them together?

Who is it aimed at?

- Analysts
- Developers
- Data Engineers
- Business Analysts
- Power Platform Developers

Why should members attend?
In this session, we will go from simple to more advanced scenarios combining these two products:

- 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

About the series: Microsoft introduced the Intelligence Data Platform to help organizations accelerate innovations, achieve agility, and build on a trusted platform. We want intermediate power platform developers and BI analysts to leverage on the use of Azure to build scalable analytics and solutions and how they can derive insights using Power BI. We want to show the capability of Power BI across all functionalities of Microsoft Tools and how they are integrated to one another.

Transcript

1. PowerBI and SQL Server: Better Together Christopher Maneu @cmaneu Cloud Advocate - Data
2. 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
3. The different ways to connect to SQL Server and Azure SQL from Power BI
4. 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.
5. 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.
6. On Premises Data Gateway
7. 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.
8. How to do quick analysis on our data?
9. Profiling Data and Examining Structures Data profiling is understanding the state and structure of the data you are working with.
10. Analyze column distribution (Cardinality) • Helps you
11. 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.
12. Using the Analyze Feature Get fast, automated, and insightful analysis on your data.
13. Securely connect to production database without impacting production performance
14. Query Folding The process that lets Power Query generate a single query statement to retrieve and transform source data.
15. © 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.
16. 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
17. Next event – This Wednesday
18. Introduction to data analytics on Azure https://aka.ms/PowerBIAzure
19. Build data analytics solutions using Azure Synapse serverless SQL pools https://aka.ms/PowerBISQL
20. Thank you! Christopher Maneu @cmaneu Cloud Advocate - Data