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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

PowerBI to MySQL : Best-practices from connection to scheduling update?

Scenario - I'm looking forward into connecting a replica of a Magento database that runs on a synced Mysql server on-premises. We've got 300+ tables of which 100 are going to be used for analytics purposes. With roughly 2 million rows / month loaded into PowerBI, considering a potentially 10x increase in volume over the next 5-6 months, what are your thoughts about performance and costs on having a neat reporting solution updatable every 24 hrs?
Note - Responding to any of the following questions, based upon your personal experience, will be highly appreciated.
 
Details - Here are a couple of clues I'd be considering:
Connection [we're talking about Import mode with full DAX functionalities] :
  • Are there any database size limits? [I've heard of 1GB under free cloud sharing offering]
  • Is there a maximum number of tables to be loaded in order to avoid a connection time out, does it depend on powerbi configuration, connectivity speed or hardware specs?
  • How does manual refresh work? It's simply executing queries loading the data each time from scratch or there's a sort of incremental trigger in order to update only the new values?
Building the model:
  • PowerBI provides a full-set of options when it comes to data transformation, but how about using a service like Azure Analysis Services in order to create the model and therefore create reports and deploy with powerBI ? Let's consider functionalities vs performance vs cost.
  • Is there any feature to connect PowerBI to Azure ML and vice-versa, or manual data-import is the only viable solution?
  • Distributing a report on a personal web-site requires powerBI pro for $10/month, what about the limits on shared capacity and does it support [embedded functionality] DAX queries?
Scheduling refresh:
  • On powerBI community there's plenty of people running into issues when it comes to setting up a gateway between cloud and on-prem data. Personal vs Enterprise gateway considerations? It seems powerBI requests for a connection for each table it has to import and another unauthorized one at the same time.
  • Any specs about how long does it take to update data from gateway vs manual refresh vs azure analysis services?
I know it's a bit of a monolithic thread, but we're facing each of the posted questions in order to evaluate a solution for a single scenario.
Thanks a lot in advance!
2 REPLIES 2
Anonymous
Not applicable

Bit of a can of worms question so firstly you'll want to step back and consider the whole data life cycle to make sure its fit for purpose.

 

So you say you have a database with 300 tables, only 100 of which need to be used in reporting.  Is this database a production system with standard read/write operations occuring constantly?

 

Have you considered a data warehouse in the first instance, then making use of Views and Stored Procedures to provide a tighter querying process?  Sometimes you might only want a simple model and going through Analysis Services could be overkill.

 

Analysis Services is great, i've been using it.  The partitioning is worth considering, espeically if you have so much data.  We have a data warehouse set up (Azure) and we make use of the Data Factory to call a Logic App which processes our Azure AS models.  I then surface those models up in 7 different workspaces that have a variety of regionalised reports each with distinct localisations for the language and currencies of the managers in that region.

 

Anonymous
Not applicable

The database is for read-only purposes (in terms of our scope).

 

For now, we only need a sales reporting service, hence a data-warehouse seems a little bit overwhelming (cost and infrastructure).

We've been looking into Data Factory in order to get all the data into an Azure DB, therefore importing it into PowerBI or AAS.

 

Could I ask you how did you manage to work around connection between DataFactory and on-prem data? There's no way to get a static IP from ADF in order to grant access.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors