cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
globe11123
Frequent Visitor

Other methods of pushing data to PowerBI

We're looking to move our database into AWS, I need to look at different methods of getting our data in PowerBI.

 

Our current setup on virtual machine 1 where the work system is located, it generates the xlsx files needed for powerbi, then a batch script is ran automatically to move them to another VM2 where the On-premisis data gateway is located. There is a scheduled PowerBI refresh every day to make sure the dataset/report is updated.

 

What are the other options in doing this or should I just replicate what I have already done but in AWS. (Albeit will be done in a single environment rather than multiple VM's)

 

Also connecting the system to powerbi is out of the question as it was very slow when we did it, it doesn't need to be live data.

2 REPLIES 2
Burningsuit
Super User
Super User

Hi @globe11123 

Connecting directly to Power BI will always be slow, unless your SQL server is very fast. But as you say it doesn't need to be live data, so you should try "import mode" Assuming it's a SQL style database, Import mode reads the tables from the Database and loads them in to the Datamodel in Power BI. You then manage regular refreshes to update the information, this could be done every day as you're doing now.

You seem to have all the elements in place, simply put the Database into the on-premise Gateway, connect to it with Power BI, import mode, build your reports, publish them and manage refreshing through the Gateway.

I don't see you'll get any benefits by moving to AWS unless you move your database there as well, replicating your current setup in AWS will have no benefits at all. You need to get away from using intermediate .xlsx files and let Power BI (Power Query) access your database directly, that will give much better results than implementing AWS with your existing setup.

Hope this helps

Stuart

I've gone ahead and moved everything up to AWS, service is installed on same instance. Just updating the source now and I'm having an issue.

 

I've shared the folder where the excel files are located on AWS, mapped a network drive onto my PC with the details of the share. Updated all the date sources to this new location and did a refresh, which worked. But looking at the scheduled refresh on the powerbi service, it cant find the data source? 

 

I've put the IP address within the data source connection which I guess it can't reference? It shows that it can see the gateway but I'm guessing it wants the name of the computer rather than an IP?

 

*Not quite ready to switch to pulling the data from the database, this is just a fix in the mean time to get our KPI's up again.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors