topic Re: Real time connection to MySQL database in Developer

Real time connection to MySQL database

iAm3G — Tue, 04 Jul 2017 22:33:31 GMT

So I have been using PowerBI Desktop to create a "DAILY SALES" report after connecting to my company's MySQL Database and running a ready made query which produces the desired results. The hourly scheduled refreshes are not enough for management as the data changes literally every minute.

Has anyone found a way to get real time results from MySQL into PowerBI or at least used another means or technique to get the updated information from MySQL and somehow stream it to PowerBI Service?

Any insight or ideas to try out would be greatly appreciated.

Thanks in advance.

Re: Real time connection to MySQL database

Eric_Zhang — Fri, 07 Jul 2017 08:55:38 GMT

@iAm3G wrote:

So I have been using PowerBI Desktop to create a "DAILY SALES" report after connecting to my company's MySQL Database and running a ready made query which produces the desired results. The hourly scheduled refreshes are not enough for management as the data changes literally every minute.

Has anyone found a way to get real time results from MySQL into PowerBI or at least used another means or technique to get the updated information from MySQL and somehow stream it to PowerBI Service?

Any insight or ideas to try out would be greatly appreciated.

Thanks in advance.

@iAm3G

MySQL is not in the DirectQuery mode list and to lift the 8 times refreshes limitation you may consider to purchase a Power BI Premium license, see Power BI Premium Capacity Tiers.

Another approach would be using real-time datasets and push data via REST API.

Re: Real time connection to MySQL database

iAm3G — Fri, 07 Jul 2017 09:18:31 GMT

@Eric_Zhang wrote:
@iAm3G wrote:
So I have been using PowerBI Desktop to create a "DAILY SALES" report after connecting to my company's MySQL Database and running a ready made query which produces the desired results. The hourly scheduled refreshes are not enough for management as the data changes literally every minute.

Has anyone found a way to get real time results from MySQL into PowerBI or at least used another means or technique to get the updated information from MySQL and somehow stream it to PowerBI Service?

Any insight or ideas to try out would be greatly appreciated.

Thanks in advance.
@iAm3G
MySQL is not in the DirectQuery mode list and to lift the 8 times refreshes limitation you may consider to purchase a Power BI Premium license, see Power BI Premium Capacity Tiers.

Another approach would be using real-time datasets and push data via REST API.

Thanks for the reply.

Premium is not an option. I have been looking into the REST API with real-time datasets. This will in-fact be my solution going forward. Now I just have to figure out how I can use the DELETE ROWS instruction without unecessarily registering an APP which I will not be creating. Any ways I will mark your response as the solution.

Re: Real time connection to MySQL database

Eric_Zhang — Mon, 10 Jul 2017 01:12:29 GMT

@iAm3G wrote:

@Eric_Zhang wrote:

@iAm3G wrote:

So I have been using PowerBI Desktop to create a "DAILY SALES" report after connecting to my company's MySQL Database and running a ready made query which produces the desired results. The hourly scheduled refreshes are not enough for management as the data changes literally every minute.

Has anyone found a way to get real time results from MySQL into PowerBI or at least used another means or technique to get the updated information from MySQL and somehow stream it to PowerBI Service?

Any insight or ideas to try out would be greatly appreciated.

Thanks in advance.

@iAm3G

MySQL is not in the DirectQuery mode list and to lift the 8 times refreshes limitation you may consider to purchase a Power BI Premium license, see Power BI Premium Capacity Tiers.

Another approach would be using real-time datasets and push data via REST API.

Thanks for the reply.

Premium is not an option. I have been looking into the REST API with real-time datasets. This will in-fact be my solution going forward. Now I just have to figure out how I can use the DELETE ROWS instruction without unecessarily registering an APP which I will not be creating. Any ways I will mark your response as the solution.

@iAm3G

As Power BI authentication is integrated with Azure AD, it is mandatory to register and use the Azure AD App@. If you have any further question, you can post it in a new thread and @me. 🙂

Re: Real time connection to MySQL database

Franklin1 — Tue, 07 Nov 2017 12:31:56 GMT

Hey,

Did it work for you?

Can you provide some details of how you did it (kind of a tutorial for using Real-Time dataset and push data via Rest API)?

Thanks!

Re: Real time connection to MySQL database

iAm3G — Thu, 09 Nov 2017 09:20:08 GMT

I ended up using PHP and cURL: Below is a complete PHP script from deleting existing data and posting new data (Enable keep historical data in streaming data set) your username must be an email from the AZURE ACTIVE DIRECTORY (xxxxx.onmicrosoft.com) for this SILENT LOGON to work. You need to create an AZURE APP to obtain CLIENT ID AND SECRET Use POSTMAN to get the correct POST / DELETE information when exporting from POSTMAN to cURL $username = 'yourUsername'; $password = 'yourPassword'; $url = 'https://login.windows.net/yourCompanyID/oauth2/token'; $params = array('client_id' => 'yourClientAppID', 'client_secret' => 'yourClientSecret', 'scope' => 'openid', 'resource' => 'https://analysis.windows.net/powerbi/api', 'grant_type' => 'password', 'username' => $username, 'password' => $password ); $ch = curl_init(); curl_setopt_array($ch, array(CURLOPT_URL => $url, CURLOPT_RETURNTRANSFER => true, CURLOPT_ENCODING => '', CURLOPT_MAXREDIRS => 10, CURLOPT_TIMEOUT => 30, CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1, CURLOPT_POST => true, CURLOPT_POSTFIELDS => $params )); $output = curl_exec($ch); $info = curl_getinfo($ch); $error = curl_error($ch); curl_close($ch); if ($info['http_code'] === 200) { header('Content-Type: ' . $info['content_type']); $response = json_decode($output, true); $accessToken = $response['access_token']; $authorization = 'Authorization: Bearer ' . $accessToken; // DELETE TABLE CONTENTS $ch1 = curl_init(); curl_setopt_array($ch1, array(CURLOPT_URL => 'https://api.powerbi.com/v1.0/myorg/groups/yourCompanyIDinPowerBI/datasets/yourDataSetID/tables/RealTimeData/rows', CURLOPT_RETURNTRANSFER => true, CURLOPT_ENCODING => '', CURLOPT_MAXREDIRS => 10, CURLOPT_TIMEOUT => 30, CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1, CURLOPT_CUSTOMREQUEST => 'DELETE', CURLOPT_HTTPHEADER => array($authorization, 'cache-control: no-cache') )); $output1 = curl_exec($ch1); $info1 = curl_getinfo($ch1); $error1 = curl_error($ch1); curl_close($ch1); if ($error1) { echo $error1; } // DELETE TABLE CONTENTS END // POST NEW CONTENT $ch2 = curl_init(); $data = $resArray; curl_setopt_array($ch2, array(CURLOPT_URL => 'https://api.powerbi.com/beta/YourCompanyIDinPowerBI/datasets/YourDataSetID/rows?key=YourKey', CURLOPT_RETURNTRANSFER => true, CURLOPT_ENCODING => '', CURLOPT_MAXREDIRS => 10, CURLOPT_TIMEOUT => 30, CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1, CURLOPT_CUSTOMREQUEST => 'POST', CURLOPT_POSTFIELDS => $data, CURLOPT_HTTPHEADER => array('Content-Type: application/json', 'Content-Length: ' . strlen($data), 'cache-control: no-cache') )); $output2 = curl_exec($ch2); curl_close($ch2); // POST NEW CONTENT END //var_export ($output2); } ?>

Re: Real time connection to MySQL database

sumnesh — Tue, 04 Sep 2018 16:25:04 GMT

Thanks for sharing.

I dont have visual studio license which is required in following step:

https://docs.microsoft.com/en-us/power-bi/developer/walkthrough-push-data-get-token

Can we avoid it?