Laravel PHP Power BI

Push data into a Power BI dataset from PHP and Laravel Application

Integrate Microsoft Power BI reporting tool with Laravel

I had a request to Integrate Microsoft Power BI Reporting tool to a custom Developed ERP Product which is based on PHP Laravel Frame Work and My SQL Database... After several hours of research and studies through the documentation, I end up with a successful effort.

So I though of sharing my efforts step by step, so that It will be helpful to someone who might have a similar requirement.
Please Note that this blog post is not about how to use Power BI Reporting tool, if you want me to help you learn the tool its self, contact me through my Linked in or Mobile and I will help you for sure..

With the Power BI API, you can push data into a Power BI dataset. For example, you want to extend an existing business workflow to push key data into your dataset. In this case, you want to push a Sales Marketing dataset which has a Product table into a dataset.

Before you get started pushing data into a dataset, you need a Power BI account Registered with Azure AD, Follow this link to obtain a power bi account, method one (Register with the Power BI App Registration Tool) is which I follow, method 2 is little cumbersome.
https://powerbi.microsoft.com/en-us/documentation/powerbi-developer-register-app/

Once you register you will be given Client ID , Client Secret, if you follow the above article correctly,
I assume you have a client id and client secret now, so the next step is to obtain office360access token before pushing data into PowerBI Dataset.


You may use the following PHP HelperClass function to obtain office 360 access token and to push the data to powerBi.

class PowerbiHelper
{
    public static function processPowerbiHttpRequest($url, $header, $data, $method = 'POST')
    {
        $header[] = 'Content-Length:' . strlen($data);
        $context = [
            'http' => [
                'method'  => $method,
                'header'  => implode("\r\n", $header),
                'content' => $data
            ]
        ];
        $content = file_get_contents($url, false, stream_context_create($context));
        if ($content != false) {
            $content = json_decode($content);
        }
        return [
            'content'=> $content,
            'headers'=> $http_response_header,
        ];
    }


    public static function getOffice360AccessToken()
    {
        $data = http_build_query([
            'grant_type'    => 'password',
            'resource'      => 'https://analysis.windows.net/powerbi/api',
            'client_id'     => 'POWERBI_ADD_CLIENT_ID',
            'client_secret' => 'POWERBI_ADD_CLIENT_SECRET',
            'username'      => 'POWERBI_USER_NAME',
            'password'      => 'POWERBI_PASSWORD',
        ], '', '&');
        $header = [
            "Content-Type:application/x-www-form-urlencoded",
            "return-client-request-id:true",
        ];
        $result = self::processPowerbiHttpRequest('https://login.microsoftonline.com/common/oauth2/token', $header, $data);
        if ($result) {
            return $result['content'];
        }else{
            return null;
        }
    }


    public static function debugPrint($param)
    {
        print '<pre>';
        print_r($param);
        print '</pre>';
    }
}


and this is a sample of how you will utilize the above helper class to push the data.

$office360token = PowerbiHelper::getOffice360AccessToken();
        if(!is_null($office360token)){

            $url = 'https://api.powerbi.com/v1.0/myorg/datasets/%s/tables/%s/rows';
            $url = sprintf($url, 'YOUR_DATA_SOURCE_ID', 'YOUR_TABLE_ID');

            //$header[] = "content-type: application/json";
            $header = [
                "Authorization:{$office360token->token_type} {$office360token->access_token}",
                "content-type: application/json"
            ];

            $result = PowerbiHelper::processPowerbiHttpRequest($url, $header, json_encode([]), 'DELETE');

            $rows = array();       

            $rows[] = [
                'Date' => $workforce_last->logged_on,
                'TOTAL EMPLOYEE' => 100,
                'ONDUTY' => 80,
                'ON_VACATION' => 10,
                'DAY_OFF' => 3,
                'ON_ESCORT' => 3,
                'RELIEVER' => 4
            ];

            $data = [
                "rows" => $rows
            ];
            $result = PowerbiHelper::processPowerbiHttpRequest($url, $header, json_encode($data), 'POST');



        }


If you have any other questions, experience or insights on "How to use Microsoft Power BI reporting tool with Laravel Framework" please feel free to leave your thoughts in the comments bellow which might be helpful to someone some day!.


Share your thoughts

You may use <pre> </pre> tag for writing multi lines code or <code> </code> tag for writing single line code in the comment section bellow.