Forum Discussion

HamidBee's avatar
HamidBee
Icon for Power Participant rankPower Participant
2 years ago
Solved

Issue with Delta Table Overwrite in Lakehouse Once Connected to Power BI Report

Hello everyone,

 

I'm currently facing a challenge with a lakehouse setup in Microsoft Fabric and I'm hoping to get some insights or solutions from this knowledgeable community.

 

Context: I've created a lakehouse and within it, I set up a folder populated with Excel files. Using a Spark notebook, I perform PySpark transformations on these files. The primary operation is to append these files and then overwrite an existing Delta table. This process has been working flawlessly until a certain point.

 

Issue: The problem arises after I create a Power BI report. The process is as follows:

 

  1. I create a Power BI report by selecting 'Get Data' -> 'SQL Server', then inputting the SQL endpoint, database name, and a SQL query for DirectQuery to fetch data from the Delta table.
  2. I successfully create a visualization in Power BI and publish this report to my workspace.

However, after this step, whenever I add new Excel files to the designated folder and run my notebook script, the script executes successfully but fails to overwrite the Delta table. This issue only occurs once the Delta table is connected to a Power BI report or has a dependency on a Power BI report.

 

Attempts to Resolve:

  • I've ensured that the script runs successfully without any errors.
  • The issue only manifests after linking the Delta table with a Power BI report.

Seeking Help:

  • Has anyone encountered a similar issue with Delta tables in Lakehouse, especially in relation to Power BI dependencies?
  • Are there any known limitations or considerations when linking Delta tables to Power BI that might be causing this issue?
  • Could this be a permissions or locking issue caused by Power BI's connection to the Delta table?

Any insights, suggestions, or guidance would be immensely appreciated. I'm looking to understand the root cause of this issue and find a viable solution to ensure continuous data flow and updating in my lakehouse environment.

Thank you in advance for your time and help!

data engineering
data science
data warehouse
one lake
power bi
  • HamidBee's avatar
    HamidBee
    2 years ago

    I turned on DirectLake:

    and this fixed my issue. Strangely, this was greyed out and was only able for me to switch it on once I created a Power BI report with data build on the lakehouse and published it to the workspace. 

     

8 Replies

  • HamidBee's avatar
    HamidBee
    Icon for Power Participant rankPower Participant
    2 years ago

    From my investigation the data only actually populates in the table when I click:

    and then hit the refresh button. This is so strange. Someone please help!.

     

    • v-cboorla-msft's avatar
      v-cboorla-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      2 years ago

      Hi HamidBee 

       

      Thanks for using Fabric Community.

       

      Since you are converting the files and loading them into a Lakehouse. Have you created a custom semantic model that they can reference to create the report and use Direct Lake mode? 

       

      Thanks

      • HamidBee's avatar
        HamidBee
        Icon for Power Participant rankPower Participant
        2 years ago

        Hi. I'm not quite sure what you mean by "creating a custom semantic model that they can reference to create the report and use Direct Lake mode". What I did was I obtianed the SQL endpoint for the Lakehouse and I connected to it directly from Power BI Desktop. by typing a T-SQL query. Let me explain:

        1. I went to the Lakehouse settings and copied the endpoit.

        2. I opened Power BI Desktop, went to get data, SQL database

        3. I pasted the endpoint and then entered the database name along with the T-SQL query. I chose directquery as the method.

         

        Is this not the correct way?. Thanks.

  • HamidBee's avatar
    HamidBee
    Icon for Power Participant rankPower Participant
    2 years ago

    I turned on DirectLake:

    and this fixed my issue. Strangely, this was greyed out and was only able for me to switch it on once I created a Power BI report with data build on the lakehouse and published it to the workspace. 

     

    • v-cboorla-msft's avatar
      v-cboorla-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      2 years ago

      Hi HamidBee 

       

      Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.

    • Scott_Powell's avatar
      Scott_Powell
      Icon for Advocate IV rankAdvocate IV
      2 years ago

      hi HamidBee , glad that worked out for you. If my post above helped, would you please mark it as a solution. Good luck with your ongoing progress!

      Scott