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 

TomMartens's avatar
TomMartens
Super User
1 year ago

Hey PrabhakaranM ,

 

from my understanding you do not use Direct Query?
Nevertheless, do not use ODBC, use the Azure Databricks connectivity that comes with Power BI Desktop / Power BI Service.

 

Basically, SQL Server is better if there are multiple connections at the same time, but this is only important if you are using Direct Query connection modes.

 

If you are using Azure Databricks, then I recommend that all data transformations are done in Power Query should be moved to Databricks, this is what Databricks is good at. Of course this requires "communication" between Power BI users and the Databricks team.

 

Regarding load times, not sure if you are using Power BI Premium or not and if you are using a on-premises data gateway.

Depending on the location of the SQL Server, you also have to remember that the data travels from SQL Server to Power BI Service vs. from Databricks to Power BI Service. If an on-premises data gateway is involved for the latter, then data travel will add a lot to the data load / data refresh duration.

Please check if Azure Databricks and the Power BI Service are in the same region. If they are not, you may have to reach out to your Azure Infrastructure architects and ask how these different data centers are connected.

 

Hopefully, this helps to tackle your challenge.

Regards,

Tom

  • PrabhakaranM's avatar
    PrabhakaranM
    Frequent Visitor
    1 year ago

    Hey Tom... Thank you for your time and response.
    I want to give more clarifications as you asked few questions.
    1. I am using Azure Databricks connector found in Power Query.
    2. All the workspaces and servers are in same region.
    3. The workspaces is Premium Per User.

    4. The connectivity mode is Import and client doesn't wants to change it.

    4. The tranformation is power bi are complex and stops at the first applied step. Hence without the query folding the refresh times are quicker when the datasource is SQL Server. Whereas Azure Databricks is take 4x time to complete a scheduled refresh. Hope this gives more insight on the situation.

    As per my knowledge there is no bottleneck from databrick side. What could be the most reasonable issue?