Forum Discussion
Power Bi vs. Data Warehouse
- 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
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
Anonymous I'm not sure if you are saying you would require a central database or if you have one. In anycase, that central database would be a data warehouse. It may not be well build or conform to a "dimensional architecture", but a data warehouse none the less.
A data warehouse generally uses an ETL process to Extract data from the operational systems, Transform that data, and Load it into another repository (a.k.a., the data warehouse). From the conversation here, at best PowerBI is being used as an ETL tool with the loading part being the subsequent reports or analyses. But, Power BI was created as a tool for reporting and analysis using a data source such as a data warehouse. Though it can connect to any type of data source. It is absolutely NOT a data warehouse.
A data warehouse provides a number of advantages, the top 3 being: 1) Getting users out of the operational systems, 2) Providing a more efficient mechanizm for handling large sets of data and big queries, and 3) Providing a more user friendly layout of the underlying data structures.
Anonymousyeah you're pretty much reiterating what I was saying. Power BI is not a data warehouse but it has some ETL capabilities that allow you to fake it to a certain extent. I still don't have a data warehouse. In fact the one small limited use warehouse I was formerly using for part of my data has been abandoned at this point. I just go to the source database and do my own ETL now. But at least I've gotten my boss to agree that we need a data warehouse. Unfortunately I am not a department so it will be a while until I have time to build one.
- Remember that you can create an Organizational Content pack and manage everything kind of centrally. But, I agree, Power BI is not a substitute for a real data warehouse but it is quite surprising what it is capable of and it has a lot of the same base functionality and is built on top of some of the same technologies.
At this point I'd almost rather build a data warehouse with DAX than SQL code...
KHorseman wrote: the time is fast approaching where I'm going to stop suggesting and start demanding :smileylol:Anonymous
How do you get your data to manipulate in power bi? SQL? What is import vs. direct query? Thanks.
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
