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
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.
When you build a Data Warehouse (loading source/operational data with ETL), you model it in a way that reflects the true relationships of the data objects to each other. You can do this in either normalized or dimensional fashion (2 main schools of thought). And you would typically include data at its most granular level possible - this way, you can present it at any level of summarization you like. A Data Warehouse with only summarized data is of little value, unless that's all your consumers want. Ever.
You can also add historical tracking into the Data Warehouse model, relieving the source systems of that burden. But what if the source overwrites everything and doesn't track objects historically? It must be manufactured. You may need ETL for that.
PowerBI can then be used to smartly present this data any way the consumer wants. You can also service multiple consumer types, requiring data at different levels of granularity: a manager wants to perform detailed analysis of current data every day, while an executive wants high-level, summarized data on a weekly basis with an historical perspective.
Your business process could change - if this happens, you could adjust or build new PowerBI applications to change with it, leaving your Data Warehouse intact (remember, the Warehouse is designed based on the data object relationships, not necessarily the business process).
In my current case, I have a Data Warehouse that sources from Salesforce but also merges in 8 years of history from Excel. I use SSIS to feed my SQL Server Data Warehouse daily. And I have a view layer on top of the Data Warehouse, which lets me abstract and transform the original data in support of my PowerBI presentation layer. When I change the View layer, it can affect all PowerBI reports, saving me maintenance time (most views are shared across all PowerBI reports). But if I have a specific change, affecting only one PowerBI report, I can do that as well.
This architecture adds complexity (and cost) but if your client has advanced data analysis needs, it provides the most flexibility and power.
I will add that because of PowerBI's ability to transform data, I find myself doing less of it in the Data Warehouse and ETL pipeline. I just need to carefully decide which transformations I want to "institutionalize" (put in the DW/ETL) versus "localize" (put in PowerBI).
Another thing: since my primary source data is Salesforce, I use the ETL to perform cleansing and conforming of the data as it goes into the Data Warehouse - because we all know that unless the Salesforce application is tight, the data can be very inconsistent and dirty. I would not want to try doing all that with PowerBI.
So, if you have a single, moderately-sized, fairly clean data source and only a couple BI reports, a Data Warehouse might be overkill. But if you have multiple sources, some with dirty data, and need to manufacture history along the way, I would suggest the Data Warehouse approach.
