Many business intelligence solutions are today available in the market, and sometimes companies need to integrate these solution’s capabilities into their web applications. An interesting case study is the integration of Microsoft PowerBI into the eCommerce solution of “Tangent Solutions“, for this reason a hackfest have been organized last year and one of the challenges was integrating PowerBI into a solution built with PHP and ingesting data from complex and dynamic data sources.
Tangent Solutions is a specialist in digitizing business and one of their latest challenges is providing more in depth and dynamic reports for the clients. As the company has clients spread across a wide range of industries using a wide range of technologies they needed a powerful reporting solution which is easy to use, but can also adapt to any data thrown at it.
The key points highlighted for this challenge :
- They wanted to be able to report on complex data structures such as EAV
- The reports should be embeddable in a wide range of different tech stacks
- They wanted to easily pull in data from different systems
As part of the solution the customer was looking for a clean way to ingest data into Power BI to make it easier to report on as well as gain some data sanity. To achieve this a new data feed platform was created. The data feed platform handles the exports from the external services, runs formatting and sanity checks, and finally exports it to Power BI through the Power BI REST API.
Why choosing PHP ? Simon Jäger, Software Engineer at Microsoft, and one of the core team who worked on this project noted in github :
THE LANGUAGE DECISION WAS MADE DUE TO SUPPORT BY LIBRARIES THEY ALREADY HAD IN PLACE TO COMMUNICATE WITH THEIR ECOMMERCE SYSTEMS
The first stumbling block encountered was authenticating with Azure AD. Implementing this was easy enough and looked something like this:
try {
/** @var GuzzleHttp\Client $client **/
$response = $client->post(
'https://login.windows.net/<tenant-id>/oauth2/token',
[
"headers" => [
"Accept" => "application/json"
],
'form_params' => [
'resource' => 'https://analysis.windows.net/powerbi/api',
'client_id' => $this->clientId,
'client_secret' => $this->secret,
'grant_type' => 'password',
'username' => $this->username,
'password' => $this->password,
'scope' => 'openid',
]
]
);
$body = json_decode($response->getBody()->getContents(), true);
return $body['access_token'];
} catch (ClientException $e) {
return ['error' => $e->getMessage()];
}
The access token returned from the above code was then added to the Authorization header to authenticate all the requests to the Power BI REST API.
The next step was to get the formatted data into Power BI. The solution was Guzzle (http://docs.guzzlephp.org/en/latest/), a PHP HTTP client for making the requests. Again, this was surprisingly easy and was achieved with the following code:
try {
/** @var GuzzleHttp\Client $client **/
$client->post(
https://api.powerbi.com/v1.0/myorg/groups/<group-id>/datasets/<dataset-id>/tables/<table-name>/rows,
[
'headers' => [
"Accept" => "application/json",
"Authorization" => sprintf("Bearer %s", $token),
],
'json' => $data
]
);
return true;
} catch (ClientException $e) {
return false;
}
The last step to the process was getting the embedding to work. They decided to embed it into an existing web application by using the PowerBI Javascript SDK. The first part of the embedding required a container:
<div class="container"> <div class="row"> <div class="col-md-12"> <div id="reportContainer" style="height: 600px"></div> </div> </div> </div>
Once that was set up, the following code embedded the report:
var models = window['powerbi-client'].models;
var config = {
type: 'report',
accessToken: '<access_token>' // Token received in the first code block above
embedUrl: 'https://embedded.powerbi.com/reportEmbed?reportId=<report_id>&groupId=<group_id>',
id: '{{ report }}',
permissions: models.Permissions.All,
settings: {
filterPaneEnabled: true,
navContentPaneEnabled: true
}
};
var reportContainer = $('#reportContainer')[0];
var report = powerbi.embed(reportContainer, config);
And finally as you can see below is an example of a live, interactive report built from the ingested data from Magento:
At the end of the event, they finally proved that the scenario works. A new data feed platform was created, which could ingest data into the Power BI ecosystem from this very dynamic world that the customer started in. The ingestions were successfully able to format and validate the data before entering the Power BI service. In the future, the customer is considering expanding this data feed platform to support multiple types of data sources – both for ingestion and egestion.
This illustrated a perfect case, where even the most dynamic of data sources can be dealt with – allowing for anyone to take their data and leverage Power BI to gain insights and knowledge about it. Ultimately, this gives the ability for anyone to embed and integrate powerful visualizations and reporting into their own solutions.
Additionally, the solution showcased proper interaction between open-source/non-Microsoft tools and frameworks with the Power BI service.
Dave Nel, CEO Tangent Solutions, Microsoft Regional Director, after that the very successful solution was executed within a very short timeframe said : “It is our belief that Digital Transformation without intelligence is stabbing in the dark. The data feed platform created during the workshop has helped us to lay the foundations for a massive value add for our customers and we hope the broader development community as well.”
