Forum Discussion
Power BI Dataflow - response too large to return
Can't use direct connection to BQ for access reasonsChallenge that assumption. Tell them that 650 GB is too much.
If they don't budge, use Incremental Refresh. Start VERY small, with a day or so. See how many rows you get. Recommended partition size is around 8 to 20 million rows.
Please explain "import does not work" - are you getting an error message?
Direct Query on a dataflow = datamart = lipstick on a pig (Azure SQL db being the lipstick and the dataflow being the pig). Possible, sure, but a travesty nevertheless.
You can consider switching the incremental refresh to the dataset instead and to completely remove the dataflow from the equation.
You can consider dataflow Gen2 and store the results as Delta Lake in Fabric.
Set it to refresh 1 day and store 10 days. Then evaluate the refresh log to see how many rows are in each partition. If there are substantially fewer than 8M rows per day then you can change your settings to 1 day and 3 months.
You will get the rolling 90 days automatically (plus some extra days to complete the months, as the actual month partitions will include not three but four months). You could change your setting to 1 day and 90 days but that would mean you would have 90 partitions. Only do that if necessary.
Okay, i just ran the refresh for store 1 day and refresh last 3 months. Also just from the dataset info on BQ there's an avg of about 4M rows per day.
So this should potentially have all the data for the past 90 days and more at the moment. Am just waiting for it to finish the run.
Once this is successful, i'll have to change the refresh schedule to be store x months and refresh 1 day, that way i get the latest day's data added, right?
Only concern here - the new data keeps on getting added with daily refresh but there is historic data present which as days pass will be much much more than 90 days. Will i run into storage/processing issues at that point?
Is there a way of automatic data pruning to get rid of historic data past 90 days?
Thanks!
Will i run into storage/processing issues at that point? Is there a way of automatic data pruning to get rid of historic data past 90 days?keep "x" at 3 and the Power BI service will do the pruning for you automatically.
I ran the refresh and it took about 45 mins, i checked the log and it looks rows were processed. I kept the incremental refresh at store from 3 months and refresh at 1 day.
I then tried connecting to the dataflow to verify and it's empty! Checked the query editor and the two parameters for Range Start and Range End have been created - the date though is historic and one in future. So i assume it was preset by the incremental refresh.Should i get rid of that filter which i initially had on the date to filter for last 30 days? Or does the incremental refresh supersede that and take into account days from the latest date available?
Where did i go wrong? Attaching the log for reference. Thanks again!
yes, looks like your day partitions processed fine. Do not add your own filters for the range, and do not use any transforms that break query folding.
Am sorry was that a yes for getting rid of the date filter which i had initially ? (last 30 days)
Because right now when connected to the dataflow, it's empty - shows nothing on there.
Just want to make sure.
The only tansformations i have on there is that date filter and one where i remove those 20 odd columns.
Oh actually one more i had to change the data type on the date column from date to date/time in order to use it for incremental refresh.Am sorry was that a yes for getting rid of the date filter which i had initially ? (last 30 days)yes
That worked! Thanks a ton for all your help!
Hello lbendlin back to this issue.
So, i was able to succesfully load the dataflow and have it run on a 90 day refresh.
Now, coming to connecting this dataflow to a PBI report - import does not work for that large of a dataset and for a direct query to dataflow i turned on the enhance compute engine but it still wouldn't give me an option to use direct query when connecting.
Please help on how i can use this dataflow now.Thanks.
Alternatively, at this point am open to exploring a data model option even but i suspect the dataset is huge even for that.
lbendlin parry2k SaiTejaTalasilaPlease explain "import does not work" - are you getting an error message?
Direct Query on a dataflow = datamart = lipstick on a pig (Azure SQL db being the lipstick and the dataflow being the pig). Possible, sure, but a travesty nevertheless.
You can consider switching the incremental refresh to the dataset instead and to completely remove the dataflow from the equation.
You can consider dataflow Gen2 and store the results as Delta Lake in Fabric.
So, i think the dataflow may work - i connected the PowerBI report to the dataflow and had it imported and refreshed- sits at about 4.5 GB of pbix.
However, the only issue now is the data that is flowing in on that is not of the last 90 days.
So ideally it should start with 05/14/2024 and up to 08/12/2024 and that's exactly how it is on the source as well as dataflow. But the moment i bring it into the report the data now starts from 05/01/2024 and ends at 07/08/2024.
I suspect it's because i kept the incremental refresha t 3 months and 1 day and should switch to 90 days and 1 day.
I can't think of any other reasons why the data is falling short.
There are no other filters applied on the report, dataflow has the auto appiled parameters for incremental refresh and the source has data for last 90 days.uncheck the "only complete months" box.
Not sure i follow. Can you please tell me the exact step?
Also when i look at the dataflow on query editor it loads the right amount of data.That looks ok. How many partitions do you see?
I don't know of a direct way to find taht but looking at the refresh history - it looks like it ran everyday for about 10-20 seconds with this being from the last run -
Do you think the data isn't even making it's way into the dataflow?
You have two partitions, but you should have at least 17 (14 for the days in August and 3 for the prior months)
Did you limit the source data in a way that you are not covering the cold partition range?
So, the source data itself that's on big query runs on a stored procedure on a daily basis that deletes the oldest day's data and adds new day's data into this table from a master table which has data for the last two years.
Upon querying the 90 day table it gives me distinct count of dates for last 90 days so it is functioning as intended.
Once on the dataflow the only other transformation steps i have in place are reducing the column count from 50 to 30 (remove columns) and change the data type on date column to date/time to help on the incremental refresh.The only time it ran longer than those avg of 15 seconds was on 2nd of Aug, prior to that it was the regular 15 seconds until 27th july and that's the last i can see.
Not sure if i mentioned before but the source data does run on a 2 day delay (including today), meaning today i only have data on that table till 08/12/2024
And since i kept the inc refresh at refresh rows from past 1 day since refresh date - it will not get any data because it just does not exist.But if you consume the dataflow you only see two days worth of data?
Not sure if i mentioned before but the source data does run on a 2 day delay (including today), meaning today i only have data on that table till 08/12/2024
And since i kept the inc refresh at refresh rows from past 1 day since refresh date - it will not get any data because it just does not exist?
As for consumption of data once loaded into PBI or even on power query to begin with i just see data from 05/01/2024 to 07/08/2024