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
globe11123
Helper I
Helper I

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
Resident Rockstar
Resident Rockstar

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
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