On the surface it may look like Microsoft’s data gateway provides an even playing field between IaaS and PaaS approaches with regards to SQL hosting, i.e. you can run your database server in your data center, on a cloud virtual machine, or via Azure managed services, and it ends up being functionally the same from the perspective of Power BI/Apps/Automate. Well, in practice that’s not quite true. I’ve bumped into a couple of annoying limitations of the gateway via trial and error:
- You can’t merge queries in dataflows if one of these queries comes from an on-premises source. You can remediate this by creating an unloaded version of said query for merge use, so this is solvable if you change how you orchestrate your queries and dataflows, but I had to find out the hard way.
- You can’t send native queries via the Power Automate connector, though you can trigger stored procedures.
One of my clients has a database running in an Azure VM which is restored from a .sql dump every night. It’s the best we could get from the vendor of their system of record – a niche financial services player that’s quite set in their ways – so these are the cards I’m dealt. I wanted to create indexed views to speed up some of my Power BI dataflows that involve cross table joins, but you can only do so on the database where the tables reside (I have a second database that doesn’t get wiped where I have already created regular views).
So I thought I’d create a flow to automate the recreation of my indexed views every morning, and that turned out to be unsupported too. Could I ask the developer who scripted the dump restore process to also reload some indexed views for me? Maybe, but the whole point of the Power platform is that we shouldn’t have to do that, nor should we have to move to Azure Data Factory or Synapse for pesky reasons. I might eventually figure a free alternative using Azure Functions, though that involves spending more time for learning and implementation, again going against the grain of the Power platform.
Let’s face it, the writing is on the wall, and all new investments are in cloud services. I think it’s fair to conclude that Microsoft doesn’t mind having these irritating limitations contributing to pushing people more and more towards fully managed cloud services. But the fact these services are heavily managed is both a blessing and a curse in terms of how much control you have over their behavior. And their cost tends to scale more in the vendor’s favor, which is an issue not just with Microsoft but across the industry at large. Managing PaaS compute and SaaS usage costs is becoming an increasingly critical concern and skill.
We’re giving up a lot in return for convenience and integration. We’re running greedy decision algorithms that are locally optimal – this is easy and fast to set up! – but I doubt they’ll give us the best outcome when everything runs in the cloud. Will we miss Access, VBA, or SSIS? Well, we just might, if you compare Access to Power Apps licensing costs! You will own nothing, but I don’t think you will be happy.
Crazy Workaround That Might Just Work
When there’s a will there’s a way: here’s my low-code, totally legit, what could possibly go wrong hack that works in Power BI.