November 26, 2014 Leave a comment
While drafting some samples & labs for this saturday SQLPort AzureML workshop decided to dig a little bit trying to use some regular AzureML scoring web services (request/response) directly in Excel, using Power Query. :)
So… can it be done? Sure! Advisable? Far,far from it…. but for a quick sample/test scenario it might be handy. :)
Saying again, what I’ll show here is far from a recommended web service usage for AzureML in Excel. It will be slow, probably insecure if you need to distribute your file, and there will be I’m sure better ways to do it with the batch web service mode, soon I hope (without the need to pass through the Azure marketplace),
- if you want to use Azure Data Marketplace APIs (odata) please check out the cool sample by Chris Webb: Sentiment Analysis In Excel With Azure Machine Learning And Power Query
- If you want to use your AzureML web services in Excel you can also check this new AzureML codeplex addin
- Checked this thread also that put me on the right track (see the final note by Curt Hagenlocher)
How it works
- To the point, you can use the Power Query Web.Contents function to invoke the AzureML web service api. As long as you are careful on: 1) using post and not get, 2)setting the authorization header expected by azureml, 3)setting the content type to json 4) building the json request like shown in the api help page.
- PowerQuery will handle the json response automatically..We encapsulate the web request in a function, we can then use it to build our scored table passing the score input column values and getting the score in return.
Step by Step-From AzureML to Excel Power Query Scoring
You’ll need an AzureML scoring web service, I used a simple two class log regression model to classify the well known Fisher Iris Dataset (with only two classes, as the original as three):
The AzureML Experiment & Web Service Experiment, can’t get much simpler than this.
Web Service Experiment
Publishing the trained model as a Web Service
this JSON request will be needed for Power Query, same thing as the service key:
In Power Query you’ll then create a function like this (text version below):
Invoking the Power Query scoring function
Then you just import the original table to Power Query and add a custom column invoking the new scoring function
Note: to test this it is advisable to filter the top 1/2 rows first, it is not lightning fast….
The result should now be visible, a little bit more Power Query expanding and renaming and that’s it!
To check that I’m getting the expected score, just used another small tool (more on this later)
Power Query results
Scoring the whole 100 rows iris dataset
The problem using the regular Power Query web.contents function is that you will be receiving api usage threshold errors from the AzureML API very, very soon. So using another trick inspired (again) on this post by Chris Webb (amazing tip by Alejandro Lopez-Lago), to add some delay between requests.
Just remove the top filter and refresh the query. Wait between 30 secs-60 secs (this will depend on the kind of endpoint you are using, AzureML is changing this whole “stag/production web service model “ right now from what I’m seeing)
note: think the latest Power Query update now shows the row count progress, neat! perfect for this
Some misses there guess the model needs some additional training!
Score= (sepal_length as any,sepal_width,petal_length,petal_width) =>
serviceUri="*yourserviceuri_note:ends with score*",
//Define Wait function
Wait = (seconds as number, action as function) =>
() => DateTimeZone.LocalNow() + #duration(0,0,0,seconds),
(x) => DateTimeZone.LocalNow() < x,
(x) => x)
) = 0)
then null else action()
//Call Wait function to wait 5 seconds
//then return the current time again
Source=Wait ( 0.05, () => Web.Contents(serviceUri,
Headers=[Authorization="Bearer "&serviceKey,#"Content-Type"="application/json; charset=utf-8"]])),
#"Imported JSON" = Json.Document(Source),
#"Table from List" = Table.FromList(#"Imported JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Transposed Table" = Table.Transpose(#"Table from List")