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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Advocate III
Advocate III

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors