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

Written by Akram Wahid 2 years ago

are you looking for a chief cook who can well craft laravel and vuejs, to make some awsome butterscotch,
yes then it is right time for you to look at my profile.

Do you want to write Response or Comment?

You must be a member of techalyst to proceed!

Continue with your Email ? Sign up / log in

Responses

Be the first one to write a response :(

{{ item.member.name }} - {{ item.created_at_human_readable }}

{{ reply.member.name }} - {{ reply.created_at_human_readable }}