The adoption has been extremely painless and business users are very comfortable creating reports and discovering new ways to represent their data.
In response to your points above... 1) A consultant is not required to set up visualisations. With minimal training (a few hours) most users understand the fundamentals and are ready to go.
2) We use AWS Redshift. You have the option to import data into Power BI or do a direct query from the cloud platform to Redshift with odbc.
3) what are you trying to do with the API? To automate reports you normally set up a gateway connection to your data source and schedule the refresh in powerbi cloud. No API required normally.
It also can use office 365 or Azure AD for single sign on, making sys admin activities a breeze.
Send me a message if you want to discuss anything specific and I'd be happy to help you out.