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 as a cloud-only product by Microsoft in 2015. Power BI comes with built-in data preparation and many innovative features and functions such as natural language query and machine learning driven “Quick Insights” for automated discovery. In mid-2017, Microsoft added the possibility of running the product on-premises with Report Server but with limited features and functions.
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 player 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. The suite is a bundle of integrated services based on technologies from the Microsoft Azure cloud offering. Power BI – a data discovery and dashboarding solution – is one of the core 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 and influencing 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. Microsoft implemented BI capabilities in SQL Server but also spread them across the Office and SharePoint product lines. For some time, Microsoft focused its BI strategy on attracting business users to Excel though additional and dedicated features.
Power BI was introduced in 2013 as a cloud-based BI package using Office 365 for publishing, collaboration and mobile delivery. Microsoft Excel played a core 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 dashboards and analysis) and Power BI Service (a web service 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 data from diverse internal or external sources.
Power BI is strongly targeted at business users. It is equipped with connectors to a broad number of data sources and a natural language processing engine (NLP). Power BI supports building reports using predefined visualizations as well as integrating custom visualizations. The vendor provides APIs to integrate Power BI visualizations in custom applications with Power BI embedded. Microsoft included further innovative features besides NLP in Power BI. Quick Insights automatically analyzes data sets for patterns and outliers and provides the user with appropriate 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 use. Licenses can be purchased for advanced features such as collaboration and sharing (Power BI Pro) and extra data and computing capacity (Power BI Premium). Microsoft relies on its partners to sell and implement its solutions as well as develop complementary technologies (e.g., planning solutions) more than most other BI vendors.
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 Salesforce, Marketo, SAP 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.
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. These capabilities are often used to leverage advanced analytics functions such as clustering or smoothed forecasts missing in the product.
Power BI offers two distinct connection types: direct access to existing data sources (e.g., for Analysis Services models) and cached access via Tabular models built in Power BI. Reports, dashboards and analysis can be created upon both connection types. Only cached models offer the complete functionality for data preparation and analysis. Data preparation and model generation can only take place in the full client (Power BI Desktop). To build data sets, users have the ability to create dimensions, calculated fields and measures, KPIs, hierarchies and other business metadata. For advanced calculations, the Excel-based DAX language must be used, which makes it more complex to perform data preparation compared to some competing products. Data from multiple sources can be combined into a single data set. A common semantic layer spanning all data sets is not available. Data sets can be shared and reused via Power BI Service. The service also allows business users to load prepared data from multiple sources to create a new dataset, but without further preparation.
Reports, visualizations and datasets can also be published using Power BI Service. The web client for Power BI Service is the main front end for information consumers. They use the service to access and work with published content and to collaborate with other users. They can also connect to reports and dashboards via mobile apps (Power BI for Mobile) for iOS and Android. On-premises content distribution through Report Server does not support Power BI’s full range of capabilities: the complete functionality is currently only available in the cloud.
Power BI is a business-oriented solution. 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. Microsoft assists users in preparing their data with the “Column from Examples” feature that recognizes patterns and creates the required scripts from examples entered by the user.
Business users can create reports based on their data sets or live data in many 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 could be predefined and published using Desktop or live data made available centrally or loaded up directly to the web with the restriction of not being able to prepare the data.
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 developer platform for custom visuals. These visualizations can be published to a 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 with the “Q&A” feature.
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).
User & Use Cases
The number of large companies using Power BI has continued to rise in this year’s BI Survey, and the mean number of users within customer companies has also increased to an above-average level. The usage patterns of Power BI have shifted from predominantly data discovery and analysis use to distributing (viewing, navigating) standardized content in dashboards and standard reports.
Current vs. planned use
5 products most often evaluated in competition with Microsoft Power BI
Percentage of employees using Microsoft Power BI
Number of users using Microsoft Power BI
Tasks carried out with Microsoft Power BI by business users
Company size (number of employees)