Has the ability to refresh a dataset with a dynamic SQL stored procedure been disabled?

[Anonymous]

Up until today, I had a dataset that was able to be refreshed perfectly fine through the Service.  This particular query has a single database as a source, which is an Azure Database.  The dataset uses 4 queries to get data from the database.  2 which are views, and 2 which use Stored Procedures that take in a few parameters.

In the case of the Stored Prodedure, the parameters require a filter string and a Start Date/End Date for the returned rows.  The dates change every day as its up until current.

Here is what my code calling one of the stored procedures looks like in Power Query:

Source = Sql.Database("<MyServerName>.database.windows.net", "<MyDataBase>", [Query="Exec Example.[sp_ExampleStoredProcedure] 0, " & fnStartDate & ", " & fnEndDate & ", 'EP231%'", HierarchicalNavigation=true, CommandTimeout=#duration(0, 0, 90, 0)]),

(i've changed some names for privacy reasons)

The functions that make the parameters dynamic are:

let
    Result = "'" & DateTime.ToText(Date.AddYears(DateTime.LocalNow(), -2), "yyyy") & "-01-01'"
in
    Result

and

let
    Result = "'" & DateTime.ToText(DateTime.LocalNow(), "yyyy-MM-dd") & "'"
in
    Result

When i check the service for this dataset, i no longer get the option to refresh the dataset, or even set "Connect directly".  When i look at the refresh history, you can see where it previously worked and then it starts to fail today.  If i remove the Stored Procedures from the dataset, i'm able to select "Connect Directly" again.  I only found this coincedently when i went to make minor updates this morning to a report.

The error now stating:

Data source error:
Unable to refresh the model (id=1767871) because it references an unsupported data source.
Cluster URI: WABI-SOUTH-EAST-ASIA-redirect.analysis.windows.net
Activity ID: 725ea1be-d2db-4f55-ace8-9a91ccc4b307
Request ID: af57b3a0-5ce2-437a-aba8-2f8e6e08e1b8
Time: 2018-04-09 00:25:48Z

[Anonymous]

Further testing has shown that if i change the parameters to a literal, rather than a function, I'm able to use "Connect Directly" and do further refreshes.

Example as:

", "<MyDataBase>", [Query="Exec Example.[sp_ExampleStoredProcedure] 0, '2016-01-01', '2020-01-01', 'EP231%'", HierarchicalNavigation=true, CommandTimeout=#duration(0, 0, 90, 0)]),

[GilbertQ]

Hi Ross, could it possibly be that the functions are not returning it as the right data type?

[Anonymous]

@GilbertQ  its a good suggestion, however refreshing through the desktop produces no issue.  The code is also unchanged since I created it early last month.

The only difference between today where it didn't work, and previous weeks where it had, was some simple changes to the front end reports.  I've not actually made any backend changes.

I've taken some code out into isolation and uploaded a dataset with just a single table, the ability to select a refresh in the service is still no longer there.

Something to note:  Whenever a refresh is required through the desktop, each new day does require a new set of authorized permissions.  This has always been the case, however it never caused any problems with the online side of this.  Part of me wonders if there is a security hole that has been patched?

[GilbertQ]

Hi @Anonymous

I think you are possibly right in that something has changed in the underlying Power BI Service and now this is affecting your refreshing of the data from the Azure SQL Database.

[Anonymous]

Interestingly,  If i change my functions fnStartDate & fnEndDate to return a literal, the problem also goes away.  It is only a problem if i use the dynamic portion of the function.

So the problem goes away if I use:

let
    Result = "'2020-01-01'"
in
    Result

instead of:

let
    Result = "'" & DateTime.ToText(DateTime.LocalNow(), "yyyy-MM-dd") & "'"
in
    Result

I can only therefore conclude that something in the service is now actively looking for, and blocking, a truly dynamic query.  I was wondering if the functions themselves were enough to cause the problem.  It seems whatever has been put in place is more complex and quite intentional.

Sadness 😞

[GilbertQ]

Hi there @Anonymous

Are you able to run Profiler on the source database?

The reason that I ask is possibly your initial result is text based, and even though you are specifying the DateTime.ToText I wonder if that is actually happening.

What happens if you change it from the original to

let
    Result = "'" & Text.From(Date.From(DateTime.LocalNow(), "yyyy-MM-dd")) & "'"
in
    Result

To ensure that it is only passing a Date and not DateTime?

[Anonymous]

Really quite a good suggestion @GilbertQ, although it didn't pan out.  Same result.

Can you give me some info on the profiler for the source database you are talking about?  Azure is still a little new to me, I haven't been given much access to know what tools are available.  I can get the Azure owner in our organisation to run it for me.

[GilbertQ]

Hi @Anonymous

Pity I was hoping that might be the work around.

SQL Profiler basically returns everything happening on the database. So you are able to see firstly if the query is getting to the database. As well if it is, what exactly it is sending to SQL Server.

If you have a DBA and explain that you want a trace from Power BI, and co-ordinate a time I am fairly certain they will be able to give you the results.

[Anonymous]

Thanks @GilbertQ.  I'll get that organised.  Any luck it will turn up some new detail.  My gut instinct is still that MS have changed something on us, but it is always prudant to get all of the data together.

[GilbertQ]

I totally agree with you that possibly something has changed.

But also good just to double check everything is working from your side.

[Anonymous]

Had a response from the ticket acknowledging this issue and stating:

"I got information that the fix will be available on prod by 4/23/2018.
 
Best Regards,
Qiuyun Yu "

[Anonymous · Accepted solution]

Microsoft have solved this on the backend and it is working again.