Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi.
Currently I've created a machine learning experiment and deployed, and want to use dataset in Power BI as input, and consume the data from Azure Machine Learning. Currently using the new Azure Machine Learning Preview. I have created a real.time Endpoint, got the URL and keys, also Python script. I can test and get data from Azure ML.
Now i want to use data in a dataset as input, and get a new column with the result from the ML.
In Power BI query, i select Pythn Script, and paste the script from Azure Machine Learning. It seems to connect, but guess i need an output section in the script. I'm not an experienced python programmer, but have programmed different solutions.
I hope someone could give me the push to consume the Azure ML service.
Solved! Go to Solution.
This post started with how to code Python to consume Azure ML predictive model. I ended up with scripting in "R", as i found good examples.
1: Have a Azure ML predictive model
2: Want to use Power BI to consume and get the prediction from azure ML (web service)
3: Add the prediction to the original dataset in Power BI.
1. Added datasource in Power BI.
2. Edit Queries
3. Transform, and choose "Run R- script"
Added this code (dataset is Power BI data, you get it directly):
# 'dataset' holds the input data for this script
library("RCurl")
library("rjson")
library("bitops")
# Accept SSL certificates issued by public Certificate Authorities
options(RCurlOptions = list(cainfo = system.file("CurlSSL", "cacert.pem", package = "RCurl")))
dataset <- dataset[complete.cases(dataset), ] ##remove NaN
h = basicTextGatherer()
hdr = basicHeaderGatherer()
req = list(
Inputs = list(
input1 = setNames(lapply(split(dataset, seq(nrow(dataset))), FUN = as.list), NULL)
),
GlobalParameters = setNames(fromJSON('{}'), character(0))
)
body = enc2utf8(toJSON(req))
api_key = "<replace key>" # Replace this with the API key for the web service
authz_hdr = paste('Bearer', api_key, sep=' ')
h$reset()
curlPerform(url = "<replace url>", # Replace this with the URL for the web service
httpheader=c('Content-Type' = "application/json", 'Authorization' = authz_hdr),
postfields=body,
writefunction = h$update,
headerfunction = hdr$update,
verbose = TRUE
)
headers = hdr$value()
httpStatus = headers["status"]
if (httpStatus >= 400){
result.error <- data.frame("Error")
}else{
result.json = h$value()
result.list = fromJSON(result.json)$Results$output1
result.list.final <- lapply(result.list, FUN = function(x) lapply(x, FUN = function(y) if( is.null(y) ){y <- NA}else{y <- y}))
result.df <- data.frame(do.call(rbind.data.frame, result.list.final))
result.df = merge(dataset,result.df,by.x="row_id",by.y="row_id") #to combine original dataset with prediction
}
Hi @Anonymous ,
maybe that's an easier solution.
https://docs.microsoft.com/en-us/power-bi/desktop-ai-insights#using-azure-ml
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hi, and thanks for answering my question. Yes I have tried your solution, and it did not work. Don't know if it is a license issue or problem with my Azure ML (Tried both Azure Classic and new Azure ML Preview). It is similar to this solution: https://docs.microsoft.com/en-us/power-bi/service-machine-learning-integration and this method requires Premium license, i've got Power BI Pro license. Was able to connect Excel to the classic model, so the ML model should work. Also found this method, and have hopes that this could work with Pro license: https://powerbi.microsoft.com/de-de/blog/power-bi-azure-ml/ This is a R script, but guess it could work without writing to SQL also, just use the dataset.
Hi @Anonymous,
the Azure ML integration should not require premium.
Look at this video 8:30
https://www.youtube.com/watch?v=FsK8OYagidY
Hi. Thanks again.
I see the comments on the video, alot of them says you need Premium. Is that not correct?
Hi @Anonymous ,
if I understand Sarina Stevens (Program Manager) correctly, Power BI Premium is needed only for the Cognitive services functions (Text Analytics & Vision).
But not for your custom model from Azure ML.
https://powerbi.microsoft.com/de-de/blog/easy-access-to-ai-in-power-bi-preview/
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hi @Anonymous
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hi again, and thanks for helping me.
Sorry, busy building Power BI and also having exam. I'm still not able to connect to Machine Learning from my PRO license on Power Bi desktop.
I get this message. It would be useful if someone can confirm and have connected to Azure ML with PRO license. It might be that I've not configured Azure ML correct.
But a killer function if it works with PRO license.
Hi @Anonymous,
has your question been answered?
Did you solve your problem?
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
This post started with how to code Python to consume Azure ML predictive model. I ended up with scripting in "R", as i found good examples.
1: Have a Azure ML predictive model
2: Want to use Power BI to consume and get the prediction from azure ML (web service)
3: Add the prediction to the original dataset in Power BI.
1. Added datasource in Power BI.
2. Edit Queries
3. Transform, and choose "Run R- script"
Added this code (dataset is Power BI data, you get it directly):
# 'dataset' holds the input data for this script
library("RCurl")
library("rjson")
library("bitops")
# Accept SSL certificates issued by public Certificate Authorities
options(RCurlOptions = list(cainfo = system.file("CurlSSL", "cacert.pem", package = "RCurl")))
dataset <- dataset[complete.cases(dataset), ] ##remove NaN
h = basicTextGatherer()
hdr = basicHeaderGatherer()
req = list(
Inputs = list(
input1 = setNames(lapply(split(dataset, seq(nrow(dataset))), FUN = as.list), NULL)
),
GlobalParameters = setNames(fromJSON('{}'), character(0))
)
body = enc2utf8(toJSON(req))
api_key = "<replace key>" # Replace this with the API key for the web service
authz_hdr = paste('Bearer', api_key, sep=' ')
h$reset()
curlPerform(url = "<replace url>", # Replace this with the URL for the web service
httpheader=c('Content-Type' = "application/json", 'Authorization' = authz_hdr),
postfields=body,
writefunction = h$update,
headerfunction = hdr$update,
verbose = TRUE
)
headers = hdr$value()
httpStatus = headers["status"]
if (httpStatus >= 400){
result.error <- data.frame("Error")
}else{
result.json = h$value()
result.list = fromJSON(result.json)$Results$output1
result.list.final <- lapply(result.list, FUN = function(x) lapply(x, FUN = function(y) if( is.null(y) ){y <- NA}else{y <- y}))
result.df <- data.frame(do.call(rbind.data.frame, result.list.final))
result.df = merge(dataset,result.df,by.x="row_id",by.y="row_id") #to combine original dataset with prediction
}
Hi @Anonymous ,
I think I get it ...
You need a "Schema discovery for Machine Learning models"
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |