Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
iAm3G
Frequent Visitor

Real time connection to MySQL database

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.

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee


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

View solution in original post

6 REPLIES 6
Eric_Zhang
Employee
Employee


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


@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 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. 🙂 

 

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!

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/RealT...', 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); } ?>

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?

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.