Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.