Microsoft BI: From Excel to Power BI

Gartner's latest Magic Quadrant for Business Intelligence software ranks Microsoft as the Leader. Here's how their Power BI solution emerged as the evolution of Excel, along with some clarity on the various components and product names which Microsoft has been using. 

In 1985, Madonna was everywhere- on the radio, in the movies, and inside record stores on that new disruptive technology called compact discs. Back to the Future was the biggest movie of the year. Each Thursday night nearly 30 million households spent the evening watching the Cosby Show, Family Ties, Cheers and Night Court. It was also the year a ten-year-old Microsoft introduced a humble little program called Excel to the world. The story of Microsoft's rise to the top of the Business Intelligence software game with their Power BI product begins with Excel.

If there were a Mount Rushmore for software, Excel would make the cut. Of course, the real Mount Rushmore is a monument for past great, long dead presidents. So one may think BI, Big Data and Analytics would have made Excel little more than a flashback to the 80's and 90's. Think again. Estimates for total Excel users worldwide range from 400 to 750 million. Almost every organization around the globe still does some portion of their data analysis on Excel. It's fair to say even companies providing the most advanced BI and Analytics tools today still probably use Excel internally, although they may not want to mention it.

"But Does it Export to Excel?"

That's a question that just doesn't go away. It comes up after a $ 10 million implementation of an ERP system.  It comes up after 'going-live' with the latest advanced data visualization and dashboard products. To their credit, most software makers have the foresight to make sure they provide a seamless export to Microsoft's ubiquitous spreadsheet.  Why is Excel the most popular tool for data analysis? I think there are two reasons for its continued embrace throughout organizations both small and large, and across industries. The first has to do with the features of the product, and the second has to do with the features of our brain.

Excel's Strength

Being around for 32 years and growing to 750 million users is a result of Excel's features and strengths, but it's also a strength in itself. That growth and scale means a continuous investment from Microsoft in improvements, as well as a universal familiarity where network effects keep adding value as more users join in.

The basic functions of Excel are intuitive without requiring any training. A user without any previous IT or software experience can learn the more sophisticated features. The costs are minimal. When we join a company it's unlikely that Excel isn't already there and in use. There's no need for an "Excel Implementation." You don't hear that "Excel will only be in next year's budget." It's a $ Hundred tool involved in many $ Billion decisions. It's used to make major decisions in finance, government, science, business and in our everyday life. If only I had it in the 1970s to calculate the future value of the comic books that I would leave around and eventually lose.

Our Brain May Prefer Excel

Our brains desire challenges. It needs to be taken out of its comfort zone sometimes. This desire may also apply to working with data. Analytics isn't about the answers; it's about the questions. So dashboards with advanced visualizations can sometimes be counterproductive as they don't require us to interact and genuinely understand the data. The Cincinnati School District, in a past Allari blog, provides a great story and an example of how data sat unused until it got messey and the users got their hands dirty.

"A wise man can learn more from a foolish question than a fool can learn from a wise answer." - Bruce Lee

The term for this phenomenon is cognitive disfluency. The simplest illustration of this is when we are shown a statement written with mixed up fonts and sizes we pay more attention to it and have a higher accuracy in interpreting it. Of course that extra time spent isn't usually worth the gain so we don't use it that way. But seeing data in a slightly more raw version, such as Excel, may force us to understand it more deeply. Answers tend to lead to questions and analysts like to analyze to find answers. So perhaps that's why Excel lives on and people keep asking, "but does it export to Excel?" 

But Excel Does Have Limitations

With more and more data available Excel's limitation as a BI tool is more visible.  Excel has a limit of about a 1 million rows of data.  Even with large data sets less than a million rows, Excel uses a lot of RAM to crunch those rows and can take a long time to load. Using data from multiple data sources become a manual process of getting the data into Excel. All this work to create that perfect set of data would then have to be repeated to get a new set of current data.  As a result, Excel on its own was not going to be the BI tool of choice for many common use cases.

RELATED: Building A Winning Organization Through Data-Driven Decision Making >>>

Power Pivot, Power Query, Power View,...

With Excel2010, Microsoft began to address these limitations to create a supercharged Excel. PowerPivot became an add-on feature for Excel2010. PowerPivot added a local instance of Microsoft Analysis Services (MAS) to the Excel Workbook allowing users to build ROLAP models.  Also, PowerPivot uses the VertiPaq compression engine to hold the data in memory on the client computer. This 90% compression is what allows Excel to go to unlimited rows or records rather than the native 1 million.  PowerPivot can directly link to many data sources to bring in the data.

MS PP and PQ and Visuals.jpg

Power Query became an add-on which enabled the feature to transform data in between the original data sources and feeding PowerPivot. These data transformations could include tasks like:

  • Remove columns, rows, blanks
  • Convert data types – text, numbers, dates
  • Split or merge columns
  • Sort & filter columns
  • Add calculated columns
  • Aggregate or summarize data
  • Find & replace text
  • Unpivot data to use for pivot tables

All the above are time consuming tasks that can now be automated via Power Query. 

Power View became the third add-on that supercharged Excel even more with a powerful visualization technology. It provided interactive maps and charts with more smarts than can be done with native Excel functionality.  In Excel2016 all three are native to Excel making it truly a strong BI tool that has helped Microsoft become the Leader according to Gartner.

MS BI Gartner Chart-1.jpg


MS Power BI

In 2015 Microsoft Power BI became Microsoft's BI as a service software/platform. Power BI under the hood is a combination of the engines Power Pivot and Power Query along with the visualization even more advanced then Power View.  Power BI Desktop is the means to build the models and has a similar UI as Power Pivot on Excel. 


Excel and Power BI.jpg

Instead of publishing with Excel, Power BI publishes to the cloud.  In this model, the Power BI Desktop is the tool the analyst would use to ask questions and find answers and then publish and communicate those insights via web. Data can be refreshed to the cloud up to 48 times per day, and there is a 10 GB maximum without using a new Premium version. Using this cloud version will increase analytical capabilities through the use of MS capabilities in machine learning and voice recognition. These two together may allow simple straight forward questions about data to take the place of more advanced analytic skills in the future.  

Power BI and Excel

Power BI has evolved from Excel add-ons into a total BI SasS solution which keeps getting better. At some point, Microsoft will want Power BI to be the primary choice as Power Pivot in Excel is virtually free. In my opinion, for customers just beginning the journey with BI, Power Pivot in Excel is a great entry point. It's free, and the implementation is quick and easy.

Analytics is more than the software. It's the people, skills, and processes to go from data to insight. Asking the right questions, developing a hypothesis and determining what data may help confirm the hypothesis is not provided by any BI tool. So building these processes and skills using a BI tool that you probably already have can lead to an easy transition into Power BI as its capabilities grow in the future. But even after moving into Power BI, you're still likely to get the question, "but, does it export to Excel? ".