From Excel to SQL Server: when spreadsheets hit a wall

By the end of my first year, the reports looked modern. Everything had moved to Power BI, clients opened their results in a client area, and BI had become part of the consultancy's portfolio. Underneath, though, the data still lived where it always had: in Excel files.

This post covers the second and third years of building a BI practice at a consultancy that serves private schools in Brazil. The full timeline is in six years of building a BI practice, and the first year is in Power BI as a product.

What Excel was costing us

Most data work starts in Excel, and for good reason: anyone can open a spreadsheet, and anyone can fix a cell. The trouble shows up later. Spreadsheets have hard limits on size, and they are awkward to share safely. A file gets copied, emailed and renamed, and soon nobody is sure which version is the right one.

The bigger cost was crossing data. Every time a question needed two sources at once, someone had to stitch files together by hand: lookups across workbooks, columns pasted in the right order, a filter forgotten along the way. It was slow for us and slower still for the person waiting on the answer.

That person is the whole point. In BI, everything comes down to the experience of whoever uses the report. A beautiful dashboard that takes minutes to render does not work. People need the right number, available when they ask for it, with no delay and no bugs. A model fed by a pile of spreadsheets was never going to deliver that consistently.

Moving the data into SQL Server

So the second year was about structure. I moved the databases that lived in Excel files into SQL Server, and two things improved right away. Maintenance got easier, because the data now had one place to live and one way to be changed. Security improved significantly, because instead of files traveling around there was a server with controlled access.

Looking back, this was a turning point. Easier handling and the extra security were what let the work keep growing without collapsing under its own weight.

The textbook name for what we were building is a data warehouse: a central repository that consolidates several sources and is optimized for reading and analysis, unlike transactional systems, which are tuned for fast writes. The road into it is the classic ETL sequence:

1. Extract the raw data from wherever it sits: files, systems, exports.
2. Transform it by standardizing formats, removing duplicates, fixing inconsistencies and applying the business rules.
3. Load it into the warehouse, either in full or incrementally, adding only what is new.

None of this is new theory. What changes when you actually do it is that the transformation rules stop living in someone's head or in a hidden spreadsheet tab. They become something you can read, review and run again.

The question a single database could answer

Once the data sat in one place, we could cross sources that had never met. One example became a reference for me.

Schools always want to know whether their tuition is right. Not right in the abstract, but right for the families who actually live around them. With the SQL Server database, we could approach that with data. We drew the school's area of influence at two radii, 2 km and 4 km, and crossed demographic data about the people living there with economic data about the city. The result was an estimate of the average amount spent on school tuition in the region, which we then compared with the client's own tuition.

The reasoning fits in a sentence: if families around a school typically spend much less, or much more, than the school charges, that gap is a strategic fact, not a feeling. With spreadsheets, getting there would have meant manual joins for every single school. With a proper database, it became an analysis we could repeat.

That is the real argument for leaving Excel. Not that SQL is more sophisticated, but that some questions only become answerable when every source can be joined to every other one.

Year three: rebuilding every report on top of it

In the third year, every report was rebuilt in Power BI, now connected to the new SQL Server database. Users felt the difference, and the semantic models behind the reports performed much better, which meant faster and more accurate analysis.

A few modeling ideas matter at this stage, and they hold for almost any BI stack:

• Model before you build. Start with the entities and how they relate, refine that into tables and keys, and only then worry about indexes and data types.
• Normalize where data is written, denormalize where it is read. Normalization keeps the source consistent and free of redundancy. Reporting models usually trade some of that for simpler, faster queries.
• Think in dimensions. Most business questions are a measure sliced by time, place and category, which is the idea behind OLAP and multidimensional analysis. A model shaped around those dimensions can answer new questions without being rebuilt.
• Automate the refresh. A report that depends on someone remembering to update it will eventually show stale numbers.

That same year we hired an assistant. With an extra pair of hands, we could keep up with client requests while also building specific areas and custom solutions for clients who needed something beyond the standard reports.

What I would do again

If I were starting another BI practice tomorrow, I would still begin with the reports, because they are what clients see and pay for. But I would not wait a full year to fix the foundation. These are the lessons I carry from this phase:

• Speed is a feature. A slow dashboard loses the user's trust faster than an ugly one.
• A database earns its keep through cross-analysis. The tuition comparison justified the migration on its own.
• Write transformation rules as code or SQL. That is what makes the work repeatable, and it is what made the next step, automation, possible.
• Rebuild when the foundation changes. Pointing old reports at a new database is not the same as redesigning them for it.

The next bottleneck was volume, and it shaped the fourth year: automating data treatment with Python.

If your data is still split across spreadsheets and you want to see what crossing it looks like in practice, the bi19 BI platform connects spreadsheets, SQL databases, Google Sheets and APIs in one place.