After updating my on-premesis data gateway one of my scheduled report refreshes is failing with a SQL Server error "An expression services limit has been reached". That error typically indicates a SQL query is too complex. The report refresh has been working without a problem for 18 months and immediately after applying the Oct. 2020 gateway update the error started to appear. My previous gateway version was quite old (June 2019).
The report connects to SQL Server 2012. The query (View0) is shown below . The 3 underlying queries (View1, 2 & 3) are simple, they query another view (View4) which in turn each query another view (View5). View5 does a union query on 2 other views (View6 & 7). View6 is simple with only 1 table and a few filters. View7 is complex, it queries 15 tables with multiple filters and case statements, some of which contain sub queries.
So, why would this suddenly cease working when the only thing that changed was the gateway version? Microsoft recommend simplyfing the query but View0 works fine in SQL Management Studio - it is only PowerBI that can't complete the task.
View0
SELECT ROW_NUMBER() OVER (ORDER BY MONTHS_ON_LIST DESC) AS PLACE_ON_LIST, * FROM View1 UNION
SELECT (ROW_NUMBER() OVER (ORDER BY MONTHS_ON_LIST DESC, PRIORITY ASC)) + 50000 AS PLACE_ON_LIST, * FROM View2 UNION
SELECT (ROW_NUMBER() OVER (ORDER BY PRIORITY ASC, MONTHS_ON_LIST DESC)) + 90000 AS PLACE_ON_LIST, * FROM View3
