Over the years I have lost count how many times I have heard the mantra “Excel is just a toy you can’t use for serious Business Intelligence”. Typically from “IT experts” that never had to work with a BI solution from an end user perspective.

In part this statement is true: Excel on its own is and was never designed to be a store for large data volumes required for BI purposes. An area for which it is still often misused in poorly designed spreadsheet convolutes.With the right analytical technology by its side though, this tool is still one of the most flexible BI front ends on the planet. Not to mention that there is a pretty knowledgeable base of an estimated 300m users around, that don’t have to be trained in yet another standalone BI client.

From the experience of 15 years in this industry and more than a hundred BI projects I can pretty confidently say, that for most business intelligence use cases, users will likely interact with a spreadsheet at some stage of the process. The notion that Excel has an important role to play and should be considered as part of a professional business intelligence solution is by now –despite initially different opinions- also shared by Gartner and other analysts (More here and here)

Separation of data storage and presentation

The crucial factor is to clearly define the boundaries of how Excel is used. Anyone working in this area will be familiar with the typical “Excel Hell” scenario: a convolute of unmanageable workbooks that are error prone and don’t really offer useful insight.The key to avoid these issues in a business intelligence context is in our opinion the separation of data storage and presentation layer: data should not be pasted directly from a data source (or even worse rekeyed) into a spreadsheet but made interactively accessible from consistent, “single version of the truth” data mart(s) that also include as much as possible key parts of the logic (especially calculations).

Complementary Excel Business Intelligence add-ins like Jedox, TM1 and most recently Power BI (xVelocity engine) exactly address this problem by providing central, server based data storage technologies optimised for analytic purposes and governed by professional multi user management capabilities. For example enabling that on the same report (or Excel spreadsheet) specific users can only view or change data cells (e.g. by account, department, etc.) based on their credentials (an area where Excel on its own falls crucially short). At Managility we have worked with all of these and believe they are great enablers of tremendously useful business intelligence solutions that typically cater for most analytical application requirements that involve “power users” in their familiar spreadsheet environment. Each one of these solutions have particular strengths and weaknesses and need to be evaluated based on the specific customer requirements. Of course there will always be use cases where a more controlled approach for non-Excel users is required but these days the above mentioned solutions offer extensions that enable publishing data as protected, interactive web applications or on mobile devices.

Exce(l)ptional capabilities added since 2010

The capabilities of Excel as a “serious BI frontend” have dramatically increased in recent times. Particularly since version 2010 new “game changing” elements -from a business intelligence point of view- have been added. Most important to mention here is “PowerPivot” and subsequently Power BI. A free Excel add-in -respectively with Power BI a separate desktop tool- developed by Microsoft that works in conjunction with Excel version from 2010.
PowerPivot/ Power BI provide the modelling environment to generate and use data models that are deployed using the Microsoft xVelocity in-memory technology. The key benefits of the technology are its ability to process staggering amounts of data (there are many reports from people analysing 100m records on their laptops) with lighting fast response times. In addition to better processing the other key focus is on data integration.
The enabler for this is Microsoft’s new BISM (Business Intelligence Semantic Model) a framework to better integrate different sources and data store technologies beyond the boundaries of internally produced data. For example by adding weather information from an external provider to analyse effects on internal sales data. With Excel version 2013 Microsoft has also added additional business intelligence features like drill down and drill across in PivotTables (a feature that also works with any complementary OLAP technology that supports ODBO) as well as amazing geographic analysis with Power Map (formerly GeoFlow) Excel Add-in. In this video we take a initial look in conjunction with randomised parking meter data from the City of Sydney. The vision, supported by great demos, is that this process is driven entirely by business end users. Here we are a bit less confident. Despite significant advances, we believe that this can work in limited areas but caution is required. Data integration particular across different sources will always be a complex and challenging subject that will likely require specific technical domain expertise.

The Best of both worlds

Microsoft’s business intelligence back end engines (like Power BI, Power Pivot and Analysis Services) are focused on read only analysis. In our client projects we have developed a variety of approaches that address this issue particularly with utilising memory based processes to handle extensive write back processes (e.g. data entry and simulation on aggregated levels e.g. all products and distribution according to typical in budgeting scenarios.
Please don’t hesitate to contact us to arrange a hands on experience of the new options in one of our Fast Start Workshops or the new Advanced Business Intelligence with Excel workshop. All in all, the improvements in Excel are definitely a huge leap forward and will pose a substantial threat to currently very popular, read only analysis tools like Tableau and Qlikview whose price tag will be closely analysed by organisations who have already invested in Microsoft licenses.