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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

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.

Top Solution Authors