I'm using Power Query in Power BI to retrieve tables from Dynamics 365 using the Dataverse connector in Import mode. I'm currently having a problem where I have a table that has 23 columns and 6M+ rows, and a lot of the keys are alphanumeric GUIDS (which I intend to replace with numeric keys). So far I have retrieved a base table from D365 (TableBase), which I referenced to another table (Table0) to do some basic transformation like changing text types to integers, replacing nulls with 0, and changing column labels. My next step would be to create 3 smaller fact tables referencing Table0 (Table1, Table2, Table 3) by filtering on a category code for each table.
I have two problems occurring:
- When I create a dimension table and left join to the fact table on the GUID, then expand the table to only include the numeric key I created in the dim table, it gives an error saying the file size is too large.
- I tried filtering Table1 to return a decreased number of rows (the current table has 6,213,553 rows, I want to get it down to 567,458) it also gives an error saying the file size is too large.
The error message I get is this: Microsoft SQL: Return records size cannot exceed 83886080. Make sure to filter result set to tailor it to your report.
Of course my alternative is to use the OData connector instead of the Dataverse connector for Dynamics 365, but it's dreadfully slow and one simple model I have build with the OData connector is not even refreshing anymore due to the refresh taking so long. Also, I'm confused as to why filtering a table to have fewer rows throws this error, as I'm trying to reduce the file size.
Do you all have any suggestions on why this is happening, and how I can get around it? I know the most obvious answer is that I should remove some columns from the fact table, and I'm going to have a talk with one of my consumers about doing this because that is the only solution I can figure out at this point.
I appreciate any help you can provide!