Forum Discussion

Sam_Jain's avatar
Sam_Jain
Icon for Helper III rankHelper III
2 years ago
Solved

Power BI Dataflow - response too large to return

Hello Community,   I have run into a PowerBI dataflow issue. My source table is in Big Query and is a table that gets updated daily and has data for the last 90 days in a rolling fashion.   What ...
Dataflow
Need Help
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    2 years ago
    Can't use direct connection to BQ for access reasons

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

  • lbendlin's avatar
    lbendlin
    2 years ago

    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.

parry2k's avatar
parry2k
Icon for Super User rankSuper User
2 years ago

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.

Sam_Jain's avatar
Sam_Jain
Icon for Helper III rankHelper III
2 years ago

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