Forum Discussion

PrabhakaranM's avatar
PrabhakaranM
Frequent Visitor
1 year ago
Solved

Longer Refresh Time After Switching from SQL Server to Azure Databricks

I cannot post any screenshots or visuals due to client policies restricting data disclosure. I am currently working on a project where the original architecture involved SQL Server as the data s...
Need Help
Tips and Tricks
  • Poojara_D12's avatar
    Poojara_D12
    1 year ago

    Hi PrabhakaranM 

    1. SQL Server vs. Databricks Performance:
      • Power BI performs better with SQL Server due to full query folding support, while Databricks has limited query folding.
    2. Databricks Connector Limitations:
      • The Databricks connector (via ODBC) adds latency and is less optimized compared to the SQL Server connector.
    3. Cluster Latency:
      • Databricks' distributed cluster adds overhead for job initialization and data fetching, especially for smaller datasets.

     

    1. Push Transformations to Databricks: This reduces refresh times significantly.
    2. Optimize Databricks: Use indexes, cache tables, and optimize queries in Databricks SQL.
    3. Cluster Settings: Ensure proper cluster configuration and enable SQL caching.
    4. Parallel Execution: Minimize dependencies between tables in Power BI to enable parallel queries.

     

    Did I answer your question? Mark my post as a solution, this will help others!
    If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

    Kind Regards,
    Poojara
    Data Analyst | MSBI Developer | Power BI Consultant
    Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

Poojara_D12's avatar
Poojara_D12
Super User
1 year ago

Hi PrabhakaranM 

  1. SQL Server vs. Databricks Performance:
    • Power BI performs better with SQL Server due to full query folding support, while Databricks has limited query folding.
  2. Databricks Connector Limitations:
    • The Databricks connector (via ODBC) adds latency and is less optimized compared to the SQL Server connector.
  3. Cluster Latency:
    • Databricks' distributed cluster adds overhead for job initialization and data fetching, especially for smaller datasets.

 

  1. Push Transformations to Databricks: This reduces refresh times significantly.
  2. Optimize Databricks: Use indexes, cache tables, and optimize queries in Databricks SQL.
  3. Cluster Settings: Ensure proper cluster configuration and enable SQL caching.
  4. Parallel Execution: Minimize dependencies between tables in Power BI to enable parallel queries.

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS