Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more

Reply
Caroline_1900
Frequent Visitor

Model Size

Hello World, Newbie User here.  I need help with a big dataset.  

Getting data from the DataVerse.

After removing the PowerBi plumbing and unwanted columns, the table has 101 columns and 508,628 rows.

 

When trying to load this big table into PowerBi, I get the error:

OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Return records size cannot exceed 83886080. Make sure to filter result set to tailor it to your report. RequestId: TDS;e3727b21-0350-40fd-8508-19e1f35522cc;2 Time: 2021-03-26T12:55:48.2431825Z.

 

If I restrict the number of columns to be loaded into Power Bi to less than or equal to 20 columns, the table loads successfully.  If I request 21+ columns, the error returns.  It doesn’t seem to matter which 21+ columns are selected to load.

 

Is this normal? Is there a way to laod more than 21+ columns with so many rows? 

Can you help?

Thanks World!!

 

1 ACCEPTED SOLUTION
MisterFry
Resolver III
Resolver III

The ODBC enpoint to Dataverse has an 80 Meg limit on the data it can return. 

 

You either need to chop up your query into smaller queries, or use another method to export the data. 

https://docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query

 

View solution in original post

6 REPLIES 6
MisterFry
Resolver III
Resolver III

Hi @Krishna09 
There is a section in the documentation that I linked above called 'limitations', it makes reference to other options for interacting with dataverse data. I haven't done any of these things before, so I don't have definitive answers.  

Anonymous
Not applicable

DataSource.Error : Microsoft SQL : Return records size cannot exceed 83886080. Make sure to filter result set to tailor it to your report.
RequestId: TDS;c3c748a8-fbbb-4e96-b53c-f4101e4f7fe4;2
Time: 2021-11-18T15:50:41.3471257Z
Détails :
DataSourceKind=CommonDataService
DataSourcePath=itesoft.crm4.dynamics.com
Message=Return records size cannot exceed 83886080. Make sure to filter result set to tailor it to your report.
RequestId: TDS;c3c748a8-fbbb-4e96-b53c-f4101e4f7fe4;2
Time: 2021-11-18T15:50:41.3471257Z

TimoRiikonen
Helper V
Helper V

We got the same error for Import.
Max size for the older OData protocol is higher as it didn't complain about the size.

Caroline_1900
Frequent Visitor

Thanks Mister Fry!  I understand now.The file was indeed much larger than 80MB.

The problem lies with the documentation of the error message: "Return records size cannot exceed 83886080" led me to believe the error had something to do with the total number of records, not the total file size .

So much to learn, so little time.

Thank you again for your knowledge and experience.

 

MisterFry
Resolver III
Resolver III

The ODBC enpoint to Dataverse has an 80 Meg limit on the data it can return. 

 

You either need to chop up your query into smaller queries, or use another method to export the data. 

https://docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query

 

@MisterFry 

EvenI had similar error in power query while merging two tables. I am creating a dataflow, I have one table in dataverse and other is excel file imported. Table in dataverse have a huge data, Excel have only 15 records. How can I shorten my query?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors