Self-Service ETL with Power BI

Recently, I’ve been using Microsoft’s Power BI heavily and am impressed with this solution as an end-to-end self-service business intelligence (BI) option. In addition, I’ve really come to appreciate the possibilities that self-service BI offers.

What is Self-Service BI?

Gartner defines self-service BI as “end users designing and deploying their own reports and analyses within an approved and supported architecture and tools portfolio.” The Data Warehousing Institute (TDWI) describes it as “[t]he facilities within the BI environment that enable BI users to become more self-reliant and less dependent on the IT organization.”

But Forrester Research notes there’s no standard definition of self-service BI.

What most experts on self-service BI do agree on is the value of removing IT departments that act as a barrier to users accessing and analyzing company data. Often, the access issue can be solved, but the analysis issue is more difficult, especially if additional transformations and manipulation are needed to answer business questions.

What is ETL? What is Self-Service ETL?

As anyone who performs BI or data analysis will confirm, getting data into the format needed for reporting and analysis is the most time-consuming part of data projects.

Coding skills or the use of data integration tools like SQL Server Integration Services (SSIS) may be needed. Extracting data from a source location, transforming it into the desired format, and then loading it into a destination location is referred to as Extract, Transform, Load (ETL). This process accounts for a substantial portion of BI projects.

Power BI really does stand out for its ability to pull data from a variety of sources and transform it for self-service users. Power Query is the component of Power BI that provides lightweight ETL for these users. Power Query uses Microsoft’s “M” language, which can be accessed by a user interface or written directly.

Power Query is only lightweight when compared to enterprise tools like SSIS; the average end user should find it quite powerful, yet easy-to-use. It can:

  • Fetch data from many different sources through its ever-growing list of connectors (see Figure 1).
  • Be used to filter and transform data, and to create and enrich data.
  • Build a “refreshable” process for the end user.

Figure 1. Connectors in Power BI.

Figure 1. Connectors in Power BI.

Again, Power Query uses Microsoft’s “M” language. If the user needs more functionality than what’s exposed through the Power Query Editor user interface, there’s the option to “tweak” a query using the Advanced Editor, or to just write a brand-new query using the Blank Query option.

The lightweight ETL capabilities within Power BI are a powerful addition to the self-service BI toolkit. For small and mid-size businesses, where skill sets may be limited, it provides a path to obtainable BI. For enterprises, it removes the IT barrier to timely reporting and analysis needed by the business and gives flexibility for the business to perform ad-hoc analyses. For both environments, it offers rapid prototyping that can be shared to convey business requirements with internal or external BI developers.

In future posts I’ll show how Power Query/“M” can be used to perform self-service ETL tasks.