The term business intelligence (usually known simply as BI) encompasses a wide range of analytical disciplines and technologies that enable business owners and analysts to capture and transform company information and then create useful reports and data visualizations. These visualizations (which can take the form of dashboards, scorecards, charts, maps, etc.) can then be made available to stakeholders who need timely information so they can make better business decisions.
The need for self-service BI
When the term BI was used traditionally, it meant enterprise-wide business intelligence, large projects and schedules, and significant costs. The tools used in BI projects were expensive and required a lot of technical know-how to implement.
As the explosion of online business transactions makes companies more complex in their operational structures, the need for up-to-date reporting has made the traditional BI model less than ideal. Business people now need to be able to access enterprise data and create the reports and visualizations they need without relying on IT specialists. The solution to this dilemma was the development of self-service BI; the DIY approach to business intelligence.
The self-service BI model frees IT professionals to focus on complex back-end development tasks by allowing users to build the analytical queries, reports, and visualizations they need themselves. In order to be successful, however, it is important that the tools used are relatively easy for a layperson to master. This is where Microsoft has a big advantage over its competitors: They own Microsoft Excel.
Excel has an extremely wide user base and its lightweight and flexible nature makes it as relevant to an engineer as it is to a statistician. In short, everyone uses Excel. It is therefore not surprising that Microsoft’s self-service BI tools are based on the core functionality of Microsoft Excel.
Microsoft Power BI
The Microsoft product that best fits the self-service BI label is called Power BI for Office 365. It is a cloud-based enterprise product that, as the name suggests, morphs into the cloud version of Microsoft Office integrated. It uses a SharePoint backend with the concept of Power BI websites to enable collaboration and allow users to easily share reports and visualizations with their colleagues.
Another important aspect of Power BI is the support it offers users to access data from a variety of devices. Basically, any device that supports HTML 5 can now be used to view a Power BI site.
Power BI exposes its core analytics capabilities through four major Microsoft Excel add-ins: Power Query, Power Pivot, Power View, and Power Map.
With the Power Query add-in, you can connect to a variety of data sources both within your organization and online. and design very flexible and sophisticated rules for the transformation and refinement of the retrieved data. For example, you could create a query that combines data from two different sources; Either by taking some columns from source A and some from source B, or by appending records from source B to the end of source A. Whatever transformations you apply to the query, each time the data source is refreshed, the same transformation rules apply and are reapplied.
Power Query even has its own query language, simply called M. When you use the query editor and apply transformations to the data, Power Query automatically generates the necessary statements. However, if you have the time and inclination to familiarize yourself with the syntax, you can write your own M statements.
Power Pivot, available as a separate add-in since Excel 2010, is now integrated with Excel and plays the central role in Microsoft’s self-service BI solution. It allows you to create a data model using data from different sources, imported either with Power Pivot itself or with Power Query.
With Power Pivot, you can create relationships between different tables and define key performance indicators and hierarchies for use in pivot tables, making life easier for both you and colleagues who need these features for their reports.
Power View is used to interactively explore your data and create visualizations, reports, and presentations. Power View’s user interface will look familiar to Excel users as it closely resembles the user interface used when creating pivot tables. Each report you generate is called a view and typically focuses on a specific aspect of your data using one of the available visualization types, e.g. a table, chart, or map.
Power View also offers a full-screen presentation mode, very similar to a PowerPoint slideshow, where you can use navigation keys to switch between the different views.
The Power Map add-in provides 3D visualization of geographic and time-based data, either on a globe or on a custom map. You can also create tours that show changes over time; for example, the gradual increase in sales in a particular country. These tours can be played in Excel or even saved as videos and uploaded to your company’s YouTube channel.
The custom map feature in particular is what makes Power Map so useful. because this is how you can really personalize your visualizations. The custom map could show a store layout, a close-up of a product, the human body, or a soccer field. everything you needed to use to visualize your particular data.
The speed, flexibility, and democratization that self-service BI offers should make it the norm, gradually replacing slower and more expensive enterprise-wide BI projects. While Microsoft isn’t the only player in the self-service BI market, the central role Microsoft Excel plays in how most people use their computers gives them an unrivaled advantage over the competition.