What is the point of a data warehouse if Power BI has ETL Capabilities?

We get this question a lot, and for a good reason. In 2018 Microsoft released Power BI Data Flows – a lightweight, self-service data preparation tool delivered as SaaS on the Power Platform. Since then, it has developed into a powerful tool enabling Power BI users to connect to various data sources, orchestrate, transform and load data for their own use, or share with others. In part, these attributes mirror some of the critical functionality of traditional ETL tools used in Data Warehouse (DWH) solution. A savvy Power BI user working in a company with an immature data platform could rightly pose the question – "What is the point of a Data Warehouse if Power BI has ETL capabilities? Is this a bad practice?"  

To best answer this question, we need to compare a DWH and the enterprise-level ETL tools used to build them with Power BI Data Flows and discuss specific business cases for each of them. 

So, what are they? 

Simply put, a data warehouse is a centrally managed, high-performance, scalable data repository for the enterprise that stores and models large quantities of current and historical data from multiple data sources for reporting and analytics. Power BI was primarily released as a visualization tool connecting to a wide range of data connections from different vendors and data sources. A typical scenario is that many Power BI users will choose to use a visualization tool like Power BI to connect their DWH. The DWH has already been transformed and optimized for this purpose. Users can expect timely and constant data with key measures and KPIs already prepared for their consumption. Power BI Data Flows is a tool to bridge the gap between what already exists in the DWH and any additional data you would like to merge with. A modern data warehouse consists of multiple components or technologies with a specific purpose of either fetching, model, adding governance and control, and making data available for consumers.  

1. Functionality – does it have what it takes? 

A big part of a data platform's job is extracting and transforming data, generally known as the ETL process. This is where the differences start to show. Remember I wrote Power BI primarily is a visualization tool? Even though Power BI has a very intuitive interface to operate, the ETL functionality in Data Flows is still limited. On the other hand, data warehouses use enterprise-level ETL / ELT platforms capable of handling large volumes of data, with much richer functionality and more, but can be more challenging to operate and require expertise often held by inhouse data engineers or consultants. In later times the complexity of operating a data warehouse has been improved as data warehouse automation tools have entered the market. 

2. Ease of use 

Microsoft has massive success with Power BI because of how easy it is to use and get started. It's also had a low entry barrier with regards to cost. Its user interface is simplified, streamlined, and builds on people's experience in using Microsoft Excel (as well as the Power Query technology). The "old" way of building a data warehouse was slightly different. Yesterday's method of building a data warehouse meant lots of manual and repetitive work to set up data pipelines and governance features, which is costly and affects the time to market. As automation software has entered the data warehouse market, this element is mitigated, and enterprises can today build complex and scalable data platforms without yesterday's worries.  

3. Scalability and user-friendliness 

One of the main issues in accepting Power BI as a complete alternative to a data warehouse is its limitations in scalability and reuse. Even though Power BI Data Flows can be shared and reused, they are still not scalable or dynamic. They would eventually pose a maintenance nightmare for DWH developers compared to other enterprise-level data orchestration tools. General performance is not comparable between the two. A medium to large organization would eventually hit scaling, performance, and maintenance issues if they were to use exclusively use Power BI Data Flows as the ETL tool and DWH storage.   

4. Consuming the data – where, who, and how? 

Because Power BI is a software service, everything is located inside this service. This is okay if Power BI is the only application you use to analyze and present data, but this is rarely the occasion. Organizations today, large or small, tend to use a variety of applications to analyze and present data. For example, in Microsoft Excel, an application pretty much everybody is using at some level. It is hard or nearly impossible to access the data models established in Power BI for other end-user applications, this can be experienced as quite limited.  

5. Can power BI be used with a data warehouse?  

Absolutely, this is a well-known practice for many enterprises. In a data warehouse, architecture, different technology components have specific functions. Some deal with fetching and transforming the data, creating context, and preparing for distribution to various end-user applications. Power BI is very often one of these applications connecting to the data warehouse to create stunning visualizations and dashboards. The benefit of this setup is it allows other end-user applications such as Microsoft Excel, Qlik, or Tableau to also be used in the organization – all connecting to the same data foundation. Same numbers for everyone – a single version of the truth! 

Summary 

A Data warehouse is a high-performance, scalable platform that will store current and historical data for the enterprise, while Power BI is mainly a visualization tool. You use Power BI for visualizing, analyzing your data, and share it with business users. Even though Power BI offers agile ETL functionality with Data Flows, its primary use is to allow Power BI users additional flexibility to transform merge and share additional data, not to replace the primary ETL tool and especially not a replacement of data warehouse.  

If you are searching for a simple way to fetch and analyze data in a narrowed-down scope, perhaps for your own use, Power BI works fine. Suppose you aim for a data-driven organization where everybody should have the ability to use the same data foundation regardless of end-user tools. In that case, a data warehouse is the way to go.  

For Power BI insight visit our Analytics and Visualization page, and for great content in Norwegian, head to our partner's blog: https://www.innsiktsbrevet.no/