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
   

   View Slide

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
   

   View Slide

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

   View Slide

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.
   

   View Slide

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.
   

   View Slide

6. On Premises Data Gateway
   

   View Slide

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.
   

   View Slide

8. How to do quick analysis on our data?
   

   View Slide

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

   View Slide

10. Analyze column distribution (Cardinality)
    • Helps you
    

    View Slide

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.
    

    View Slide

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

    View Slide

13. Securely connect to production database
    without impacting production performance
    

    View Slide

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

    View Slide

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.
    

    View Slide

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
    

    View Slide

17. Next event – This Wednesday
    

    View Slide

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

    View Slide

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

    View Slide

20. Thank you!
    Christopher Maneu
    @cmaneu
    Cloud Advocate - Data
    

    View Slide