> 2) either a vm running windows server or create a db on azure to update manually because powerbi doesn't allow aws databases
I believe this is a misunderstanding. You can totally connect to AWS databases with PowerBI, assuming your AWS database is routable. You can't set up the connection through the web interface, but you can use PowerBI Desktop to set up virtually any database that has an ODBC driver as a source[1]. And once you publish it to the Azure-hosted PowerBI instance, it'll use that connection string to connect directly to the data source and automatically update/refresh like any other data source.
As for a VM running Windows Server - that's the option if your AWS database isn't routable by the Azure-hosted PowerBI service. You install a gateway for PowerBI to hop through, and configure all of the data sources within that gateway. That way your internal AWS databases are not publicly routable, and you can lock down and centralize the connection between PowerBI and AWS via that gateway box[2]. Data Gateway is a Windows-only application at the moment, so that part is a pain. But it's a relatively minor pain in the grand scheme of BI implementations.
Yes exactly, so the only way to have a non public database is to run a Windows vm on AWS, I would rather avoid that, especially because we need one vm per customer.
Thanks for the detailed write up!
Is there a reason you need one VM per customer, and can't use a single Data Gateway to manage multiple data connections?
On that note, you don't have to use the official data gateway application. It certainly has a lot of ancillary benefits, but isn't critical. You can just as effectively create a bastion linux host and use ssh to remote forward the connection to AWS database[1]. I've done that plenty of times in the past, to access databases like Redshift and Dynamo that were within non-public VPCs. Easier to manage and cheaper to run per client, if that's a hard requirement.
You could also use something like QuickSight, if you're purely AWS. But I'd only recommend that if you expect to allocate a large amount of development resources for support. PowerBI is a lot more user friendly for business users to be self-sufficient, and sometimes it's less of a hassle to deal with the up-front complexity of that than to deal with the non-stop application support needs of business users.
I believe this is a misunderstanding. You can totally connect to AWS databases with PowerBI, assuming your AWS database is routable. You can't set up the connection through the web interface, but you can use PowerBI Desktop to set up virtually any database that has an ODBC driver as a source[1]. And once you publish it to the Azure-hosted PowerBI instance, it'll use that connection string to connect directly to the data source and automatically update/refresh like any other data source.
As for a VM running Windows Server - that's the option if your AWS database isn't routable by the Azure-hosted PowerBI service. You install a gateway for PowerBI to hop through, and configure all of the data sources within that gateway. That way your internal AWS databases are not publicly routable, and you can lock down and centralize the connection between PowerBI and AWS via that gateway box[2]. Data Gateway is a Windows-only application at the moment, so that part is a pain. But it's a relatively minor pain in the grand scheme of BI implementations.
[1] https://docs.microsoft.com/en-us/power-bi/desktop-connect-to...
[2] https://docs.microsoft.com/en-us/power-bi/service-gateway-ge...