Linked tables refresh 'successful' but are empty -...

oBi1 · ‎09-30-2024

I'm having this weird situation in Power BI service where all my dataflows update successfully, but everything is empty. When checking the refresh history excel document it says 'skipped' in the refresh status column.

Setup:

1) Staging: separete premium workspace

2) Transformation workspace:

- staging dataflow to bring in the required set of staging tables

- several layers of transformations in different dataflows ending up in creating DIM tables and FACT tables

- whenever I need a data source from one of the other dataflows I use 'linked tables'

Now I only need to refresh the 'staging dataflow' int he transformation workspace. That basically triggers all the other data flows (through linked tables).

It takes about a minute and then everything is done without errors. But upon closer inspection (e.g. opening the Fact dataflow) I see that all linked tables and the calculated fact table are without rows/data.

There are no date filters in any of the data flows so that can't be it...

I basically redid the whole thing to this setup as I got into error messages when I just pointed directly to the staging workspace - I got all these 'locked' table errors into the fact workspace (updating df 1 locked th fact df, then df 2 which also provides data to the fact df crashed etc).

I'm really struggling how to set up a 'complicated' stage - transformation - end result DIMs/FACTs setup through the service.

--> no, I can't get a database - company policy

siddharth1k · ‎09-30-2024

Perform the following steps and see if it works

Refresh the staging dataflow, transformation dataflows, and fact/dim dataflows in sequence to see if the issue persists.
Review the dependency chain between dataflows to ensure proper refresh sequence and no missing links.
Check if memory or CPU limits are affecting the dataflow refresh on the premium capacity.
Break down complex dataflows into smaller units, or decouple highly interdependent layers to ensure data isn't getting "locked" or skipped.

oBi1 · ‎10-02-2024

Hello! thank you for coming back.

I've reworked the dataflow setup for simplicity:
Staging workspace - holds staging tables

Data model workspace:
1) Dataflow transformations - uses 'linked tables' from staging and has some computed tables

2) dataflow dimensions - uses 'linked tables' from both staging as well as dataflow transformations - outcomes in computed tables

3) dataflow facts - uses 'linked tables'from staging and dimensions (NOT transformations as I've foudn that could create unwanted outcomes)

Both workspaces are PPU / Premium capacity

to your points:
- refresh in sequence: don't know how to do that as tables are all 'linked tables' refreshing dataflow 1 automatically starts refresh of 2 and 3...

- chain is quite simple 1 -> 2 -> 3 if you look at the schematic view, with the add inputs where appropriate from the other workspace but e.g. 3 doesn't directly link to 1

- I don't get locking errors at all. I do get skipped in the 'refresh status'. I have no idea why! the order (chain in previous bullet) is quite clear. It looks like the run engine decides to do a fact table first while the dims have not been completed refreshing..

Not using the computing engine is not a solution as then why would I need premium license and refreshes take ages.

A bit more research showed me this btw:
- it appears in dataflow 2 it skipped all the tables that did NOT use a linked table from dataflow 1 in their computation

so the refresh of 1 triggers the refresh of 2 but apparently ONLY for those computed entities that actually use one of the tables from 1.