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.
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.
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