$30 off During Our Annual Pro Sale. View Details »

Power BI Everywhere - Power BI and SQL Server

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.

Christopher MANEU

January 30, 2023
Tweet

More Decks by Christopher MANEU

Other Decks in Technology

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