Hi everyone. I am not sure if I am on the right forum but will give it a go. Thanks.
I was wondering what are the advantage/disadvantages of building a data warehouse. Currently, we are using power BI which connects to various data sources using native connectors, clean the data using power query and build a reports/dashboards around it. My thought was is it expensive to build a data warehouse rather than using the power bi itself and do the ETL, etc. from there?
Thanks for the tips and valuable links that may help. Appreciate it
I would say, it depends on various factors such as size of your data, refresh rate required, type of data sources and ETL Steps involved. Either way, now that you have DataMart option available in Power BI, you can build a Data Warehouse within Power BI Service itself (hopefully affordably) compared to building and managing Data Warehouse on a separate server and having to setup an Enterprise Gateway to access it through Power BI.
In a similar scenario, I set up multiple dataflows from each data source and then created a golden dataset with RLS enabled and also maximum permutation and combination of base measures to build reports on. This provided me and the power users with option of creating custom reports from a common set of data sources leveraging the golden dataset or in some special cases create their own golden datasets by accessing the dataflows. This allowed the report developers to focus on Report development, UI/UX aspects rather than having to figure out data refresh, data connections and ETL steps involved.
But in cases where you have to import data from data sources where there is no native connector available or does not provide you with access to specific content format you are looking for, say Project Management softwares - MS Projects, Azure DevOps, Primavera etc., it is better to set up a Data Warehouse so you can save time for the Power BI Developers.
