Hello! I am running into the same issues. I do not think the response from Karpurapu D is sufficient and this problem is Not Resolved.
Project Technical Context: In the environment I am working with, we are building a data platform in Fabric Lakehouse, and pulling in data from D365 leveraging Synapse Link for Dataverse. In Fabric Lakehouse, the raw tables from D365 are landing in the schema "dbo" and we are leveraging EntityUtil to create database views, in the schema "bronze_d365_views". We are building in a "greenfield" environment, and we are leveraging the latest Microsoft technologies.
Example of entities / database views that are failing due to the same "query processor error": dimensioncombinationentity and dimensionsetentity. Both of these entities fail on the same column displayvalue. There may be more, but these are critical ones because they are the "base" views that many other entities leverage. These are causing a great many of important entities to fail - some examples of critical entities that is causing failures for: VendVendorV2Entity, SalesOrderLineV2Entity, CustCustomerDetailV2Entity, LedgerJournalLineEntity, EcoResProductV2Entity, etc.
We encountered another entity / database view that has a similar error: financialdimensionvalueentityexistinglistview. This one results in the error "The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes. A typical reason why a worktable is required is a GROUP BY or ORDER BY clause in the query." Pulling the query open, we see some order by statements that look to be necessary for grabbing the right data (there's a subselect top 1, after grabbing rows, then ordering them to grab the correct single row). So what is the solution - break the vetted, accurate logic?
The solution "If you prefer not to rewrite your queries, you might consider modularizing them into smaller, materialized steps using intermediate staging tables." seems to be missing the point.
There needs to be validated, accurate, and turnkey integration of Entities from D365 to Fabric - and having users modify Microsoft-approved Entity business logic introduces significant risk in getting the logic wrong and/or miscalculating important fields as part of the modification process. Because of the "query processor error", users canโt run the views, users donโt know what the data is supposed to look like for testing purposes as users attempt to fix and break down the EntityUtil-generated logic into parts. Creating Spark or PySpark code / notebooks also requires editing the business logic provided out of D365, with all the same risks.
There needs to be a solution for people building in a greenfield context, in a modern Fabric Lakehouse / Warehouse context, that does not involve manually modifying the database views / business logic that comes from D365 / EntityUtil.
