Power Bi vs. Data Warehouse

Our data sources are mostly... not great for reporting. We have a small data warehouse managed by a third party that covers a few things but does not include most things that desperately need one. One of the things we're using Power BI for is a sort of stopgap between what we have and the data warehouse we (well, I anyway) want. I do all sorts of wild transformations and merges in my queries (go find some of the threads I have started), which produces fairly large datasets that have a lot of the flexibility of a data warehouse. So when people come up with crazy new things they want added to existing reports, I can often just throw together a quick visual or two, maybe a slicer, and they have what they want, where that data formerly would have been essentially inaccessible. It's not a real data warehouse but it gets us by amazingly well for now.

That said, the time is fast approaching where I'm going to stop suggesting and start demanding a real warehouse. There are very real disadvantages to using Power BI in place of one. Most limitations come from the basic fact that everything is stored in a one file. The more things you try to make a single data model do, the better your chances of producing something that's unstable or at least hard to debug or significantly alter without producing a cascade of errors. Lots of table transformations can produce a large file with slow refresh times. You are also forced to use Import rather than DirectQuery, so you need to do multiple refreshes per day to stay somewhere near realtime, which puts a load on your data source.

Conclusion: if you just can't have a good data warehouse right now, you can go a long way by faking it in Power BI. Power BI is not a substitute or replacement for a warehouse, nor is it meant to be, but you might be surprised how much it can manage. But if you think you need a data warehouse, you're probably correct.