The term business intelligence (usually known simply as BI) covers a wide range of analytic disciplines and technologies which allow business owners and analysts to acquire and transform corporate information, and then to produce 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 in need of timely information to enable them to make better business decisions.
The need for self-service BI
Traditionally, when the term BI was used, it meant enterprise-wide business intelligence, major projects and timescales, and significant costs. The tools used in BI projects were expensive and required a fair amount of technical expertise to implement.
As the explosion of online business transactions make businesses more complex in their operational structures, the need for constant up-to-date reporting has made the traditional BI model less than ideal. Business people now need to be able to access corporate data and produce the reports and visualizations they need, without having to rely on IT specialists. The solution to this dilemma has been the development of self-service BI; the DIY approach to business intelligence.
The self-service BI model frees up IT professionals to focus on complex back-end development tasks by allowing users themselves to create the analytical queries, reports and visualizations they need. However, if it is to be successful, it is important that the tools which are used be fairly easy for a non-specialist to master. This is where Microsoft have a huge edge over their competitors: they own Microsoft Excel.
Excel has an extremely wide user base and, because of its lightweight and flexible nature, it is as relevant to an engineer as to a statistician; in short, everybody uses Excel. It is therefore not surprising that Microsoft’s self-service BI tools are based around the core functionality of Microsoft Excel.
Microsoft Power BI
The Microsoft product which best fits the self-service BI label is called Power BI for Office 365. It is a cloud-based enterprise product which, as the name suggests integrates with the cloud version of Microsoft Office. It uses a SharePoint back-end, with the concept of Power BI sites, to enable collaboration and allow users to easily share reports and visualizations with their colleagues.
Another key aspect of Power BI is the support it provides for users to access data using a variety of devices. Basically, any device that supports HTML 5 can now be used to view a Power BI site.
Power BI provides its core analytic functionality via four key Microsoft Excel add-ins: Power Query, Power Pivot, Power View, and Power Map.
The Power Query add-in allows you to connect to a wide variety of data sources both within your organization and online; and design very flexible and sophisticated rules for the transformation and refinement of the data that is being retrieved. For example, you might create a query which 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, these same transformation rules will always be reapplied.
Power Query even features its own query language, simply called M. As 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 the inclination to get your head around the syntax, you can also write your own M statements.
Power Pivot, which has been available as a separate add-in since Excel 2010, is now integrated into Excel and plays the central role in Microsoft’s self-service BI solution. It allows you to create a data model, using data from disparate sources, either imported using Power Pivot itself or using Power Query.
Power Pivot allows you to create relationships between different tables, as well as defining key performance indicators and hierarchies for use in pivot tables, making life easier both for yourself and for colleagues needing these features for their reports.
Power View is used for interactive exploration of your data and for creating visualizations, reports and presentations. The Power View interface will seem familiar to Excel users, since it closely resembles the interface used when creating pivot tables. Each report you generate is called a view and typically focuses on one particular aspect of your data using one of the available visualization types, such as a table, chart or map.
Power View also features a full screen presentation mode, very similar to a PowerPoint slide show, where navigation buttons allow you to move between the various views.
The Power Map add-in provides 3-D visualization of geographical and time-based data, either on a globe or on a custom map. It also allows you to create tours which illustrate changes over time; for example, the gradual increase in sales in a particular country. These tours can be played in Excel or you can even save them as videos and upload them to your corporate YouTube channel.
It is the custom map feature, in particular, that makes Power Map so useful; because this allows you to truly personalize your visualizations. The custom map could show the layout of a store, a close-up of a product, the human body, a football pitch; anything that you needed to use to illustrate your particular data.
The speed, flexibility and democratization offered by self-service BI looks set to make it the norm, gradually replacing slower and more expensive enterprise-wide BI projects. While Microsoft are not the only players in the self-service BI market, the central role played by Microsoft Excel in most people’s computer usage gives them an unrivalled edge over the competition.