HANA Advanced SQL statement being changed to NativeQuery. Unable to edit SQL directly

[russellya]

Hi All,

Half of our team are now on the September 2022 patch and the others are on August 2022.  Those of us on Sep 2022 can no longer directly edit Advanced SQL.  We are pushed to the advanced editor, which is not ideal.

Before patch: in applied steps the cog icon is avaiable

= SapHana.Database("pcci-serverurl.us:30015", [Query=

Any new queries since the patch.  applied steps the cog icon is no longer there.  The source is change to look like that below.

= Value.NativeQuery(SapHana.Database("pcci-serverurl.us:30015", [Implementation="2.0"])

Anyone know how we can stop this?  Or at least have the ability to edit the SQL query without all the formating that the advanced editor does.

For some reason all my seraches on this trend towards folding.  At this point we do all the optimisation directly in the SQL.  Moving these to power query logic is not someting I'd want to do right now.

Thanks 

Russell

[ron1999]

This is still happening in the Nov release. I had to go to the AUG version to make the reports work.

[braventooth]

Super crucial to get this fixed/reverted.
Some of us working in corporate environment don't exactly have an option to revert to the older version due to IT/Admin settings 🙂

[v-stephen-msft · Accepted solution]

Hi @russellya ,

This issue has been submitted internally.（Internal ICM ID: 339515871）

The workaround is removing the Value.NativeQuery() from the source M query and using the old way with SapHana.Database().

 Please be patient to wait for fixing. If there is any news, I will update it here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[braventooth]

Hey @v-stephen-msft .
Any updates on the resolution of this?

This 'fix' literally killed half of our reporting.

[russellya]

I was told the November patch would fix this but I've not seen anything yet.  I'll continue to use the August patch until I hear something.  If I do I'll post here.

[Daryl-Lynch-Bzy]

@russellya - I glad Microsoft are working on the bug.  

I can understand how this can be frustrating because it make that useful cog disappear.  However, I would like to suggest another approach that might make is easier to maintain the Hana Query.  I would suggest inserting an earlier Applied Step to hold the SQL script.  This would look like this:

let
   sql = "SELECT TOP 100 * FROM TABLE",
   Source = Value.NativeQuery( SapHana.Database( Server , [Implementation="2.0", Query= sql])
in
   Source

The sql step is visible in the Power Query screen, so you can simply paste in new strings into the formula bar. 

However, I like to go one step further.  I recommend pasting the sql string in the Advance Editor because it will eliminate the auto addition of #(lf) and #(tab).  Something like this:

let
   sql = 
"
SELECT TOP 100
 * 
FROM 
   TABLE
",
   Source = Value.NativeQuery( SapHana.Database( Server , [Implementation="2.0", Query= sql])
in
   Source

instead of 

let
   sql = "SELECT TOP 100 #(lf)#(tab)*#(lf)FROM#(lf)#(tab)TABLE",
   Source = Value.NativeQuery( SapHana.Database( Server , [Implementation="2.0", Query= sql])
in
   Source

[russellya]

Hi GaryOJ,

I finally got to talk to Microsoft and this "feature" introduced in the September 2022 patch.  The support guy gave me a link to the August Patched version and after I installed this the issue went away.

I assume they will realize this is a bug and fix it in a future patch.  The support guy said maybe in October but I'm not sure how he knows this.    

The work-around shown by TheCreepster1 works but you have to be careful about what you replace.  However I'd say unistall Power BI and then install the August Patch version.    

When they fix this I'll post the details here.

Regards

Russell

[GaryOJ]

Thanks for the update. I'm glad people are reaching out to Microsoft about this.

My scheduled refresh has been broken ever since this change happened as well. Has this happened to anyone else?

An error returns almost immediately when trying to refresh in the Power Bi Service. Stating "Native Queries aren't supported by this value" 

 

Anyone know why? I need my reports automated or it's a waste of money. Seems like everything has broken down since this update 😞

[russellya]

Hi GaryOJ,

This is the second symtom of the issue.  The soultion is to apply the fix TheCreeper1 mentioned to each query that was changed.  Then re-publish.  It's can be trciky but it works.  

2nd choice is to intall the pre-spetember patch version and rebuild the queires.  

I've been told Microsoft product team have an internal ticket to fix this but that may take a while.

Regards

Russell

[TheCreepster1]

Hi Russell,

I just managed to work around the problem by going to the advanced editor and reverting it back to the old format at the front and end of the query, so removed the Value.NativeQuery( from the start and , null, [EnableFolding=true]) from the end, and replaced with something like the below:

From

To

This then made the cog re-appear and enabled more simplistic editing of the SQL query.

[GaryOJ]

For some reason this option still does not work for me. It says it's returning values greater than expected 😞

[TheCreepster1]

Spoiler

Hi GaryOJ,

Yeah, sorry, think my snip is a little but wrong. I'll post the amended code tomorrow for you. 

[russellya]

This is now a ticket with Microsoft.  Verifired that switching back to August Patch fixes the issue.  Support persons stated that it might be fixed in the October 2022 patch.  I'll post if this is the case or not.

[russellya]

Thank you TheCreepster1 this work around does the trick.  Of course as soon as I save it reverts back but at least I can update code without having to navigate the advanced editor.   Still looking for a way to prevent power BI from converting my queries to nativequery.  

I'm escalating this issue to Microsft as it also seems to be impacting published reports but that's a different topic.  I can't believe this is how microsoft want this to function.  

Thanks again

Russell

[v-stephen-msft]

Hi @russellya ,

You could edit the code in Advanced Editor.

Query overview in Power BI Desktop - Power BI | Microsoft Docs

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TheCreepster1]

You could, but it's hideous. It goes from being able to simply adjust the SQL, to having to try and amend your SQL in Syntax like: 

with FISCAL as#(lf)#(lf)(#(lf)#(lf)select 'FISCAL' as VERSION,#(lf) dt.CALENDAR_YEAR as YEAR,#(lf) dt.CALENDAR_MONTH as MONTH,#(lf) RPRCTR as PROFIT_CENTRE,#(lf)

Which isn't particularly user friendly, especially given my 200 line code is now in 1 line in Power Query...

Keeping an eye on this thread in case someone else has another option.

[GaryOJ]

I'm running into the same issue. What used to be an easy sql change to grab different columns, or filter data, has become a nightmare. 

why would they change this? 

[GaryOJ]

Same here. This has basically haunted my work in two different reports. It's terrible.

[russellya]

I''ve done this before but my team are still learning SQL.  This would breask them.  Hoping microsoft will admit they messed up and fix their patch.