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
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.