Azure ML odata & Refresh

[konstantinos]

I used PowerBI Desktop to score a model from AzureML..

I am using 2 data sources Azure SQL Database and OData from AzureML.. This link describes it the procedure to score from AzureML

https://support.office.com/en-au/article/Use-Power-Query-to-import-Azure-ML-score-data-into-Excel-65...

I create a function to score the data from Azure SQL database with the OData from AzureML. Everything is working and refreshing in PowerBI Desktop. Store the file in OneDrive and upload it to Service. Entered the credentials for the sources - no warnings. I Refresh Now,  the refresh succeeds but I have no data coming from AzureML OData..The scoring table (function) is empty. So I have no warning to know what is going wrong.

Is not supported due to credentials entered ( AzureML Odata - Basic with API key ) ?

Did anyone tried it?

Thanks

[Greg_Deckler]

Have not tried it, but thanks for the link and idea, now I've got to try it. I have a bunch of experiments I've been doing in Azure ML so this has given me all kinds of ideas! I'll try it and let you know.

[konstantinos]

Glad I help...I am expecting your results..

FYI the functions are not a problem as they are refreshing .. ( tested )

[curth]

I know of no specific reason why this wouldn't work. Can you post your query or send it to me in a private message?

[konstantinos]

Thanks @curth for quick responce , sorry for the my late reply but was off for weekend..

This is the formula Query ( I just replace the AzureML workspaces codes with xxx ). I rename the function query to "GetScore". 

let
    Source = OData.Feed("https://ussouthcentral.services.azureml.net/odata/workspaces/0xxxxxxxxxxxxxxxxxxxxx/services/xxxxxxxxxxxx"),
    #"Score_function (Country as text, Department as text, Source as text, SourceDetails as text) as record" = Source{[Name="Score",Signature="function (Country as text, Department as text, Source as text, SourceDetails as text) as record"]}[Data]
in
    #"Score_function (Country as text, Department as text, Source as text, SourceDetails as text) as record"

The actual query reads from an Azure SQL database: (also replace the names of database & Server). The columns are only the ones as input to function plus an ID column.

let
Source = Sql.Database("ServerName.database.windows.net", "DatabaseName"),
dbo_PredictPerf = Source{[Schema="dbo",Item="PredictPerf"]}[Data],
#"Removed Columns" = Table.RemoveColumns(dbo_PredictPerf,{"Performance Rating", "Class"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{ {"Country", type text}, {"Department", type text}, {"Source", type text}, {"SourceDetails", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each GetScore([Country],[Department],[Source],[SourceDetails])),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"ScoredLabels", "ScoredProbabilities"}, {"ScoredLabels", "ScoredProbabilities"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"ScoredProbabilities", type number}})
in
#"Changed Type1"

[curth · Accepted solution]

Okay, I guess I was wrong when I said that I couldn't think of a reason this wouldn't work.

When you run this inside Power Query or PBI Desktop, you either have to enable "Fast Combine" or you need to set privacy levels for the two sources. Currently, all sources inside refresh are marked as "Private" with no ability to change this configuration or set "Fast Combine". This prevents the query from working correctly. This was picked as a default to maximize security without asking the user hard-to-explain questions, but it's obviously quite limiting in the kinds of queries that can be performed -- so we need to figure out how best to enable this scenario for refresh.

[konstantinos]

@curth Any news on refreshing oData from Azure ML web services? Is there a workaround with the new November "Fast Combine: personal gateway update?

Still cannot refresh Azure ML web services in PowerBI athough both PBI desktop and Personal gateway is in Fast Combine ( probably due to AzureML is a web source and don't get through gateway?)

Thanks

[curth]

Fast Combine can currently be enabled only with the Personal Gateway. To use it in this case, you'd need to introduce an artificial reason in your query for it to be run locally instead of in the cloud.

[konstantinos]

Thanks @curth, although I am not sure what you meant by  "introduce an artificial reason in your query for it to be run locally", at least I know that is not possible yet to refresh with the azure odata web service.