Need Help: Incremental Refresh Issues with MySQL Stored Procedure and User Hierarchy Bridge Table

[sanu]

Hello Community Members,

I’m looking for some expert advice and suggestions regarding a scenario involving Power BI, MySQL, and incremental refresh.

I have a view called User_View in MySQL that contains user information. Since I’m using an older version of MySQL that doesn’t support CTEs, I’ve created a stored procedure to handle User > Admin hierarchy mapping.

To enable incremental refresh, I’ve added RangeStart and RangeEnd parameters in the stored procedure and am passing Power BI’s RangeStart and RangeEnd values when calling the procedure.

Here’s an example of how I’m calling the stored procedure from Power BI:

Both the User_View and the output of the stored procedure (bridge table) are filtered by RangeStart and RangeEnd, and incremental refresh is configured using the UpdatedDate column in both.

Power Query formula for calling the SP.

let
RangeStartText = DateTime.ToText(RangeStart, "yyyy-MM-dd HH:mm:ss"),
RangeEndText = DateTime.ToText(RangeEnd, "yyyy-MM-dd HH:mm:ss"),
Source = MySQL.Database(
"Test.mysql.database.azure.com",
"TestDb",
[Query = "CALL sp_bridge_table('" & RangeStartText & "', '" & RangeEndText & "')", CommandTimeout = #duration(0, 3, 20, 0)]
)
in
Source

The Challenge:

There are scenarios where a user's UserType may change (e.g., from "User" to "Admin" or vice versa). As a result, duplicate records appear in the archived data from User_View, likely due to changes not being correctly detected during the refresh.

I’ve enabled "Detect data changes" using the UpdatedDate column in both the view and the stored procedure output, but still encountering issues. The bridge table is designed to create a row for each user along with their AdminEmail to help filter the fact table. The Fact Table contains user events and when an Admin logged in the same Group, they can see thier own and user under that groups data.

However, due to the duplication and inconsistent updates in User_View, the bridge table breaks and filtering in the fact table does not work as expected.

Questions:

1. Has anyone encountered a similar issue?

2. Would it be better not to apply incremental refresh on both User_View and the bridge table, and instead perform a full refresh every time?

3. Any suggestions or best practices to resolve this?

Looking forward to your inputs.
Thank you!

[v-dineshya · Accepted solution]

Hi @sanu ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @vojtechsima , @lbendlin , Thank you for your prompt responses.

Hi @sanu , In addition to @vojtechsima ,@lbendlin  responses, I am adding some more points.

1. Power BI Service may not override RangeStart and RangeEnd parameters correctly after publishing. This will cause only partial data being refreshed. Use dummy variables in reference queries to pass RangeStart and RangeEnd explicitly and check the query folding is enabled .

2. Please check whether  the stored procedure supports datetime parameters and returns consistent schema. And avoid referencing the stored procedure output in other queries, it will reflect the refresh behavior.

3. If UserType changes, consider those changes in a separate audit table or Use a surrogate key to differentiate historical records from current ones.

4. Please check the bridge table is refreshed properly that sync with User_View. If the stored procedure logic is complex, consider that output into a physical table and refresh that table. Use Power BI Dataflows to stage the bridge table separately and apply incremental refresh.

Question: Would it be better not to apply incremental refresh on both User_View and the bridge table, and instead perform a full refresh every time?

Solution: Not necessary. Incremental refresh offers performance and reliability, especially for large datasets. if your stored procedure or view logic is too complex, full refresh might be more stable.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @sanu ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @sanu ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @sanu ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[lbendlin]

and incremental refresh is configured using the UpdatedDate column 

That column is not suitable.  Incremental Refresh depends on an immutable value like "CreatedDate".

[vojtechsima]

Hey, @sanu ,

at first glance, I wouldn't use Incremental refresh for a slowly changing dimension (or perhaps accidental SCD), or any source where you can get duplicates (that happens because of the archive). I don't know how big the view/table is, but generally, if you have couple of mil. rows, it should be definitely fine. So Incremental refresh on fact table only.

Alternatively, create a proper Slowly changing dimension type 2, where you have from, to fields and you can filter with parameters on that.