Jump to content

Enable to connect with Power BI


Joyce
 Share

Recommended Posts

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

 

 

Link to comment
Share on other sites

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

image.png

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:

image.png

 

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

 

  • Like 1
Link to comment
Share on other sites

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?

image.png.bcfcbe92f4cf3220c4d961e6db1ffe78.png

What value do I put for this example? is it '80' for 80 seconds?

Link to comment
Share on other sites

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

Screen Shot 2017-12-05 at 09.03.31.png

Kind Regards

Trevor Killick
 

Link to comment
Share on other sites

@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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...