Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
At the moment I get my data from exports that are automatically added to a folder. But I get this export only once a day. In order to have real time data, I would like to connect to our MySQL database. But before I do that I just want to make sure if there are any pro's or con's for connecting directly with the database.
Can it have any influence on the database, or it speed for example?
Thanks in advance for sharing your experience.
Solved! Go to Solution.
Hi @Anonymous ,
According to the official document, when connect to MySQL database, the only available connection mode is Import, which means that Power BI will cache the data that you’re connected to creating a point in time snapshot of your data.
So let’s check out some pros and cons of the Import mode.
Refer to:
https://hevodata.com/learn/mysql-to-power-bi/
https://tessellationtech.io/import-vs-direct-query-power-bi/
https://radacad.com/directquery-live-connection-or-import-data-tough-decision
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
According to the official document, when connect to MySQL database, the only available connection mode is Import, which means that Power BI will cache the data that you’re connected to creating a point in time snapshot of your data.
So let’s check out some pros and cons of the Import mode.
Refer to:
https://hevodata.com/learn/mysql-to-power-bi/
https://tessellationtech.io/import-vs-direct-query-power-bi/
https://radacad.com/directquery-live-connection-or-import-data-tough-decision
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Connecting directly to the database is always going to be preferred to extracting to files.
You obviously get the benefit of "real-time" data, potentially less overhead in maintaining extraction processes, etc. etc.
Yes, you can most definitely effect database performance. Just like you can if you run poorly written queries directly, e.g. SELECT * FROM myBiggestTable
You need to be careful when you decide whether you're going to choose 'import' or 'direct query' methods depending on data size and actual frequency required. Business often say they need the data to be "real-time" but rarely is that ever actually true.
I would typically say, choose 'import' first over 'direct query'.
Either way, there's always the temptation to hand author the query vs connecting to the tables and letting Power BI do the smart stuff in the background. Typically, writing the query yourself will cause query folding to break. There are some exceptions to this using the Value.NativeQuery() function but generally speaking, you're better off connecting to tables and use Power BI to do the filtering and transformations to give you the best chance of query folding working.
I'm not sure about MySQL but for MSSQL you can profile the performance of the queries to ensure minimum possible impact.
I hope this helps.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.