The term business intelligence (often known simply as BI) covers a wide range of analytical disciplines and technologies that enable business owners and analysts to acquire and transform corporate information and then produce actionable reports and data visualizations. These visualizations (which can take the form of dashboards, dashboards, graphs, maps, etc.) can be made available to stakeholders who need timely information to enable them to make better business decisions.
The need for self-service BI
Traditionally, when the term BI was used, it referred to enterprise-wide business intelligence, large projects and time scales, 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 makes companies more complex in their operational structures, the need for constantly updated reports has made the traditional BI model less than ideal. Entrepreneurs 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 IT professionals to focus on complex back-end development tasks by allowing users to create the analytic queries, reports, and visualizations they need. However, for it to be successful, it is important that the tools used are easy enough for a non-specialist to master. This is where Microsoft has a huge advantage over its competitors: they own Microsoft Excel.
Excel has an extremely wide user base and, due to its lightweight and flexible nature, is as relevant to an engineer as it is to a statistician; in short, everyone uses Excel. So it’s no surprise that Microsoft’s self-service BI tools build 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’s a cloud-based business product that, 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 colleagues.
Another key aspect of Power BI is the support it provides for users to access data through 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 analytical functionality through four key Microsoft Excel add-ins: Power Query, Power Pivot, Power View, and Power Map.
power query
The Power Query plugin 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 being retrieved. For example, you can create a query that combines data from two different sources; either taking some columns from source A and some from source B, or adding records from source B to the end of source A. Regardless of what transformations you apply to the query, every time the data source is updated, these same transformation rules always apply again.
Power Query even features its own query language, simply called M. As you use the query editor and apply transformations to your data, Power Query automatically generates the necessary statements. However, if you have the time and inclination to understand the syntax, you can also write your own M statements.
powerpivot
Power Pivot, which has been available as a standalone add-in since Excel 2010, is now integrated into Excel and plays a 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 define key performance indicators and hierarchies to use in pivot tables, making life easier for both you and colleagues who need these features for their reports.
PowerView
Power View is used for interactive exploration of your data and for creating visualizations, reports, and presentations. The Power View interface will be familiar to Excel users, as it closely resembles the interface used when creating PivotTables. Each report you generate is called a view, and it typically focuses on a particular aspect of your data using one of the available visualization types, such as a table, graph, or map.
Power View also features a full-screen presentation mode, much like a PowerPoint slide show, where navigation buttons let you move between views.
power map
The Power Map plugin provides 3D visualization of geographic and time-based data, either on a globe or a custom map. It also allows you to create walkthroughs that illustrate changes over time; for example, the gradual increase in sales in a particular country. These walkthroughs can be played in Excel or you can even save them as videos and upload them to your corporate YouTube channel.
It’s the custom map feature, in particular, that makes Power Map so useful; because this allows you to truly customize your visualizations. The custom map could show a store layout, a close-up of a product, the human body, a soccer field; whatever you need to use to illustrate your particular data.
In conclusion
The speed, flexibility, and democratization offered by self-service BI seem poised to 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 that Microsoft Excel plays in most people’s use of computers gives them an unmatched advantage over the competition.
