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.
Sam_Jain Sorry very hard to tell what is going on. What error message do you get in gen2? I'm wondering if it is a transient error or if it is failing at the BQ end.
Yea, so here's what the error reads - apologies i copy pasted the entire thing
Mashup Exception Data Source Error Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: DataSource.Error: Error in replacing table's content with new data in a version: #{0}., InnerException: #{0}: #{1}, Underlying error: ODBC: ERROR [HY000] [Microsoft][BigQuery] (100) HTTP Error 403: responseTooLarge (Response too large to return. Consider specifying a destination table in your job configuration. For more details, see https://cloud.google.com/bigquery/troubleshooting-errors). Details: Reason = DataSource.Error;Message = ODBC: ERROR [HY000] [Microsoft][BigQuery] (100) HTTP Error 403: responseTooLarge (Response too large to return. Consider specifying a destination table in your job configuration. For more details, see https://cloud.google.com/bigquery/troubleshooting-errors).;Detail = [DataSourceKind = "GoogleBigQuery", DataSourcePath = "GoogleBigQuery", OdbcErrors = error "Microsoft.Mashup.Engine1.Runtime.ValueException: [Expression.Error] Value was not specified.#(cr)#(lf) at Microsoft.Mashup.Engine1.Language.ValueCreator.CreateValueForThrow(IThrowExpression throwExpr)#(cr)#(lf) at Microsoft.Mashup.Engine1.Language.ValueCreator.<>c__DisplayClass23_0.<CreateValueForRecord>b__0(Int32 index)#(cr)#(lf) at Microsoft.Mashup.Engine1.Runtime.RecordValue.DemandRecordValue.get_Item(Int32 index)#(cr)#(lf) at Microsoft.Data.Mashup.ProviderCommon.MashupResource.TryGetValue(Func`1 getValue, IValue& value, String& errorMessage)#(cr)#(lf)Record"];Message.Format = #{0}: #{1};Message.Parameters = {"ODBC", "ERROR [HY000] [Microsoft][BigQuery] (100) HTTP Error 403: responseTooLarge (Response too large to return. Consider specifying a destination table in your job configuration. For more details, see https://cloud.google.com/bigquery/troubleshooting-errors)."};Microsoft.Data.Mashup.Error.Context = User
Alternatively, please let me know if there is any other approach or method i can use to accomplish the task.
ThanksCan'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.
Yea, so the 650 GB is for the entire dataset on BQ, which is roughly about a year's worth of data and the proposed solution is to have two datasets one rolled up monthly, which i was able to succesfully create and provide a dataflow access. The second one was a 90 day rolling window but at daily level and i was able to create a stored procedure on BQ that deletes the oldest day's data and add's new day's data from source table , had that schduled as a daily run to comply the 90 day rolling window.
Challenge started when i created a dataflow off of it where the data size on bq is about 200 GB, which kept failing on me so i cut the columns into half to 30 from 50 odd. Still didn't work.
So, i then filtered by date on power query editor - that worked! But now it only has 30 days of data and am not sure how i proceed from here?
Also the dataset on BQ is partitioned by day for effeciency.
From what you are suggesting, it looks like i have done the first bit getting data for 30 days and now i should use incremental refresh for every 15-30 days until i have all 90? Is that the right approach?
Thanks.Use the biggest partition size you can get away with. Your choices are Day,Month,Quarter and Year.
I would start with Day and check how many rows you get per partition. If not too many you can switch to Month.
