Tech Blog

Microsoft Power BI – connecting to Alma API

As proof-of-concept, I wanted to see if I could use Alma Analytics as a data source for Microsoft Power BI.

Although I couldn’t get Power BI to communicate directly with Alma Analytics, I was able to make the connection using a PHP web page as an intermediary. 

The process was as follows:

1. Create a report in Alma Analytics 

I created a report of how many items were in each classmark.

This returned the results:

2. Make a PHP page that displays these results

I put a PHP page in my web space that called that Analytics report (in this case ‘Items per classmark’) using its full path, and displayed the results as an HTML table.

<html>
   <head>
  	<title>Microsoft Power BI - API test</title>
    </head>

   <body>
   
<h1>Microsoft Power BI - API test</h1>

<table>

<?php


// Turn off error reporting
error_reporting(0);


$ch = curl_init();
$url = 'https://api-eu.hosted.exlibrisgroup.com/almaws/v1/analytics/reports';

$queryParams = '?' . urlencode('path') . '=' . urlencode('/shared/University of Sussex/Systems/In Progress/Items per classmark') . '&' . urlencode('limit') . '=' . urlencode('25') . '&' . urlencode('apikey') . '=' . urlencode('<<YOUR API KEY GOES HERE>>');
curl_setopt($ch, CURLOPT_URL, $url . $queryParams);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
curl_setopt($ch, CURLOPT_HEADER, FALSE);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'GET');
$response = curl_exec($ch);
curl_close($ch);

/* creates xml array */
$alma_analytics_result_array = simplexml_load_string($response);
$rowset = $alma_analytics_result_array ->QueryResult->ResultXml->rowset;


function remote_file_exists($url)
{
    $ch = curl_init($url);
    curl_setopt($ch, CURLOPT_NOBODY, true);
    curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    curl_close($ch);
    if( $httpCode == 200 ){return true;}
}


  
echo '<table>';  
  
 foreach ($rowset->Row as $row_number){

 echo '<tr><td>' . $row_number[0]->Column1 . ' </td><td>' . $row_number[0]->Column2 .'</td></tr>';
 

 }

 echo '</table>';
    
?>

  
  </body>

</html>

You can see the results here.

To adapt the code for one of your own reports, you would need to change the flight path to the report and put in your own API key:

</a >

3. Use that PHP page as a data source in Power BI

In Power BI:

Select ‘Web’ as the source:

, and point to the PHP page:

Power BI parses the page, and recognizes a table of data: 

Tell BI to ‘Load’ that table.

4. Start visualizing

You can then use Power BI’s range of tools to easily create data visualizations.

You can see a very basic example here.

 

 

Leave a Reply