Suite B1 | G&C Apartments | Plot 1314 | Church Road | Ntinda | P.O.Box 72663 | Clock Tower | Kampala | Uganda+256(39)321-5149 +256(77)581-9896email

Adding Business Intelligence to Microsoft Excel Analytics

Adding Business Intelligence to Microsoft Excel Analytics

PowerPivot and Power View

Good day folks,

In my classes and workshops over the last few months, one major requirement was highlighted by participants:

How do we use Microsoft Excel to analyse huge volumes of data?

I was, therefore, tempted to Start a new series of articles titled “Adding Business Intelligence to Microsoft Excel Analytics”

Today, I will simply navigate the history of a powerful tool that not only brings the power of handling big data to Excel but also empowers Excel to be a business intelligence tool.

Excel and Data Analysis

Microsoft Excel is the world standard for performing data analysis. Its ease of use and power make the Excel spreadsheet the tool that everybody uses, regardless of the kind of information being analysed.

You can use Excel to analyse your personal expenses, your current account information, your customer information or a complex business plan, or even your weight-loss progress during a hard-to-follow diet. The possibilities are infinite—I am not even going to try to start enumerating all the kind of information you can analyse with Excel. The fact is that if you have some data to arrange and analyse, Excel will be the perfect tool. You can easily tabulate data, update it, generate charts, PivotTables, and calculations based on that data, and make forecasts with relatively limited knowledge of the software. You can also use Excel on mobile devices like tablets and smart phones, too, using cloud based technology over the internet and have anytime access to your information. Earlier versions of Excel had a limit of 65,536 records per worksheet but due to customer demand, Microsoft was inclined to increase this limit to 1 million records in Excel 2007. This is a clear indication that users want Excel to store and analyse large amounts of data.

Excel and Business Intelligence

Besides data analysts, there is another category of people dedicating their professional lives to data analysis: business intelligence (BI) professionals.

BI is the science of getting insights from large amounts of information.

In recent years, BI professionals have learned and created many new techniques and tools to manage systems that can handle the range of hundreds of millions or even billions of rows. BI systems require the effort of many professionals and expensive hardware to run. They are powerful, but they are expensive and slow to build, which are serious disadvantages.

Before 2010

Before 2010, there was a clear separation between the analysis of small and large amounts of data: Excel on one side and complex BI systems on the other. A first step in the direction of merging the two worlds was already present in Excel because the PivotTable tool had the ability to query BI systems. By doing that, data analysts could query large BI systems and get the best of both worlds because the result of such a query can be put into an Excel PivotTable, and thus they could use it to perform further analysis.

In 2010

In 2010, Microsoft made a strong move to break down the wall between BI professionals and Excel users by introducing xVelocity, a powerful engine that drives large BI solutions directly inside Excel. That happened when Microsoft SQL Server 2008 R2 PowerPivot for Excel was released as a free add-in to Excel 2010. The goal was to make the creation of BI solutions so easy that Excel would start to be not only a BI client, but also a BI server, capable of hosting complex BI solutions on a notebook. They called it self-service BI.

Microsoft PowerPivot has no limits on the number of records it can store: if you need to handle 100 million records, you can safely do so, and the speed of analysis is amazing.

PowerPivot also introduced the DAX language, a powerful programming language aimed at creating BI solutions, not only Excel formulas. Finally, PowerPivot is able to compress data in such a way that large amounts of information can be stored in relatively small workbooks. But this was only the first step.

In 2013

The second definitive step to bring the power of BI to users was the introduction of Excel 2013. PowerPivot is no longer a separate add-in to Excel; now it is an inherent part of the Excel technology and brings the power of the xVelocity engine to every Excel user. The era of self-service BI started in 2010, and it advanced in Excel 2013.

Because you are reading this introduction, you are probably interested in joining the self-service BI wave, and you want to learn how to master PowerPivot for Excel. You will need to learn the basics of the tool, but this is only the first step. Then, you will need to learn how to shape your data so that you can execute analysis efficiently: we call this data modelling. Finally, you will need to learn the DAX language and master all its concepts so you can get the best out of it. If that is what you want, then this is the right place.

In my next article, we shall get our hands dirty and perform some real analysis of data using this Powerful tool.