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.
Compared to the huge business the company does in a range of different markets, its BI front-end revenues are still 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.
Microsoft has been a provider of BI and data management products for a long time. The vendor started with a bundled offering called SQL Server, which many companies used to build a central data warehouse. Currently, more and more customers rely on the corresponding offering in Azure, Synapse Analytics.
As requirements and technology in the BI and analytics area evolved, Microsoft began to focus more on the cloud as the major deployment option for its portfolio. In order to ship cloud-native products, the vendor began to redesign its own components and introduce new products more suitable for the new deployment form. With the Azure cloud infrastructure, Microsoft began to not only offer a cloud platform for its technology partners but also used it for its own offerings. At that point, its cloud and on-premises portfolios began to diverge.
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. Further innovative features in Power BI include Quick Insights, which 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 is a front end for building reports and dashboards on various data sources. It also has functions for data preparation in the Power Query Editor module. It offers two distinct connection types: direct access to existing data sources (e.g., for SQL Server, Analysis Services models, SAP BW and others) and cached access via tabular models built in Power BI data sets. Reports, dashboards and analyses can be created using either connection type. Only cached models offer the product’s 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 data set, but without further preparation.
Power BI includes more than 100 connectors for various data sources, such as relational and multidimensional databases including SQL Server, Azure SQL Database, Analysis Services, Oracle Database, IBM DB2, SAP HANA, Amazon Redshift, Teradata and Snowflake. It also offers connectivity to other databases via ODBC and OLE DB. Furthermore, the product can natively connect to big data systems such as SPARK, HDFS and Impala but also to on-premises or cloud-based business applications including Salesforce, Marketo, MicroStrategy and Microsoft Dynamics. For the latter group, an increasing number of content packs are available that can serve as templates to quickly build analytics solutions on top of these applications.
Reports, visualizations and data sets 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.
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. 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, which 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 directly to the web (with the restriction of not being prepared). Power BI reports can be shared with other users through Power BI Service or embedded in other applications using the APIs.
Power BI supports a set of visualization types such as range, scatter, donut, polar, treemap, sunburst, gauges, KPI, maps and more. In the June release, for example, the decomposition tree was added. With this graphic, a user can understand the root cause contributing to a high or low KPI. This graphic type was already available in ProClarity Analytics, a tool that was acquired by Microsoft in 2006. 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.
User & Use Cases
The number of large companies using Power BI has continued to rise in this year’s BI & Analytics Survey, and the mean number of users within customer companies has also increased to an above-average level. The pattern of use of Power BI has shifted from predominantly data discovery and analysis to distributing (viewing, navigating) standardized content in dashboards and standard/enterprise 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)