Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Disable data load in power query editor

We work on semantic model and which is connected with databricks and our requirement is we want to do some modification in source table but that change should not reflect in power bi.. we loaded data...
General Comment
Need Help
  • MasonMA's avatar
    MasonMA
    1 year ago

    Anonymous 

     

    Hi, 

    Even if a query is unchecked for load and refresh, the semantic model still holds a reference to the source table especially if you're using DirectQuery or Composite Model, or the query is used in a relationship/downstream reference, Or, the model schema still knows about that table’s structure.

     

    Power BI doesn’t cache or isolate the schema by default, so if your Databricks table changes, Power BI reflects those schema changes, even if it doesn't reload the data.

     

    That being said, instead of connecting directly to a live or frequently updated table in Databricks, you may 

    • Create a snapshot or staging table in Databricks that holds a static version of the data.
    • Connect Power BI to this static table.
    • Only update this table manually when you want changes to reflect in Power BI.

    You can run this in a Databricks SQL notebook or SQL Editor if it is an one-time copy, 

    CREATE OR REPLACE TABLE database.snapshot_table
AS
SELECT * FROM database.source_table;

    This will give you full control over when changes are visible.

     

    Hope this helps:) 

MasonMA's avatar
MasonMA
Super User
1 year ago

Anonymous 

 

Hi, 

Even if a query is unchecked for load and refresh, the semantic model still holds a reference to the source table especially if you're using DirectQuery or Composite Model, or the query is used in a relationship/downstream reference, Or, the model schema still knows about that table’s structure.

 

Power BI doesn’t cache or isolate the schema by default, so if your Databricks table changes, Power BI reflects those schema changes, even if it doesn't reload the data.

 

That being said, instead of connecting directly to a live or frequently updated table in Databricks, you may 

  • Create a snapshot or staging table in Databricks that holds a static version of the data.
  • Connect Power BI to this static table.
  • Only update this table manually when you want changes to reflect in Power BI.

You can run this in a Databricks SQL notebook or SQL Editor if it is an one-time copy, 

CREATE OR REPLACE TABLE database.snapshot_table
AS
SELECT * FROM database.source_table;

This will give you full control over when changes are visible.

 

Hope this helps:) 

  • BA_Pete's avatar
    BA_Pete
    Super User
    1 year ago

    Hi Anonymous ,

     

    MasonMA's explanantion is correct and they have provided you a good solution to your question.

    I just want to tag onto this and suggest (possibly for other users reading in future that don't have Databricks), that the same effect can be achieved by using Dataflows (DF) in the Power BI Service.

    You can query your snapshot in a DF, refresh it once to initially load the data/snapshot, then don't refresh the DF again until you want to pick up the latest changes. Connect your model to this DF instead of the source system to gain the same control over when data is updated.

     

    Essentially the same method, just using a more-widely accessible tool/platform than Databricks.

     

    Pete