Why Excel is no longer top. Evolution from Excel to BI systems

It does not take much time to explain what excel is, why it is needed and how it is useful. This, if I may say so, All-on-one tool has been in use for a very long time and is used not only in the framework of analytical work. Microsoft has done a great job and gave us a tool indescribable in its diversity. VBA (Visual basic for applications) and the ability to integrate Add-ins only strengthened this diversity, making it almost unique.

But, as with everything, the devil is in the details, and when it comes to more, shall we say, “fine” analytical work or big data processing, we face difficulties.

We need lines

If we are talking about a more or less large company, then we mean the presence of one or more systems that regulate business processes, collect information about customers and business information (CRM). Such systems collect data in large volumes and often, if they are put in one place, the amount reaches several billion rows. Excel does not export the volume of rows on a sheet exceeding one million. The manager will say: “What does the volume have to do with it? I need one number. We will explain that for more detailed analytics, we need maximum detail. Answers to questions, of course, at the level of statistics, but in order to build it correctly, you need to see everything.

An experienced analyst will say: “So what? I have power query. I have a DBMS where I will make a precalculation. I have long wanted to understand Python, they say Pandas top. We will answer that the point is complexity and the use of several tools requires both more time to complete the work and additional knowledge that the analyst may not have at a given time. We love to learn, but the customer does not always want to wait.
Integration into the work of Power Query – already in itself – is a bold hint that something is missing. If we dig into what Power BI consists of, then we will understand that using Power Query is the first step towards realizing that it is time to implement a BI system.

We need optimization

We always want everything to be considered quickly. With Excel, this is not always possible. Depending on the amount of data and the complexity of the calculations used, the speed of the book can reach infinity. Experienced analysts closed this problem both through Access and through the same Python, but we again return to the issue of complexity.

Microsoft’s attempt to make a universal tool, in this context, played a cruel joke. Excel does not use relational models, so an attempt to LOOKUP data for a large amount often turns into torture and shamanism.
In general, the job of an analyst is, for the most part, to take large amounts of data from different places and put it in one place for further calculations and visualization. This raw data can be taken from different sources in different formats. Not all of these formats are well read through excel and also require additional time for conversion. If we forget to type the data in columns at the input, it will not be a surprise if the book freezes, stops working, or does not open at all in the future.

In fairness, it should be said that BI systems can also experience difficulties when we try to integrate data from several sources, but this problem lies in the development of ETL processes, which we will definitely talk about in the future.

We need visibility

After processing the data, we want to show it. Excel is also not deprived of such functions: graphs, histograms and pies of all kinds, ample opportunities for styling each as you please. Here our problem lies not in the functionality, but rather simply in the fact that excel is not for this.

We very rarely show the result of the work done directly in excel. When we need to validate data with someone, or confirm the correctness of the methodology – yes. The final result, with all the conclusions, we usually put in Power Point.

The problem here is manifested in the user-friendly theme. Let’s imagine a manager who is responsible for a certain area, within which he has responsibilities for the implementation of several projects. The manager will want to see the performance of his territory for each project, preferably also in comparison with other regions. He needs it to accept

certain business decisions. He might want to dive even deeper and check some insights to improve performance and share experience with the organization. How will this happen if everything is built on excel and presentations? He will look for a way to get sources, collect them from himself, ask the analyst how to read the data and find what he wants. Will ask colleagues for help or hire a new employee to “prepare” reports exclusively for him. There may be several scenarios, but they all revolve around the convenience of working with the end result of the analyst’s work. Each organization finds its own way how to share the results of work, but each of them is far from ideal and requires a separate large work in terms of training and informing the organization. As a result, everyone still wants to see One place, where everything is there.

Results

We have described only some of the problems, but this is far from all. But the conclusions already suggest themselves – Excel no longer meets the modern needs of the analyst and requires modernization. Excel is still needed and important, if only because it is convenient for a user who is not engaged in analytics, but wants to conduct a little research in an accessible way, perhaps even on the basis of already processed data.

If we look, for example, at the Power BI structure, we see such integrated tools as power query and power pivot, which previously existed as Add-ins in Excel. It is likely that after their creation, Microsoft came to the conclusion that in order to realize the full potential and level the described shortcomings, it is necessary to create a fundamentally new product.

This product should be the same functional, but: focused on working with large amounts of data; the possibility of “modeling”, creating the final data model that will form the basis of analytics; visualization of this data and simplified sharing.

In addition, we missed that for a modern company, the “frequency” of reporting, its relevance at a given point in time, is important. If the company has prepared and learned how to correctly collect data in the database (we mean the ETL process), then automation and setting the regularity of updates are also an indisputable advantage of BI. Both for the analyst (no need to constantly update the report manually – it will update itself, you can do other work), and for the organization (no need to wait for a new report to be published, you know when and where it will update itself).

As we can see, given the development of these systems and their growing popularity, not only we come to the conclusion that BI is no longer an optional, but a necessary tool. We wish you a speedy transition! 🙂