Over the past few years there has been a growing momentum in Self-service BI. This momentum can no longer be ignored and is going to affect the way IT approaches and facilitates the way the enterprise works with data. Questions arise such as: How can IT embrace the self-service BI movement and ensure it is implemented in a successful, controlled environment? What role does IT play in self-service BI? What is the new data analysis and discovery process?
IT can no longer be the gate keeper to all corporate data. It is no longer practical or feasible to load data from the various sources available today into a centralized relational database. Business users need an agile way to analyze large amounts of data both structured and unstructured to gain a competitive edge. In the past specialized skills were needed to analyze and processes this data. It was simply impractical to expect the average business analyst to be able to do this. Seeing the need for an easier to use business intelligence toolset, the major vendors invested a great deal of time and money into this effort. Microsoft, Oracle, and Tableau, just to name a few, have all created some pretty compelling solutions.
When looking at a self-service BI toolset there are several requirements that are important for a successful adoption. First there needs to be an easy to use query tool that allows users to extract data from various data sources including relational and non-relational sources. Next the data needs to be managed and modeled so that it can be efficiently analyzed for trends and hidden insights. Finally there needs to be a graphical component that allows users to easily create reports and dashboards to convey the results in a simple digestible manor.
Microsoft has done a good job in providing a self-service BI toolset geared toward the business analyst whom is comfortable working in Excel (One of the most used data analysis tools on the market). At the core of the toolset is Power Pivot. Power Pivot provides a way to model and analyze large amounts of data (100 million rows) through the use of data compression and in memory analytics. It contains a robust formula engine that out performs tradition Excel formulas, while maintaining the look and feel of the Excel formula experience. The figure below shows a table of data in the Power Pivot model. Each tab represents a different table in the model.
To get the data into a Power Pivot model from many different data sources Microsoft has provided Power Query. This is a critical tool in the self-service BI stack. It allows Business Analysts the ability to discover and refine data from various sources in an intuitive consistent manner regardless of the data source. Once the data has been refined, cleansed, and aggregated it can then loaded into the Power Pivot model for analysis. The figure below shows a query being developed in Power Query.
Once the data has been loaded into the Power Pivot model it is ready to be analyzed. Using Data Analysis Expression (DAX) language you can add calculated columns, custom measures, and establish relationships between the various tables in the model. The figure below shows a measure defined using DAX in the Power Pivot model.
Once the model is completed you can use Excel pivot tables and charts for data discovery and analysis. Another option is to use Power View to create powerful interactive dash boards and reports to perform the analysis and share the results (using SharePoint or Office 365). The figure below shows a sales analysis dashboard in Power View.
As we empower business users with self-service BI tools, we need to look carefully at how self-service BI and corporate BI initiatives will coexist and complement one another. IT should embrace self-service BI as a cheap, agile, and efficient discovery and proof of concept development system. By providing business analysts early access to the raw data they can decide what is important and needs provisioning into the corporate BI system. They can also quickly develop throw-away analysis for one time initiatives that are not candidates for inclusion in the corporate BI system. The figure below shows the collaborative interaction between self-service BI and traditional corporate BI.
By providing business users toolsets and guidance to effectively implement self-service BI, IT will forge exciting new relationships and synergy with the business users. Development cycles will compress, data analysis will become more agile, and satisfaction will increase.