Microsoft Power BI
Power BI is a cloud-based BI product designed for business users to access and combine different data sources for data discovery, visualization and interactive dashboards. It was released by Microsoft in 2015 initially as a cloud-only product. Power BI comes with built-in data preparation and multiple innovative features and functions such as natural language query and machine learning driven “Quick Insights”. In mid-2017, Microsoft delivered the possibility of running the product on-premises with Report Server.
Microsoft is the world’s largest software company. Founded in 1975 and headquartered in Redmond, it has become a household name, primarily due to its Windows operating system and Office suite. Aside from these products, Microsoft has a vast range of enterprise software and cloud offerings including its own database, browser, various servers and ERP solutions.
In recent years, Microsoft has focused its business on cloud-based solutions such as Azure with double-digit growth rates. AI and machine learning have also become increasingly important in product development. Microsoft completed its acquisition of the business social network LinkedIn towards the end of 2016.
Compared to the huge business the company does in a range of different markets, its BI revenues are relatively small. Nevertheless, Microsoft is a strong presence in the BI market and its offering is strategic to complement existing solutions and to drive cloud revenues.
In the past, the vendor spread its BI capabilities across the Office, SharePoint and SQL Server product lines, providing tools for formatted reporting, analysis and dashboards. However, in 2015 Microsoft released a dedicated BI and analytics product portfolio called Cortana Intelligence Suite. Power BI – a data discovery and dashboarding solution – is one of the components of this suite.
Microsoft’s development efforts in the BI area date back to 1996 when it purchased OLAP technology from Panorama. Within a few years Microsoft moved from being a surprising entrant into the OLAP market to become a clear leader setting standards for the entire market. Microsoft bundled its main database technologies in its SQL Server portfolio, which many companies use today to build a central data warehouse.
Besides SQL Server, Microsoft spread its BI capabilities across the Office and SharePoint product lines. For some time, part of Microsoft’s BI initiative has been to focus strongly on the spreadsheet solution Excel – and in turn on business users. Accordingly, the vendor has regularly added more BI functionality to the solution.
Power BI was first introduced in 2013 as a cloud-based BI package using Office 365 for publishing, collaboration and mobile delivery. Microsoft Excel played a dominant role in the Power BI offering and received functional extensions, such as Power Query, Power Pivot and others.
In July 2015, Microsoft launched a new generation of its Power BI product line consisting of Microsoft Power BI Desktop (a full client for ad hoc reporting, dashboards and analysis) and Power BI Service (a web client for content publishing and sharing). In 2017, Power BI Report Server was released to enable the on-premises distribution of Power BI reports.
Power BI is an important pillar in Microsoft’s offerings for machine learning, advanced analytics and data science. Its integration into Azure cloud allows Power BI to scale in global organizations, reaching a high number of users and analyzing substantial amounts of data from IoT devices, for example.
Power BI is strongly targeted at business users. It is equipped with connectors to different data sources and a natural language processing engine (NLP). As well as building reports using predefined visualizations, Power BI supports the building and integration of custom visualizations. With Power BI Embedded, the vendor also provides APIs to integrate Power BI visualizations in custom applications. Besides NLP, Microsoft included some other innovative features in Power BI.
Quick Insights automatically analyzes data sets for patterns and outliers and provides the user with visualizations based on relevant findings. The related Insight feature retrieves information about patterns in the data behind a specific visualization or dashboard object.
Power BI Service and Power BI Desktop are free to download. Users can purchase Power BI Pro licenses for advanced features such as collaboration and extra data capacity. More than other BI vendors, Microsoft relies on its partners to develop complementary technologies (e.g. planning solutions) as well as sell and implement its solutions.
Report Server does not support Power BI’s full range of capabilities so the complete functionality of Power BI is currently only available in the cloud. When signing up to the cloud service, Power BI creates a user environment and administration takes place in the Power BI portal.
Power BI includes native connectors for relational and multidimensional databases including SQL Server, Azure SQL Database, Analysis Services, Oracle Database, SAP HANA, Amazon Redshift and Teradata, and offers connectivity to other databases via ODBC and OLE DB. Furthermore, the product can natively connect to big data systems like SPARK, HDFS and Impala but also to on-premises or cloud-based business applications like Marketo, SAP, Salesforce and Microsoft Dynamics. For the latter group, an increasing number of content packs is available that can serve as templates to quickly build analytics solutions on top of these applications.
Data preparation and model generation, necessary to create data sets for consumption, can only take place in the full desktop client. Analyses, reports and dashboards can be created upon the data sets produced. A common semantic layer, spanning all data sets is not available. Data from multiple sources can be combined into a single data set. Data sets can be reused via Power BI Service and can be built by business users.
Data preparation in SSRS is not as easy to perform as it is with some competing products due to the need for DAX scripting for advanced functions. The data models allow the creation of dimensions, calculated fields and measures, KPIs, hierarchies, and other business metadata. For advanced calculations, the Excel-based DAX language has to be used. Data can also be imported to Power BI’s own in-memory column store engine (VertiPaq) to provide instant analytics.
Reports, visualizations and datasets can be published using Power BI Service. Power BI Service also offers an HTML5 client that can not only be used for viewing and publishing content but also for collaboration purposes. Users can connect to reports and dashboards via mobile apps (Power BI for Mobile) for iOS and Android.
Besides its predefined features, Power BI has a set of APIs based on the REST protocol. Customers can use these to build custom visualizations and integrate them into the solution. Power BI also allows the embedding of visualizations in individual applications.
Power BI was designed as a business-oriented solution. Its full-client Power BI Desktop includes capabilities for data preparation built on Microsoft’s Power Query technology, and data modeling is built on existing Power Pivot technology. Users can connect to different data sources using native connectors or OLEDB and ODBC. Data can be transformed, cleansed and enhanced to create a single data set out of multiple data sources.
Business users can create reports based on their data sets in a number of ways. Firstly, they can be built in Power BI Desktop and turned into templates for reuse if needed. Another option for reuse is to transfer reports into an organizational content pack. Business users can also create a new report from scratch using the online Power BI Service based on the data available to them. This data should be predefined and published by a power user using Power BI Desktop.
Power BI supports a comprehensive set of visualization types such as column, bar, line, area, range, scatter, pie, donut, polar, treemap, sunburst, gauges, KPI and maps. In addition, Power BI offers a full developer platform for custom visuals based on the D3JS platform. These visualizations can be published to a public visualization gallery to make them available to other users. To analyze the data sets, natural language queries can be used to effortlessly produce meaningful visualizations.
Power BI has several built-in maps based on Bing. The integration with Bing allows Power BI to correctly plot spatial data based on the names provided rather than requiring latitude and longitude to be included in the data sets. Maps in Power BI are highly customizable and allow drilling up and down. The product also offers trigonometric functions that can be used for calculations (e.g. distances).
Power BI can run R scripts and import the resulting data into Power BI data sets. Besides R integration, Power BI’s inbuilt expression language provides statistical functions such as chi-squared distribution, confidence intervals and beta distribution. Furthermore, users can connect to Azure ML and leverage the machine learning platform to process data and retrieve the results.
Power BI reports can be shared with other users using Power BI Service or embedded in other applications using the APIs. Printing features are built into the platform. However, unlike with SQL Server Reporting Services, Power BI cannot be used to design print-oriented reports. It is also possible to embed Power BI visuals directly in PowerPoint using add-ins for Office.
User & Use Cases
Microsoft Power BI deployments show a fairly common distribution in terms of customer size despite largely relying on provisioning via cloud, unlike most comparable tools. Installations typically serve a below average number of users.
In line with usage of other data discovery oriented offerings, more Power BI users model and enrich (i.e. prepare) their data than users of other solutions. Accordingly, basic data analysis and dashboarding (i.e. condensing multiple analyses on a single screen) are among the most common application areas.
Current vs. planned use
N = 109
5 products most often evaluated in competition with Microsoft Power BI
N = 105
Percentage of employees using Microsoft Power BI
N = 107
Number of users using Microsoft Power BI
N = 107
Tasks carried out with Microsoft Power BI by business users
N = 104
Company size (number of employees)
N = 108
Want to see the whole picture?
BARC’s Vendor Performance Summary contains an overview of The BI Survey results based on feedback from Microsoft Power BI users, accompanied by expert analyst commentary.Purchase the Vendor Performance Summary