Syncing data to PowerBI

0
votes

I have a problem of syncing data from MySQL to PowerBi cloud for reporting purpose. The footprints of the flow of the information is as below:

[Live Database] can not be connected on public network -> download database to a Linux os based MySQL server which can be connected on public network. -> Above download/mysqldump runs every hour to export/import data. -> Lots of views runs to consolidate/prepare analytical data from different tables and insert it into simple MySQL tables. -> A powerBi gateway is installed on a separate windows server to fetch data from mysql server and send to PowerBI cloud -> A new gateway is required for each new client but the main dataset remains same, which means there are more gateways and powerbI cloud accounts to connect and fetch data. this increases load on MySQL server

Size of data downloaded and imported on MySQL server is 9GB size. Views runs to consolidate data into simple MySQL tables takes 40 mins Above two tings happens every two hour There is only one MySQL server which hold data for each new client Number of gateways increases with each new client There is one powerBi cloud account for each powerbi gateway There is scheduled refresh of data on each powerBi cloud account. As the number of account have increased it increases load on main MySQL server and it crashes frequently.

I want to know if this is the best way of syncing data to powerBI? How can I reduce the load on main MySQL server?

There 5 gateways, on for each client,

[Live Database] -> Mysql Server1 -> Windows Gateway1 -> PowerBi CLoud1 [Live Database] -> Mysql Server1 -> Windows Gateway2 -> PowerBi CLoud2 [Live Database] -> Mysql Server1 -> Windows Gateway3 -> PowerBi CLoud3 [Live Database] -> Mysql Server1 -> Windows Gateway4 -> PowerBi CLoud4 [Live Database] -> Mysql Server1 -> Windows Gateway5 -> PowerBi CLoud5

1
mysqlsynchronizationpowerbi
Why do you have Power bi reports per client? this might be a requirment when they design their own reports.. Else I would go with row level security.. – Aldert
@Aldert the central db has shared information, each client can do self service to generate reports relevant to them – Krishan Gopal
I would consider Dataflows per client and refresh once a night. I do also not understand why you would need 5 gateways.. – Aldert
Each client is a separate company with their own PowerBi Account, separate gateways, that is the setup with a shared database. – Krishan Gopal
Your solution seems to have a lot of overhead in maintaining gateways, having data for each client in your MySQL db. please have a look at power bi dataflows, it might help you. – Aldert

1 Answers

1
votes

It seems you can benefit from the latest features available in Power BI such as:

  • Direct Query (if the volume allows)
  • Incremental refresh option, you may need to turn on this feature on your PBI desktop as shown in the picture.
  • Go to File Menu, Options , [Preview features], Incremental Refresh Policies [feature may be moved to another category when preview completes] - Maybe be a Premium feature only for now.
  • If feature is not available in your system, you may need to create views in your database that only pushes new records into a separate landing table in PBI, then join that data with existing table [+remove duplicates] for the report to use. (basically simulating incremental refresh)

PBI Option

Incremental Refresh Policies

More about Incremental Refresh at: https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh