Why do all these BI tools exist?

BI tools like Tableau, Qlik, and Power BI, started off targeting business users, not technical folks. They wooed them with fancy charts and visuals and promised to show insights from their data. But this selling point is only the last mile of BI. This skips past the hardest problem about working with data: accessing and modeling.

Business users never really needed another way to make a line chart. These BI tools limit users to making line and pie charts only to export the data later. This model assumes the user already knows what they want to see, but it typically takes a lot of time before a company's processes mature and become stable enough to base all their decisions off a few charts.

The tools are designed to help business users make dashboards without having to learn HTML, JavaScript, SQL, color hex codes, and all the other technical know-how it takes to build interactive visualizations. Yes, they do a lot more than that now, but that's how they all started out, and it's still how they get sold.

The reality is there is one BI tool on the market that could supplant all other BI tools for the foreseeable future, if only it implemented a few changes. You probably already know what I'm referring to. It's Excel.

I mentioned before that the hardest problem about working with data is simply accessing and modeling the data. What do I mean by that?

Well accessing is simple enough to understand, and yes, Tableau, Power BI, et. al. have tons of connectors to different data sources including APIs to SaaS products. So sourcing data is not really what I am referring to. As a business user, who just connected to and imported data from your company survey tool or payment processor or whatever, what's the first thing you want to do? Make a bar chart? Not likely. You want to look at the data, the actual data, all the different columns that are at your disposal. You want to see what data is available to you. Maybe later you want to build a chart to monitor trends, but first you need to build a mental model of the data you are monitoring. It's possible to do in these tools, but it's not a first-rate experience.

That leads to data modeling. Modeling is the process of transforming data into a form that makes sense for analysis. Data can be messy. Raw values are often stored as operational codes that need to be translated. Column names may not accurately define what data they hold. Erroneous or invalid values may be present. The data might be sourced from two different tables, or even different systems. Wrangling the mess takes a lot of trial and error, even for seasoned BI professionals. Tableau and Power BI and the like have data prep features that help even business users accomplish these tasks with point-and-click interfaces. But the end result still forces them to see that data through a pie chart.

On the other hand, Excel allows users to really see their data. They can filter it, poke it, test different calculations, summarize it, even build those line charts if they want. Excel's foundational simplicity, a canvas of rows and columns, makes it easily approachable to users of all skill levels. Couple that with the fact that the majority of business users also use it or have it pre-installed, means it is infinitely easier to collaborate and share. So why have these other tools surfaced in the first place? Because they solve the one problem that Excel has. Excel does not scale very well. A worksheet only allows up to about one million rows which has not changed for many years. Even if you have that much data in a worksheet, Excel will struggle to run calculations off it and the file will be too big for you to share over email.

Microsoft did make some progress on that front for a time. For example, Power Pivot allows you to import data into Excel way beyond the 1M row limit. Does anyone remember Power View? It was an add-on to Excel that allowed for better data visualization. Building eye-catching dashboards right inside Excel. Are you aware of Power Query, the intuitive data prep tool that is embedded in Excel? Not long after these came to Excel, Microsoft pivoted and released Power BI, where all the advancements in these tools are now focused. Power View is gone now, but Power Pivot and Power Query still live on, although their capabilities are far behind that which exists in their Power BI equivalents. But in that move, Microsoft abandoned the most fundamentally powerful BI feature of Excel, the grid.

Imagine if you could interactively query a database in Excel like you would a database client like DBeaver and have the results show in an Excel table. I know you can sort of do this already, but the experience is sub-optimal. What if all the data connectors in Power BI were natively available in Excel? What if you designed a report in Excel as a template, published it to SharePoint, and then each day that report is refreshed as a new version and delivered automatically? What if Power Query or Power Pivot helped guide the user to model data appropriately? (Maybe one day soon with Co-Pilot.) What if all your Excel files/datasets could be linked together and viewed in the cloud to build off the work of others? We do have Excel for the web, but it still has severe limitations when it comes to data ingestion and refresh.

Users don't need another tool to create charts, Excel already figured that out. They need a better experience to access and work with their data without limitations. If Microsoft had focused on improving Excel's BI scalability, I don't think these other tools would have survived long and many of the newer entrants would never have existed.