Enable to connect with Power BI

[Joyce]

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

 

 

[m.vandun]

I'm having the same issues.

[Victor]

Something that @Steve G might be able to assist with?

[Steve G]

@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

 

[Victor]

@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.

[m.vandun]

The workaround works like a charm.

[Joyce]

Great thanks both @Victor and @Steve G the work around is working great.

[Joyce]

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]

Hi @Joyce,

Unfortunately not, as there is no option in Excel to use R scripts as a data source.

Kind regards,

Steve

[Joyce]

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]

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]

[Joyce]

The above file is smaller than the below, but it is taking too long to run:

[TrevorKillick]

@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]

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]

@TrevorKillick
Is there a cap at the maximum rows to export? I have a couple of reports that stop at 9000.

Regards,

Mark

[Victor]

@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]

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]

@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]

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]

@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..