Using SQL option force order in BI desktop

[Anonymous]

All, 

I am trying to create a dataset in BI Desktop using a view that summarizes other views and when I run it in SSMS or BI I get his error.

DataSource.Error: Microsoft SQL: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

In SSMS I can run the select statement from the view using OPTION (force order) and I can get it to run succsefully, but my view wont accept that syntax so I figured I could call it in BI advanced editor like this:

Select * from myView OPTION( Force Order) but BI complains that there is a syntax error on the word option.  

I tried wrapping this in a Stored Procedure and a function but get the same syntax error. 

I even tried just taking the select statement from my view and using the option at the end of the statement and BI doesnt like that either. 

Is there a way to use OPTIONS/Hints in BI desktop? I have been searching for days and cant come up with an answer.

Thanks in advance.

[az38]

Hi @Anonymous 

as I understand the only hint available for power bi is MAXDOP

have a look here

https://blog.crossjoin.co.uk/2014/09/29/advanced-options-for-loading-data-from-sql-server-with-power-query/

[Anonymous]

The root error I am having trouble with is:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions...

I have a view selecting from many others summarizing all kinds of financial data.

I was able to get the view to work in ssms using the option force order but cant get that to work in BI.

Any other info would be great if anyone knows but it appears that a lot of the options arent available in BI

Still talking to support but havent got anywhere yet with them.

[parry2k]

@Anonymous interesting, I have to test it to see why it is not working. Maybe you want to open a support ticket on the support forum.