Power BI – Scheduled refresh stuck

Introduction

When creating reports, there are several modes through which we can import data. In our case, reports had been created using the “import mode.” In this mode, the data is imported or copied into our reports to be included. This then allows us to build (for example) dashboards.

Once the report is published, the data presented by the report is not automatically updated. Therefore, if data is added between the moment the report is published and the report is displayed, it will not be accessible or visible to the end users.

Here are some details:

• I create my report
• I connect to my datasource
• I choose the “import mode” to import my data

• From what we can see (via this mode), Power BI will call the Microsoft SQL Server Analysis Services to load the data into memory (Power BI launches an instance of SSAS through a separate process).

We can observe this:

• Start of data loading via SSAS

• Data is being loaded via SSAS

Changes are then made to how data is structured and organized (through xVelocity, VertiPaq).

Thus, it is necessary to load the entire dataset before being able to query it. The problem arises when the report has been published and the data is updated after publication. When the report is run, it does not reflect the changes made after its publication.

Here is an example:

• My report queries the LinkTypes table, which has 2 types of links: “Linked” and “Duplicate”
• Once published, I can see a table displaying this data

• I add a new row to the LinkTypes table

• A new run of the report does not display this data

To address this issue, it is possible to create scheduled refreshes to update the data and ensure the report has up-to-date information.

What is the problem?

In the case of our client, the scheduled refreshes were not occurring and remained stuck in the “in progress” or “refresh in progress” state. As a result, the reports did not have up-to-date data.

To understand the origin of the problem, we tried to analyze logs:

• We checked the Power BI logs. However, we found nothing. There were no errors (no timeouts, no exceptions, etc)

• The event viewer of the server hosting the Power BI service
• The SQL Server error log that hosts the Power BI databases
• SQL Server waits
• Extended events
• The jobs created by Power BI (which correspond to the scheduled refreshes) did not return any errors

The functioning model of scheduled refresh

To understand where the problem might be coming from, we need to analyze what happens when a scheduled refresh is requested.

When a scheduled refresh is requested, here are some of the operations that occur:

• The stored procedure “AddEvent” is called. This procedure inserts a row into the “Event” table:

• A row is added to the “Event” table

• Power BI is notified that data needs to be updated

• The status of the subscription is updated (the value of the “@Status” is in Fench because I triggered a scheduled refresh through a web browser that uses French language)

Other objects are then updated, such as the notifications table. The Event table is also updated to remove the corresponding event :

• The TakeEventFromQueue is a stored procedure that calls a delete operation

Problem Resolution

In our client’s case, the Events table contained data, but actions to execute a scheduled refresh were not adding any rows to the Event table. We decided to clear the contents of the Events table after backing up the database. After this action, the reports started updating correctly.

Thank you, Amine Haloui.