Joyce Posted November 29, 2017 Posted November 29, 2017 Hi, Try to connect to a Report from Power BI R Script as follows: #PowerBIDataSource_Report.R #Define Instance Details instanceName = "vinci" instanceZone = "eur" # Define API Key apiKey = "<removed for security purposes>" # Define Report details reportID = "71" reportComment = "A comment to add to the report run" # Import dependencies library('RCurl') library('XML') # Build XMLMC URL arrUrl = c("https://", instanceZone, "api.hornbill.com/", instanceName) xmlmcURL = paste(arrUrl, collapse="") # invokeXmlmc - take params, fire off XMLMC call invokeXmlmc = function(url, key, service, xmethod, params) { # Build Methodcall paramsrequest = paste(params , collapse="") arrRequest = c( "<methodCall service=\"", service, "\" method=\"", xmethod, "\">", paramsrequest, "</methodCall>") request = paste(arrRequest, collapse="") # Build Invoke URL invokeURL = paste(url, "/xmlmc/", service, "/?method=", xmethod, sep="") # Build Headers espKeyAuth = paste('ESP-APIKEY ', key, sep="") requestHeaders = c('Content-Type'='text/xmlmc', 'Cache-control'='no-cache', 'Accept'='text/xml', 'Authorization'=espKeyAuth) data = getURL( url = invokeURL, postfields=request, httpheader=requestHeaders, verbose=TRUE ,ssl.verifypeer = FALSE) return(data) } # suspendExec - wait for a given number of seconds suspendExec = function(susSec) { p1 = proc.time() Sys.sleep(susSec) proc.time() - p1 # The cpu usage should be negligible } ### Kick off report run, get job ID # Build XMLMC Request arrXmlmcParams = c( "<params>", "<reportId>", reportID, "</reportId>", "<comment>", reportComment, "</comment>", "</params>") reportRunResponse = invokeXmlmc(xmlmcURL, apiKey, "reporting", "reportRun", arrXmlmcParams) xmltext = xmlTreeParse(reportRunResponse, asText = TRUE,useInternalNodes=T) runID = unlist(xpathApply(xmltext,'//methodCallResult/params/runId',xmlValue)) reportSuccess = FALSE if(runID > 0){ reportComplete = FALSE while(reportComplete == FALSE){ # Wait a second... suspendExec(1) # Check status of report arrXmlmcRequest = c( "<params>", "<runId>", runID, "</runId>", "</params>") xml.request = paste(arrXmlmcRequest , collapse="") reportRunStatus = invokeXmlmc(xmlmcURL, apiKey, "reporting", "reportRunGetStatus", xml.request) xmlRunStatus = xmlTreeParse(reportRunStatus, asText = TRUE,useInternalNodes=T) runStatus = unlist(xpathApply(xmlRunStatus,'//methodCallResult/params/reportRun/status',xmlValue)) if ( runStatus == "completed" ){ reportCSVLink = unlist(xpathApply(xmlRunStatus,'//methodCallResult/params/reportRun/csvLink',xmlValue)) reportSuccess = TRUE reportComplete = TRUE } else if ( runStatus == "failed" ){ reportSuccess = FALSE reportComplete = TRUE } } } if(reportSuccess == TRUE) { # Now go get CSV # Build Invoke URL invokeURL = paste(xmlmcURL, "/dav/reports/", reportID, "/", reportCSVLink, sep="") # Build Headers espKeyAuth = paste('ESP-APIKEY ', apiKey, sep="") requestHeaders = c('Content-Type'='text/xmlmc', 'Authorization'=espKeyAuth) # GET request for report CSV content reportContent = getURL( url = invokeURL, httpheader=requestHeaders,ssl.verifypeer = FALSE) ## CSV vector in to data frame object dataframe <- read.csv(textConnection(reportContent)) } The connection takes too long, then timeout. My report is less than 1MB so not very big, and it seems to run, but struggle to download. Help please. Thanks. Regards, Joyce
Victor Posted November 29, 2017 Posted November 29, 2017 Something that @Steve G might be able to assist with?
Steve G Posted November 29, 2017 Posted November 29, 2017 @m.vandun @Joyce @Victor, The bad news - this is actually due to a defect (regression) in Power BI, in that these data source scripts no longer work for larger reports when they used to run fine. The actual problem is when we call RCurl::getURL within a loop in the R script. Once the report has been started, we enter a while-loop, waiting 1 second between loops before running the reportRunGetStatus API to check to see if the report has completed. After the first loop, if the report is not at a completed status as yet, then we loop through to get the report status after waiting another second. At this second run of getURL, Power BI causes getURL to hang indefinitely, until either Power BI times out (30 minutes) or you click the Cancel button. Once you've clicked cancel or the timeout has occurred, the R engine does actually continue to run the script in the background, and gets the data from Hornbill, but Power BI is no longer expecting the data returned so can do nothing with the response. If you were to run the same script directly against your R engine of choice, outside of Power BI, then the script does work as expected. The good news - we do have a workaround until Microsoft fix this defect in Power BI, albeit not a very graceful one. You need to alter the number of seconds passed to suspendExec within the loop to be greater than the time it usually takes for the report to run, to allow the report to complete before running reportRunGetStatus for the first time. So change row 82 in the script highlighted below: You can find the average time taken to run the report in the reporting section of the admin tool for your Hornbill instance, highlighted in green below: Just add a few extra seconds on to the average process time when changing the value being passed to suspendExec, and the script won't need to run getURL more than once in the loop. Feel free to comment-out the whole while(reportComplete == FALSE){ line, and matching } character too should you wish, to completely remove the looping (although this is not really necessary). I hope this helps, Steve 1
Victor Posted November 29, 2017 Posted November 29, 2017 @Joyce you need to be a bit more careful with sensitive information such as API key... our forum is publicly accessible (to the actual public, not only Hornbill users). So anyone could see and use such information... We have edited your post and removed this. 2
Joyce Posted November 30, 2017 Author Posted November 30, 2017 Great thanks both @Victor and @Steve G the work around is working great.
Joyce Posted November 30, 2017 Author Posted November 30, 2017 While I have got Steve G attention here, can I use the above script to connect from Excel PowerPivot? which data source query from below should I use?
Steve G Posted November 30, 2017 Posted November 30, 2017 Hi @Joyce, Unfortunately not, as there is no option in Excel to use R scripts as a data source. Kind regards, Steve
Joyce Posted December 4, 2017 Author Posted December 4, 2017 Hi Steve, Changing of the time above, doesn't seems to work with existing report. I keep getting, 'edit permission' and when I click run, it just keeps on loading. I have even reduce the size of my report. but it still taking too long and end up timing out. Does the example you provided above, in seconds or minutes? What value do I put for this example? is it '80' for 80 seconds?
Joyce Posted December 5, 2017 Author Posted December 5, 2017 Something has change on Hornbill, because the same report which took 1 -3 second to run last month, is taking 30 sec to run now!!, I am real struggling to get them to refresh on Power BI because of this. @Steve G
Joyce Posted December 5, 2017 Author Posted December 5, 2017 The above file is smaller than the below, but it is taking too long to run:
TrevorKillick Posted December 5, 2017 Posted December 5, 2017 @Joyce In the Output Formats tab if you make sure only CSV is checked then the report will only generate the CSV file required by Power BI, to ensure backwards compatibility the default is to generate PDF and CSV if no optional outputs are selected. Kind Regards Trevor Killick
Joyce Posted December 5, 2017 Author Posted December 5, 2017 Thanks @TrevorKillick It now take less time to run, but more than one report run is somehow happening , with the R script to connect to Power BI, and still doesn't download the update on Power BI.
m.vandun Posted December 5, 2017 Posted December 5, 2017 @TrevorKillick Is there a cap at the maximum rows to export? I have a couple of reports that stop at 9000. Regards, Mark
Victor Posted December 5, 2017 Posted December 5, 2017 @m.vandun 2 hours ago, m.vandun said: Is there a cap at the maximum rows to export? I have a couple of reports that stop at 9000. Yes, that's a hardcoded cap.
Joyce Posted December 5, 2017 Author Posted December 5, 2017 I think I found out what the problem is, the script is looking for HTML file to download to Power BI, but now on hornbill we only create PDF and CSV Is there a way to get the HTML format report again?
m.vandun Posted December 6, 2017 Posted December 6, 2017 @Victor 13 hours ago, Victor said: Yes, that's a hardcoded cap. Are there any best practices for dealing with this cap? Is there a way around this cap? Should I create multiple reports, splitting Incidents and Service Requests for example or is there a way to only export changes and import these to the existing table. I would really like the historical information for camparisson. Mark
Joyce Posted December 6, 2017 Author Posted December 6, 2017 Hi @Victor I now cannot download any of the reports, I am getting the following error when try to open a csv report: Error --- : Array ( => https://eurapi.hornbill.com/vinci/dav/reports/32/Service desk % Incidents resolved within SLT Monthly_1779.csv [content_type] => text/html [http_code] => 404 [header_size] => 468 [request_size] => 242 [filetime] => -1 [ssl_verify_result] => 0 [redirect_count] => 0 [total_time] => 0.024697 [namelookup_time] => 0.004324 [connect_time] => 0.005227 [pretransfer_time] => 0.019288 [size_upload] => 0 [size_download] => 0 [speed_download] => 0 [speed_upload] => 0 [download_content_length] => -1 [upload_content_length] => 0 [starttransfer_time] => 0.024613 [redirect_time] => 0 [certinfo] => Array ( ) [primary_ip] => 172.16.1.22 [primary_port] => 443 [local_ip] => 172.16.1.34 [local_port] => 45216 [redirect_url] => ) 0
Victor Posted December 6, 2017 Posted December 6, 2017 @Joyce have a look at the report name... it won't work if you have any of these characters: # % & { } \ < > * ? / $ ! ' " : @ (this is fixed in the next admin tool update, but meantime, the report name can not contain any of these characters) @m.vandun there is no way around the hardcoded cap I'm afraid... so yes, the best approach would have to be multiple reports... splitting them based on request type is a good idea... you could as well split them based on dates..
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now