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 multiple 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.
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 offers a vast range of enterprise software and cloud products 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 high double-digit growth rates. AI and machine learning have also become increasingly important in product development.
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. 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.
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. Only cached models offer the complete functionality for data preparation and analysis. Data preparation and model generation necessary to create data sets for consumption can only take place in the full client (Power BI Desktop). Analyses, reports and dashboards can be created upon the data sets produced or live data. 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 Power BI 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 must be used.
Reports, visualizations and datasets can be published using Power BI Service. Power BI Service also offers a web client that can be used for viewing and publishing content as well as for collaboration. Users can connect to reports and dashboards via mobile apps (Power BI for Mobile) from 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. These capabilities are often used to leverage advanced analytics functions such as clustering or smoothed forecasts missing in the product.
The on-premises content distribution through Report Server does not support Power BI’s full range of capabilities, so the complete functionality is currently only available in the cloud.
Power BI is a business-oriented solution, 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.
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 usage patterns of Power BI have shifted from data discovery and analysis centric use to distributing (viewing, navigating) standardized content in dashboards and standard reports. Power BI Installations typically serve a below average number of users, nevertheless a tendency for larger companies to implement Power BI can be observed.
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)