Forum Discussion
Power Bi vs. Data Warehouse
Hello. Everyone says that a data warehouse and Power Bi are complementary and that the better you get with Bi the more a data warehouse can be put to good use. I almost see that Power BI is a replacement for a data warehouse, no? If you have databases with the data, can't you just get the queries that you need from those database directly into power BI? I guess the only issue would be the amount of data that you can work with is more in a data warehouse tool (Pentaho). Even the creation of metrics is much more flexible in power bi. In our data warehouse, a lot of the output is at the summary level. I guess we can adapt the warehouse but every iteration seems to take a lot of time. Thoughts?
- ammartino44 we're doing exactly that using power bi on top of data warehouse. Good thing is power bi has ability of DirectQuery feature which means power bi connects live to your data source and doesn't import any data from the warehouse as data warehouse has millions of rows of records. I would suggest you use DQ feature of power bi and connect straight to data warehouse and that would also eliminate your problem of memory issues.
Hi ammartino44,
Power BI is cloud-based business analytics service that enables anyone to visualize and analyze data. In my opinion, the ultimate goal of Power BI is to visualize data. It's not a full replacement of data warehouse. But Power BI desktop also provide features for us to model and shape data:
By the way, the big difference between DirectQuery and Import mode is no data is imported or copied into Power BI Desktop in DirectQuery mode. For more information, please refer to this article: Use DirectQuery in Power BI Desktop.
Best Regards,
Qiuyun Yu
17 Replies
ammartino44 You shouldn't compare power bi and data warehouse. Data warehouse is an enterprise need that will store current and historical data for the enterprise while power bi is a visualisation tool. You use power bi for visualising, analysing your data and share it with business users.
ankitpatira. We use Pentaho (have you heard of it?) I believe it is the tool ON TOP of our data warehouse, but it is not very good in terms of analysis and vizualisation. Would you ever put power BI on top of the data warehouse instead? Not really sure how that works.....Also, everyone touts the memory of power pivot and power bi, but almost everything that is output from a data warehouse is at the summary level, so the need for visualization tools is somewhat muted.
- ammartino44 we're doing exactly that using power bi on top of data warehouse. Good thing is power bi has ability of DirectQuery feature which means power bi connects live to your data source and doesn't import any data from the warehouse as data warehouse has millions of rows of records. I would suggest you use DQ feature of power bi and connect straight to data warehouse and that would also eliminate your problem of memory issues.
Well, for smaller datasets, Power BI could theoretically be used as a data mart or data warehouse. The data model behind it is SQL Server Tabular after all, so the same basic technology that is in SQL Server Analysis Services.
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.
Anonymous pleased to read your reply, i am in the same situation with a Big difference is that we don't have a datawarehouse at all, to be honest after working 17 years in 4 countries, 3 continents, maybe 5 companies, I have yet to see one, although my job is reporting coordinator which require a central database to host all the difference source of data :).
I work in construction, for some reason we are technology averse:) a typical IT infrastructure would be a lot of operational database that don't talk to each others, and dozen of people reporting the same **bleep** thing using different tools ( MS access, Massive excel file with vlookup, ok sometimes SQL Express).
last year I discovered PowerPivot, it was a life changing experience, I manage to connect all those data source with all those crazy transformation in one semantic model, and it was properly documented, ok i got even a promotion :) a couple of months later the data keep increasing, PowerPivot did not scale well, the cloud is not an option for contractual reason, I tried all kind of workaround, then I read this Blog from imke it turn out PowerBI desktop engine is a local SSAS server that works only in the local PC ( for obvious reason), and you can export the data to Excel, I moved my Model to PowerBI Desktop, and it running beautifully since three months ( main fact table 5 Million rows and counting)
now I know what's next, in six months with M integration with SQL Server Vnext, I am going to buy a standard license, even with my own money, Microsoft created an awesome technology, it democratized Data, I have experienced that and I am grateful
As noted by others, it is not meant to replace Data Warehouse. Maybe to be used if you don't have a data warehouse or can't afford and you're a very small (startup) company it may work.
I use Data Warehouse and Cubes with PowerBI. Using PowerBI specifically only for Visualization however quick measurements is easy on PowerBI versus me writing or rewriting SQL.
a_mixed_life I still don't get how it wouldn't replace a data warehouse. Maybe I'm struggling to see what a data warehouse is (the full potential). I CANNOT get granular data in my data warehouse. In power BI, when I can get data dumps I can build the data into whatever angles/views I want it to be. Per IT, we can get the data warehouse to where it will add all the necessarry metrics but that's always the next iteration, so it seems that anything new or outside of the scope of the stock metrics and looks in the data warehouse tool, would be better to use power b.
Also, do you use powerpivot on top of the data warehouse? How do you get the data for visualization?
Seems to me that the new Composite model feature and the previously available dataflows kills the adventages a datawarehouse could have against powerbi cloud service...
Well done microsoft you truely killed a monster in my understanding
